Lua db:close deletes WAL file
(1) By chromesearcher on 2021-09-01 08:52:11 [link]
I have a question concerning SQLite & Lua. Basically i'm trying to use SQLite in WAL mode in my Android app. However if i perform a particular set of operations with database from my Lua script, it deletes `-wal` file. It leads to the problem: some of my transactions could be lost if the device loses power, and i don't have opportunity to close DB connection properly. The flow is attached in picture below. [![The process flow]] Database opened this way (c++): `int32 rcOpen = sqlite3_open(filename.c_str(), &handle);` Also, after connection opened, i use this `pragma`'s ``` PRAGMA journal_mode=WAL; PRAGMA synchronous=FULL; ``` More details: - I do manually commit transactions by `BEGIN TRANSACTION;` + `END TRANSACTION;`. I check if transactions have been commited via registered `sqlite_wal_hook`, which shows that number of pages in WAL file increases after INSERT. However i am not sure what happens to transactions that are transferred from WAL file to main DB file during checkpoint. - After Lua DB connection closed, there is STILL one c++ connection that is opened, so there is no reason to delete WAL file. - If i do db:close straight after sqlite3.open, without extra action between them - WAL file is not deleted - Also tried `PRAGMA synchronous=EXTRA + PRAGMA locking_mode=EXCLUSIVE` - didn't make any difference - Also tried using file controle mode `SQLITE_FCNTL_PERSIST_WAL` - didn't make any difference either ``` int enabled = 1; int rcControl = sqlite3_file_control(handle, "main", SQLITE_FCNTL_PERSIST_WAL, &enabled); ``` Stack: android 7 (fs ext4) - JNI (NDK 21.4.7075529) - c++ 11 - sqlite 3.27.2 - lua 5.3.4 - lsqlite3complete 0.9.5 The lua script i use for reproducing the problem: ``` function prequire(name) local status, lib = pcall(require, name) if(status) then return lib end --Library failed to load return nil end print("start lua") local sqlite3 = prequire("lsqlite3complete") if sqlite3 == nil then print('No lsqlite3') return end local db = sqlite3.open('/data/data/ru.chromesearcher.sqlitewal/files/database.sqlite3') if db == nil then print("db open failed") return end print("query start") local stmt = db:prepare('SELECT * FROM paymentevents where pe_amount=?') local amount = 3000 stmt:bind_values(amount) stmt:finalize() db:close() ``` : https://i.stack.imgur.com/8rFsj.jpg
(2) By Gunter Hick (gunter_hick) on 2021-09-01 10:22:40 in reply to 1 [link]
Are you sure your "app" and your "lua script" are actually running in truly separate processes, and not just separate threads? Are you sure that your "app" and your "lua script" are calling the same sqlite library? Maybe one is hard linked and the other is using the "installed" library.
(3) By chromesearcher on 2021-09-01 12:36:51 in reply to 2 [link]
I'm suppose, that app and script are running in the same process. However i don't know for sure, neither i know how to check. sqlite library for sure is different. - с++ uses library 3.27.2 which is built with CMake from source - Lua uses library 3.24.0 which (i assume) is built in lsqlite3complete library which i use (http://lua.sqlite.org/index.cgi/index - v0.9.5)
(4) By Gunter Hick (gunter_hick) on 2021-09-01 13:48:12 in reply to 3 [link]
Then you already have the answer you are looking for and can stop repeat-posting the same problem over and over again. There is no problem with SQLite, only with the way you are abusing it. The book 1984 is a warning, not an instruction manual. The same goes for https://sqlite.org/howtocorrupt.html
(5) By Chris Brody (chrisbrody) on 2021-09-01 16:02:57 in reply to 4
I don't think this pitfall is obvious enough for new users, especially for new users that are using SQLite from multiple languages as in the OP. I don't even see this info here: https://www.sqlite.org/quirks.html
(6) By Keith Medcalf (kmedcalf) on 2021-09-01 18:09:17 in reply to 5 [link]
This is not particular nor peculiar to SQLite3 but applies to *ALL* libraries when abused in this manner.
(7) By chromesearcher on 2021-09-07 09:20:15 in reply to 4 [link]
- Different libraries I tried using dynamic libsqlite3.so on C++ side and dynamic lsqlite3.so that is linked to dynamic libsqlite3.so and liblua.so. That makes possible using the same instance of sqlite3 library on C++ and Lua sides. And, with such configuration the original problem with WAL file was solved. P.S. Despite the fact that my case seems quite unique (android + NDK + lua + sqlite + wal mode), I still think, that such a situation should be somehow noted in documentation for Lua-sqlite lib. In my opinion, that's not obvious that using static libsqlite3 alongside static lsqlite3 (lsqlite3complete) could leed to such problems. Link to lua.sqlite.org ticket: http://lua.sqlite.org/index.cgi/tktview?name=0c4a8253ac - Different processes However i'm still confused about "separate processes" part. I'll examine https://sqlite.org/howtocorrupt.html more thoughtfully.
(8) By Warren Young (wyoung) on 2021-09-07 10:05:11 in reply to 7 [link]
> I'll examine [the docs] more thoughtfully. The relevant section is [this one](https://www.sqlite.org/howtocorrupt.html#multiple_copies_of_sqlite_linked_into_the_same_application). It directly addresses your wish for the problem to be documented. It doesn’t speak of Lua specifically, but it also doesn’t speak of the other infinity of combinations the same problem can occur under.
(9) By chromesearcher on 2021-09-07 11:27:07 in reply to 8 [link]
Indeed. Well, my fault. However until @gunter_hick asked, i didn't even realize, that i use 2 different SQLite instances. Hence, i still suppose, that this particular topic should be mentioned in Overview section of LuaSQLite3 Documentation. http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki#overview *I hope it's not an 'off topic', since lua.sqlite.org is a subdomain of sqlite.org
(10) By chromesearcher on 2021-09-07 15:08:15 in reply to 9 [link]
UPD. The Overview section of LuaSQLite3 docs concering lsqlite3complete have been kindly updated by Javier Guerra Giraldez.