lua-users home
lua-l archive

[Date Prev][Date Next][Thread Prev][Thread Next] [Date Index] [Thread Index]

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. 
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, 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
print("start lua")
local sqlite3 = prequire("lsqlite3complete")
if sqlite3 == nil then
    print('No lsqlite3')
local db ='/data/data/ru.chromesearcher.sqlitewal/files/database.sqlite3')
if db == nil then
    print("db open failed")
print("query start")
local stmt = db:prepare('SELECT * FROM paymentevents where pe_amount=?')
local amount = 3000
С уважением, Семенов Дмитрий