SQLite Forum

Timeline
Login

21 forum posts by user chromesearcher

2021-09-07
15:08 Reply: Lua db:close deletes WAL file (artifact: 4f230a6590 user: chromesearcher)

UPD. The Overview section of LuaSQLite3 docs concering lsqlite3complete have been kindly updated by Javier Guerra Giraldez.

11:27 Reply: Lua db:close deletes WAL file (artifact: a6d97e403f user: chromesearcher)

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

09:24 Reply: Why data is lost in SQLite database with WAL mode on when connection is not closed properly? (artifact: 7f201b7890 user: chromesearcher)

Solution found. It was different sqlite lib instances i used from Lua and C++ that coused WAL file problems.

Details: https://sqlite.org/forum/forumpost/8e121a3145

09:23 Reply: Why WAL file (-wal) may disappear? (artifact: 72ea4fe471 user: chromesearcher)

Solution found. It was different sqlite lib instances i used from Lua and C++ that coused WAL file problems.

Details: https://sqlite.org/forum/forumpost/8e121a3145

09:20 Reply: Lua db:close deletes WAL file (artifact: 862cf05c09 user: chromesearcher)
  • 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.

2021-09-02
11:41 Reply: Why WAL file (-wal) may disappear? (artifact: 2b31d614b8 user: chromesearcher)

I can INSERT into DB using the c++ connection, which i assume means that connection is open, unless INSERT automatically opens a new connection, which sounds unrealistic to me

2021-09-01
12:37 Edit: Why WAL file (-wal) may disappear? (artifact: f425729d03 user: chromesearcher)

I have an Android app using SQLite via c++ bridge (using JNI/NDK). I'm trying to store data in SQLite database with WAL mode on. However most of the time the WAL file is either missing either empty (0 bytes size). Why can it possible happen?

Details:

  • PRAGMA journal_mode=WAL;

  • 1 connection for reading and writing (however, the DB operations - init, insert, select - are in different threads each. However, all operations are completed sequentially. It's not a situation with more than one reader/writer at the same time).

  • If i do a checkpoint via PRAGMA wal_checkpoint; all transactions that i've inserted are transferred from journal to main db file, even when WAL file is empty/absent visually.

  • Transactions are commited manually using BEGIN TRANSACTION; and END TRANSACTION;, no autocommit

  • I'm checking the file's status via Device File Explorer in Android studio (also i tried to copy journal files from internal storage to external)

  • The DB file and -wal/-shm files are in internal memory: /data/data/com.package.my/files/

  • Transactions are being actually commited to WAL file - i've checked by setting up a sqlite3_wal_hook: it shows that page's count increases

  • sqlite 3.27.2

  • Android 7 (device Ciontek CS10)

  • fs = ext4

  • NDK version=21.4.7075529

What have i tried:

  • SERIALIZED threadsafe mode (SQLITE_OPEN_FULLMUTEX)

  • PRAGMA locking_mode=EXCLUSIVE; + PRAGMA synchronous=EXTRA;

  • PRAGMA synchronous=FULL;

  • setting VFS to unix-excl

  • changing file directory to /data/data/com.package.my/files/db (in order to have folder only for DB files, since /files contains other files)

  • making a minimum reproducible snippet. Part of the problem is that i tried to compose a minimum reproducible example: made a copy of the project with only DB part (removed everything except DB init/DAO/entity code). And this minimized app couldn't reproduce the problem: -wal file always exists and doesn't shrink/disappear. So i'm confused, can't figure out what part of the original project couses trouble.

Code:

int wal_hook(void* userdata, sqlite3* handle, const char* dbFilename, int nPages){

    char* pChar;
    pChar = (char*)userdata; // "test"

    printf("Hello hook");
    return SQLITE_OK;
}

// DB init (executed once on app start)
void initDB() 
    int32 rc = sqlite3_open(db_name.c_str(), &handle); // rc = 0
//    int32 rc = sqlite3_open_v2(filename.c_str(), &handle, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX, nullptr);
//    int rc = sqlite3_open_v2(filename.c_str(), &handle, SQLITE_OPEN_READWRITE, "unix-excl");

    // check threadsafe mode
    int stResult = sqlite3_threadsafe(); // stResult = 1

    // register WAL hook
    char* pointerContext = new char[4]();
    pointerContext[0] = 't';
    pointerContext[1] = 'e';
    pointerContext[2] = 's';
    pointerContext[3] = 't';
    sqlite3_wal_hook(handle, wal_hook, pointerContext);

    // turn WAL mode on
    int32 rcWAL = sqlite3_exec(handle, "PRAGMA journal_mode=WAL;", processResults, &result, &errMsg); // rcWAL = 0
}

// close connection
int32 close() {
    return sqlite3_close(handle);
}

// WAL checkpoint
sqlite3_exec(handle, "pragma wal_checkpoint;", processResults, &result, &errMsg);

// Insert
EventPtr persist(EventPtr obj) {
    vector<DBData*> args;
    int beginResult = sqlite3_exec(_connector->handle, "BEGIN TRANSACTION;", NULL, NULL, NULL);

    try {
        args.push_back(&obj->eventId);
        // ... push more args

        string query = "insert into events values(?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14);";
        int32_t rc = _connector->executePreparedWOresult(query.c_str(),&args);
        if(rc == SQLITE_DONE) {
            int endResult = sqlite3_exec(_connector->handle, "END TRANSACTION;", NULL, NULL, NULL);
            return obj;
        }
    } catch(...){ }
}

// SELECT
vector<EventPtr> readAll()
{
    string query = "select * from Events;";
    ResultSetPtr result= _connector->executePrepared(query.c_str(), NULL);
    vector<EventPtr> vec;
    for(int32_t i = 0; i < result->size(); i ++){
        EventPtr event(new Event);
        // init event
        vec.push_back(EventPtr(event));
    }
    return vec;
}

// executePreparedWOresult
int32 executePreparedWOresult(const string& query, vector<DBData*> *args){
    sqlite3_stmt *stmt;
    cout << query ;
    sqlite3_prepare_v2(handle, query.c_str(), query.size(), &stmt, NULL);

    for(uint32 i = 0;args && i < args->size(); i ++){
        switch(args->at(i)->getType()){
              // statement bindings (sqlite3_bind_text, etc.) 
        }
    }

    int32 rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return rc;
}

// executePrepared
ResultSetPtr executePrepared(const char *query, const vector<DBData*> *args){
    ResultSetPtr res = ResultSetPtr(new ResultSet);
    sqlite3_stmt *stmt;
    int32_t rs = sqlite3_prepare_v2(handle, query, strlen(query), &stmt, NULL);

    for(uint32 i = 0;args && i < args->size(); i ++){
        switch(args->at(i)->getType()){
              // statement bindings (sqlite3_bind_text, etc.) 
        }
    }
    int32 count = sqlite3_column_count(stmt);
    int32 rc;
    ResultRow row;
    int32 rows = 0;
    while((rc = sqlite3_step(stmt)) == SQLITE_ROW){
         rows ++;
         for(int32 i = 0; i < count; i++){
              // multiple row.push_back-s for all columns
         }
         res->push_back(row);
         row.clear();
    }
    sqlite3_finalize(stmt);
    return res;
}

12:36 Reply: Lua db:close deletes WAL file (artifact: 5e29c30f89 user: chromesearcher)

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)
08:52 Post: Lua db:close deletes WAL file (artifact: 7410a836e4 user: chromesearcher)

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()
07:15 Reply: Why WAL file (-wal) may disappear? (artifact: 5b765c1730 user: chromesearcher)

I've found out that WAL file is deleted when I close db connection in Lua script. So i have two connections: first is opened from c++ and second is opened from Lua script. When i close the connection that is opened from Lua script, WAL file disappears. Even though c++ connection is still opened.

2021-08-23
08:12 Reply: Why WAL file (-wal) may disappear? (artifact: 77671c76a6 user: chromesearcher)

After hours of research, i've narrowed problem down to lua-scripts. There are database interactions in some of my lua-scripts. And some particular of them make WAL file to disappear. I'll list some of it below:


// 1.

local query = [[ SELECT val from Parameters WHERE name = "column_name"]]
local period = 0
for row in db:nrows(query) do 
    if row["val"] ~= nil then
        period = row["val"]
    end
end

// 2.

local table = {}
if json ~= nil then
    table["event_id"] = in_json["event_id"]
    local query = [[ SELECT * FROM Events WHERE event_id = "%s" ]]
    query = string.format(query, table["event_id"])    
    for row in db:nrows(query) do
        table = row
    end
else
    json = {}
    local query = [[ SELECT * FROM Events order by created DESC LIMIT 1; ]]
    for row in db:nrows(query) do
        table = row
    end
end

// 3.

function getRow(con, sql)
    local iRow = nil
	for a in con:nrows(sql) do
		iRow = a
	end
	return iRow
end
local termRow = getRow(db,[[SELECT value FROM parameters WHERE name='column_name']])

// 4.

local stmt = db:prepare("SELECT value FROM parameters WHERE name='column_name'")
local cnt = 0
for row in stmt:nrows() do
	cnt = cnt + 1
end
stmt:finalize()	

// 5.

local param = "N"
for Parameter in db_transport:nrows([[SELECT val FROM Parameters WHERE name = 'param']]) do  
    param = SParameter["val"]
end

2021-08-09
07:51 Edit: Why WAL file (-wal) may disappear? (artifact: 2fcaea7a14 user: chromesearcher)

I have an Android app using SQLite via c++ bridge (using JNI/NDK). I'm trying to store data in SQLite database with WAL mode on. However most of the time the WAL file is either missing either empty (0 bytes size). Why can it possible happen?

Details:

  • PRAGMA journal_mode=WAL;

  • 1 connection for reading and writing (however, the DB operations - init, insert, select - are in different threads each. However, all operations are completed sequentially. It's not a situation with more than one reader/writer at the same time).

  • If i do a checkpoint via PRAGMA wal_checkpoint; all transactions that i've inserted are transferred from journal to main db file, even when WAL file is empty/absent visually.

  • Transactions are commited manually using BEGIN TRANSACTION; and END TRANSACTION;, no autocommit

  • I'm checking the file's status via Device File Explorer in Android studio (also i tried to copy journal files from internal storage to external)

  • The DB file and -wal/-shm files are in internal memory: /data/data/com.package.my/files/

  • Transactions are being actually commited to WAL file - i've checked by setting up a sqlite3_wal_hook: it shows that page's count increases

  • sqlite 3.27.2

  • Android 7 (device Ciontek CS10)

  • fs = ext4

  • NDK version=21.4.7075529

What have i tried: - SERIALIZED threadsafe mode (SQLITE_OPEN_FULLMUTEX)

  • PRAGMA locking_mode=EXCLUSIVE; + PRAGMA synchronous=EXTRA;

  • PRAGMA synchronous=FULL;

  • setting VFS to unix-excl

  • changing file directory to /data/data/com.package.my/files/db (in order to have folder only for DB files, since /files contains other files)

  • making a minimum reproducible snippet. Part of the problem is that i tried to compose a minimum reproducible example: made a copy of the project with only DB part (removed everything except DB init/DAO/entity code). And this minimized app couldn't reproduce the problem: -wal file always exists and doesn't shrink/disappear. So i'm confused, can't figure out what part of the original project couses trouble.

Code:

int wal_hook(void* userdata, sqlite3* handle, const char* dbFilename, int nPages){

    char* pChar;
    pChar = (char*)userdata; // "test"

    printf("Hello hook");
    return SQLITE_OK;
}

// DB init (executed once on app start)
void initDB() 
    int32 rc = sqlite3_open(db_name.c_str(), &handle); // rc = 0
//    int32 rc = sqlite3_open_v2(filename.c_str(), &handle, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX, nullptr);
//    int rc = sqlite3_open_v2(filename.c_str(), &handle, SQLITE_OPEN_READWRITE, "unix-excl");

    // check threadsafe mode
    int stResult = sqlite3_threadsafe(); // stResult = 1

    // register WAL hook
    char* pointerContext = new char[4]();
    pointerContext[0] = 't';
    pointerContext[1] = 'e';
    pointerContext[2] = 's';
    pointerContext[3] = 't';
    sqlite3_wal_hook(handle, wal_hook, pointerContext);

    // turn WAL mode on
    int32 rcWAL = sqlite3_exec(handle, "PRAGMA journal_mode=WAL;", processResults, &result, &errMsg); // rcWAL = 0
}

// close connection
int32 close() {
    return sqlite3_close(handle);
}

// WAL checkpoint
sqlite3_exec(handle, "pragma wal_checkpoint;", processResults, &result, &errMsg);

// Insert
EventPtr persist(EventPtr obj) {
    vector<DBData*> args;
    int beginResult = sqlite3_exec(_connector->handle, "BEGIN TRANSACTION;", NULL, NULL, NULL);

    try {
        args.push_back(&obj->eventId);
        // ... push more args

        string query = "insert into events values(?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14);";
        int32_t rc = _connector->executePreparedWOresult(query.c_str(),&args);
        if(rc == SQLITE_DONE) {
            int endResult = sqlite3_exec(_connector->handle, "END TRANSACTION;", NULL, NULL, NULL);
            return obj;
        }
    } catch(...){ }
}

// SELECT
vector<EventPtr> readAll()
{
    string query = "select * from Events;";
    ResultSetPtr result= _connector->executePrepared(query.c_str(), NULL);
    vector<EventPtr> vec;
    for(int32_t i = 0; i < result->size(); i ++){
        EventPtr event(new Event);
        // init event
        vec.push_back(EventPtr(event));
    }
    return vec;
}

// executePreparedWOresult
int32 executePreparedWOresult(const string& query, vector<DBData*> *args){
    sqlite3_stmt *stmt;
    cout << query ;
    sqlite3_prepare_v2(handle, query.c_str(), query.size(), &stmt, NULL);

    for(uint32 i = 0;args && i < args->size(); i ++){
        switch(args->at(i)->getType()){
              // statement bindings (sqlite3_bind_text, etc.) 
        }
    }

    int32 rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return rc;
}

// executePrepared
ResultSetPtr executePrepared(const char *query, const vector<DBData*> *args){
    ResultSetPtr res = ResultSetPtr(new ResultSet);
    sqlite3_stmt *stmt;
    int32_t rs = sqlite3_prepare_v2(handle, query, strlen(query), &stmt, NULL);

    for(uint32 i = 0;args && i < args->size(); i ++){
        switch(args->at(i)->getType()){
              // statement bindings (sqlite3_bind_text, etc.) 
        }
    }
    int32 count = sqlite3_column_count(stmt);
    int32 rc;
    ResultRow row;
    int32 rows = 0;
    while((rc = sqlite3_step(stmt)) == SQLITE_ROW){
         rows ++;
         for(int32 i = 0; i < count; i++){
              // multiple row.push_back-s for all columns
         }
         res->push_back(row);
         row.clear();
    }
    sqlite3_finalize(stmt);
    return res;
}

07:35 Edit: Why WAL file (-wal) may disappear? (artifact: 270c0b5c2b user: chromesearcher)

I have an Android app using SQLite via c++ bridge (using JNI/NDK). I'm trying to store data in SQLite database with WAL mode on. However most of the time the WAL file is either missing either empty (0 bytes size). Why can it possible happen?

Details: - PRAGMA journal_mode=WAL;

  • 1 connection for reading and writing (however, the DB operations - init, insert, select - are in different threads each. However, all operations are completed sequentially. It's not a situation with more than one reader/writer at the same time).

  • If i do a checkpoint via PRAGMA wal_checkpoint; all transactions that i've inserted are transferred from journal to main db file, even when WAL file is empty/absent visually.

  • Transactions are commited manually using BEGIN TRANSACTION; and END TRANSACTION;, no autocommit

  • I'm checking the file's status via Device File Explorer in Android studio (also i tried to copy journal files from internal storage to external)

  • The DB file and -wal/-shm files are in internal memory: /data/data/com.package.my/files/

  • Transactions are being actually commited to WAL file - i've checked by setting up a sqlite3_wal_hook: it shows that page's count increases

  • sqlite 3.27.2

  • Android 7 (device Ciontek CS10)

  • fs = ext4

  • NDK version=21.4.7075529

What have i tried: - SERIALIZED threadsafe mode (SQLITE_OPEN_FULLMUTEX)

  • PRAGMA locking_mode=EXCLUSIVE; + PRAGMA synchronous=EXTRA;

  • PRAGMA synchronous=FULL;

  • setting VFS to unix-excl

  • changing file directory to /data/data/com.package.my/files/db (in order to have folder only for DB files, since /files contains other files)

  • making a minimum reproducible snippet. Part of the problem is that i tried to compose a minimum reproducible example: made a copy of the project with only DB part (removed everything except DB init/DAO/entity code). And this minimized app couldn't reproduce the problem: -wal file always exists and doesn't shrink/disappear. So i'm confused, can't figure out what part of the original project couses trouble.

Code:

int wal_hook(void* userdata, sqlite3* handle, const char* dbFilename, int nPages){

    char* pChar;
    pChar = (char*)userdata; // "test"

    printf("Hello hook");
    return SQLITE_OK;
}

// DB init (executed once on app start)
void initDB() 
    int32 rc = sqlite3_open(db_name.c_str(), &handle); // rc = 0
//    int32 rc = sqlite3_open_v2(filename.c_str(), &handle, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX, nullptr);
//    int rc = sqlite3_open_v2(filename.c_str(), &handle, SQLITE_OPEN_READWRITE, "unix-excl");

    // check threadsafe mode
    int stResult = sqlite3_threadsafe(); // stResult = 1

    // register WAL hook
    char* pointerContext = new char[4]();
    pointerContext[0] = 't';
    pointerContext[1] = 'e';
    pointerContext[2] = 's';
    pointerContext[3] = 't';
    sqlite3_wal_hook(handle, wal_hook, pointerContext);

    // turn WAL mode on
    int32 rcWAL = sqlite3_exec(handle, "PRAGMA journal_mode=WAL;", processResults, &result, &errMsg); // rcWAL = 0
}

// close connection
int32 close() {
    return sqlite3_close(handle);
}

// WAL checkpoint
sqlite3_exec(handle, "pragma wal_checkpoint;", processResults, &result, &errMsg);

// Insert
EventPtr persist(EventPtr obj) {
    vector<DBData*> args;
    int beginResult = sqlite3_exec(_connector->handle, "BEGIN TRANSACTION;", NULL, NULL, NULL);

    try {
        args.push_back(&obj->eventId);
        // ... push more args

        string query = "insert into events values(?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14);";
        int32_t rc = _connector->executePreparedWOresult(query.c_str(),&args);
        if(rc == SQLITE_DONE) {
            int endResult = sqlite3_exec(_connector->handle, "END TRANSACTION;", NULL, NULL, NULL);
            return obj;
        }
    } catch(...){ }
}

// SELECT
vector<EventPtr> readAll()
{
    string query = "select * from Events;";
    ResultSetPtr result= _connector->executePrepared(query.c_str(), NULL);
    vector<EventPtr> vec;
    for(int32_t i = 0; i < result->size(); i ++){
        EventPtr event(new Event);
        // init event
        vec.push_back(EventPtr(event));
    }
    return vec;
}

// executePreparedWOresult
int32 executePreparedWOresult(const string& query, vector<DBData*> *args){
    sqlite3_stmt *stmt;
    cout << query ;
    sqlite3_prepare_v2(handle, query.c_str(), query.size(), &stmt, NULL);

    for(uint32 i = 0;args && i < args->size(); i ++){
        switch(args->at(i)->getType()){
              // statement bindings (sqlite3_bind_text, etc.) 
        }
    }

    int32 rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return rc;
}

// executePrepared
ResultSetPtr executePrepared(const char *query, const vector<DBData*> *args){
    ResultSetPtr res = ResultSetPtr(new ResultSet);
    sqlite3_stmt *stmt;
    int32_t rs = sqlite3_prepare_v2(handle, query, strlen(query), &stmt, NULL);

    for(uint32 i = 0;args && i < args->size(); i ++){
        switch(args->at(i)->getType()){
              // statement bindings (sqlite3_bind_text, etc.) 
        }
    }
    int32 count = sqlite3_column_count(stmt);
    int32 rc;
    ResultRow row;
    int32 rows = 0;
    while((rc = sqlite3_step(stmt)) == SQLITE_ROW){
         rows ++;
         for(int32 i = 0; i < count; i++){
              // multiple row.push_back-s for all columns
         }
         res->push_back(row);
         row.clear();
    }
    sqlite3_finalize(stmt);
    return res;
}

07:33 Post: Why WAL file (-wal) may disappear? (artifact: a2eeab1049 user: chromesearcher)

I have an Android app using SQLite via c++ bridge (using JNI/NDK). I'm trying to store data in SQLite database with WAL mode on. However most of the time the WAL file is either missing either empty (0 bytes size). Why can it possible happen?

Details: - PRAGMA journal_mode=WAL;

  • 1 connection for reading and writing (however, the DB operations - init, insert, select - are in different threads each. However, all operations are completed sequentially. It's not a situation with more than one reader/writer at the same time).

  • If i do a checkpoint via PRAGMA wal_checkpoint; all transactions that i've inserted are transferred from journal to main db file, even when WAL file is empty/absent visually.

  • Transactions are commited manually using BEGIN TRANSACTION; and END TRANSACTION;, no autocommit

  • I'm checking the file's status via Device File Explorer in Android studio (also i tried to copy journal files from internal storage to external)

  • The DB file and -wal/-shm files are in internal memory: /data/data/com.package.my/files/

  • Transactions are being actually commited to WAL file - i've checked by setting up a sqlite3_wal_hook: it shows that page's count increases

  • sqlite 3.27.2

  • Android 7 (device Ciontek CS10)

  • fs = ext4

What have i tried: - SERIALIZED threadsafe mode (SQLITE_OPEN_FULLMUTEX)

  • PRAGMA locking_mode=EXCLUSIVE; + PRAGMA synchronous=EXTRA;

  • PRAGMA synchronous=FULL;

  • setting VFS to unix-excl

  • changing file directory to /data/data/com.package.my/files/db (in order to have folder only for DB files, since /files contains other files)

  • making a minimum reproducible snippet. Part of the problem is that i tried to compose a minimum reproducible example: made a copy of the project with only DB part (removed everything except DB init/DAO/entity code). And this minimized app couldn't reproduce the problem: -wal file always exists and doesn't shrink/disappear. So i'm confused, can't figure out what part of the original project couses trouble.

Code:

int wal_hook(void* userdata, sqlite3* handle, const char* dbFilename, int nPages){

    char* pChar;
    pChar = (char*)userdata; // "test"

    printf("Hello hook");
    return SQLITE_OK;
}

// DB init (executed once on app start)
void initDB() 
    int32 rc = sqlite3_open(db_name.c_str(), &handle); // rc = 0
//    int32 rc = sqlite3_open_v2(filename.c_str(), &handle, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX, nullptr);
//    int rc = sqlite3_open_v2(filename.c_str(), &handle, SQLITE_OPEN_READWRITE, "unix-excl");

    // check threadsafe mode
    int stResult = sqlite3_threadsafe(); // stResult = 1

    // register WAL hook
    char* pointerContext = new char[4]();
    pointerContext[0] = 't';
    pointerContext[1] = 'e';
    pointerContext[2] = 's';
    pointerContext[3] = 't';
    sqlite3_wal_hook(handle, wal_hook, pointerContext);

    // turn WAL mode on
    int32 rcWAL = sqlite3_exec(handle, "PRAGMA journal_mode=WAL;", processResults, &result, &errMsg); // rcWAL = 0
}

// close connection
int32 close() {
    return sqlite3_close(handle);
}

// WAL checkpoint
sqlite3_exec(handle, "pragma wal_checkpoint;", processResults, &result, &errMsg);

// Insert
EventPtr persist(EventPtr obj) {
    vector<DBData*> args;
    int beginResult = sqlite3_exec(_connector->handle, "BEGIN TRANSACTION;", NULL, NULL, NULL);

    try {
        args.push_back(&obj->eventId);
        // ... push more args

        string query = "insert into events values(?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14);";
        int32_t rc = _connector->executePreparedWOresult(query.c_str(),&args);
        if(rc == SQLITE_DONE) {
            int endResult = sqlite3_exec(_connector->handle, "END TRANSACTION;", NULL, NULL, NULL);
            return obj;
        }
    } catch(...){ }
}

// SELECT
vector<EventPtr> readAll()
{
    string query = "select * from Events;";
    ResultSetPtr result= _connector->executePrepared(query.c_str(), NULL);
    vector<EventPtr> vec;
    for(int32_t i = 0; i < result->size(); i ++){
        EventPtr event(new Event);
        // init event
        vec.push_back(EventPtr(event));
    }
    return vec;
}

// executePreparedWOresult
int32 executePreparedWOresult(const string& query, vector<DBData*> *args){
    sqlite3_stmt *stmt;
    cout << query ;
    sqlite3_prepare_v2(handle, query.c_str(), query.size(), &stmt, NULL);

    for(uint32 i = 0;args && i < args->size(); i ++){
        switch(args->at(i)->getType()){
              // statement bindings (sqlite3_bind_text, etc.) 
        }
    }

    int32 rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return rc;
}

// executePrepared
ResultSetPtr executePrepared(const char *query, const vector<DBData*> *args){
    ResultSetPtr res = ResultSetPtr(new ResultSet);
    sqlite3_stmt *stmt;
    int32_t rs = sqlite3_prepare_v2(handle, query, strlen(query), &stmt, NULL);

    for(uint32 i = 0;args && i < args->size(); i ++){
        switch(args->at(i)->getType()){
              // statement bindings (sqlite3_bind_text, etc.) 
        }
    }
    int32 count = sqlite3_column_count(stmt);
    int32 rc;
    ResultRow row;
    int32 rows = 0;
    while((rc = sqlite3_step(stmt)) == SQLITE_ROW){
         rows ++;
         for(int32 i = 0; i < count; i++){
              // multiple row.push_back-s for all columns
         }
         res->push_back(row);
         row.clear();
    }
    sqlite3_finalize(stmt);
    return res;
}

2021-07-30
08:09 Edit reply: Why data is lost in SQLite database with WAL mode on when connection is not closed properly? (artifact: a056cb8ed6 user: chromesearcher)

Thanks for reply! I'll check the cases that you've suggested and come back with results.

The one issue here is case B.2). Most of the time i don't have -wal and -shm files on my Android device (at least at the /data/data/com.package.my/files directory). To elaborate: SOMETIMES i see the files near the main db file. But most of the time -wal file is empty.

To sum up, after inserting several transactions in WAL mode i see:

Most often: /data/data/com.package.my/files/

  • db.sqlite3
  • db.sqlite3-wal (0 bytes)
  • db.sqlite3-shm (32 bytes)

A bit less often: /data/data/com.package.my/files/

  • db.sqlite3
  • db.sqlite3-wal (DON'T EXIST)
  • db.sqlite3-shm (DON'T EXIST)

SEEN ONE TIME for all the tesing period (around 2 weeks) /data/data/com.package.my/files/

  • db.sqlite3
  • db.sqlite3-wal (3,4 mb)
  • db.sqlite3-shm (32 bytes)
08:07 Reply: Why data is lost in SQLite database with WAL mode on when connection is not closed properly? (artifact: 18140540cc user: chromesearcher)

Thanks for reply! I'll check the cases that you've suggested and come back with results.

The one issue here is case B.2). Most of the time i don't have -wal and -shm files on my Android device (at least at the /data/data/com.package.my/files directory). To elaborate: SOMETIMES i see the files near the main db file. But most of the time -wal file is empty.

To sum up, after inserting several transactions in WAL mode i see: 1) Most often: -/data/data/com.package.my/files/ --- db.sqlite3 --- db.sqlite3-wal (0 bytes) --- db.sqlite3-shm (32 bytes)

  1. A bit less often: -/data/data/com.package.my/files/ --- db.sqlite3 --- db.sqlite3-wal (DON'T EXIST) --- db.sqlite3-shm (DON'T EXIST)

  2. SEEN ONE TIME for all the tesing period (around 2 weeks) --- db.sqlite3 --- db.sqlite3-wal (3,4 mb) --- db.sqlite3-shm (32 bytes)

07:58 Reply: Why data is lost in SQLite database with WAL mode on when connection is not closed properly? (artifact: ff2dffec98 user: chromesearcher)

Understood. I'll prepare a minimum reproducible sample/project. Where should i send it?

2021-07-29
12:31 Reply: Why data is lost in SQLite database with WAL mode on when connection is not closed properly? (artifact: 95d46fdac5 user: chromesearcher)

Thanks for the reply! I would like to help you reproduce the problem, however, i don't quite understand what do you mean by "compose an example of the problem"? What should i add to my description?

12:25 Reply: Why data is lost in SQLite database with WAL mode on when connection is not closed properly? (artifact: 092adc201e user: chromesearcher)

Thanks for the suggestion! I actually tried pragma synchronous=FULL; & pragma synchronous=EXTRA; after your comment. So far problem stays even after using the synchronous pragma.

I tried different ways for step 8:

  • kill app itself
  • reboot device via POWER system button
  • removing battery from device.

Result is always the same - after re-opening app there are some transaction lost. Which i totally don't understand as soon as wal hook shows, that pages been commited to WAL journal file. Why don't they appear after checkpoint?

09:49 Edit: Why data is lost in SQLite database with WAL mode on when connection is not closed properly? (artifact: d3fe145605 user: chromesearcher)

Problem: data is not available to SELECT from new connection to SQLite DB with WAL mode (Write Ahead Log) on if previous connection hasn't been closed properly. Main question is: why data is lost and is there some way to get lost transactions?

I'm trying to store data in a SQLite table with WAL mode on. I'll describe 3 cases: case A results in transaction loss, cases B and C - don't.

Case A (quit app, don't close connection):

  1. Open application, open Connection 1, open Connection 2 (on the same DB)
  2. Begin transaction (Connection 1)
  3. INSERT (Connection 1)
  4. End transaction (Connection 1)
  5. Repeat 5 times steps 1-3
  6. SELECT (Connection 1) // All data is present.
  7. SELECT (Connection 2) // All data is present.
  8. Close application (without closing Connection 1)
  9. Open application, open Connection 3
  10. SELECT (Connection 3) // Some data is lost - select could return 2 out of 5 inserted transactions, for example. Or 0 out of 5 transactions. Totally random.

Case B (close connection before quit app):

  1. Open application, open Connection 1, open Connection 2 (on the same DB)
  2. Begin transaction (Connection 1)
  3. INSERT (Connection 1)
  4. End transaction (Connection 1)
  5. Repeat 5 times steps 1-3
  6. SELECT (Connection 1) // All data is present.
  7. SELECT (Connection 2) // All data is present.
  8. Close Connection 1
  9. Close application
  10. Open application, open Connection 3
  11. SELECT (Connection 3) // All data is present.

Case C (perform checkpoint before close app + don't close connection):

  1. Open application, open Connection 1, open Connection 2 (on the same DB)
  2. Begin transaction (Connection 1)
  3. INSERT (Connection 1)
  4. End transaction (Connection 1)
  5. Repeat 5 times steps 1-3
  6. SELECT (Connection 1) // All data is present.
  7. SELECT (Connection 2) // All data is present.
  8. Perform WAL checkpoint
  9. Close application (without closing Connection 1)
  10. Open application, open Connection 3
  11. SELECT (Connection 3) // All data is present.

To sum up, when i SELECT from the table before closing app, all data is present, but after incorrect closing of the application (for example if app crashed) some of the data i inserted is missing. However if i perform checkpoint before closing the app (or close connection before closing app)- all the data is available.

Extra info:

  • If i perform checkpoint after re-opening app (Case A), transactions don't appear (don't proceed from journal to main db file).
  • WAL hook: i've registered callback using sqlite3_wal_hook to check whether transactions are actually commited to WAL-journal file and it shows that pages being successfully written to the log file.
  • WAL file: i've tried to look into -wal file using Device File Explorer in Android Studio or by copying it to external storage from internal storage (/data/data/com.package.my/files) and most of the time it is either empty or doesn't exist.
  • Threadsafe: i've tried to turn on SERIALIZED threadsafe mode by using SQLITE_OPEN_FULLMUTEX flag when opening DB:

sqlite3_open_v2(db_name.c_str(), &handle, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX, nullptr);

It didn't make any difference. However, it lead to problems with reading from 2nd connection, so i use sqlite3_open without SQLITE_OPEN_FULLMUTEX.

  • Stack: android 7 - JNI - c++ 11 - sqlite 3.27.2

Code:


// DB init (executed once on app start)
void initDB() 
    int32 rc = sqlite3_open(db_name.c_str(), &handle); // rc = 0

    // check threadsafe mode
    int stResult = sqlite3_threadsafe(); // stResult = 1

    // register WAL hook
    char* pointerContext = new char[4]();
    pointerContext[0] = 't';
    pointerContext[1] = 'e';
    pointerContext[2] = 's';
    pointerContext[3] = 't';
    sqlite3_wal_hook(handle, wal_hook, pointerContext);

    // turn WAL mode on
    int32 rcWAL = sqlite3_exec(handle, "PRAGMA journal_mode=WAL;", processResults, &result, &errMsg); // rcWAL = 0
}

// close connection
int32 close() {
    return sqlite3_close(handle);
}

// WAL checkpoint
sqlite3_exec(handle, "pragma wal_checkpoint;", processResults, &result, &errMsg);

// Insert
EventPtr persist(EventPtr obj) {
    vector<DBData*> args;
    int beginResult = sqlite3_exec(_connector->handle, "BEGIN TRANSACTION;", NULL, NULL, NULL);

    try {
        args.push_back(&obj->eventId);
        // ... push more args

        string query = "insert into events values(?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14);";
        int32_t rc = _connector->executePreparedWOresult(query.c_str(),&args);
        if(rc == SQLITE_DONE) {
            int endResult = sqlite3_exec(_connector->handle, "END TRANSACTION;", NULL, NULL, NULL);
            return obj;
        }
    } catch(...){ }
}

// SELECT
vector<EventPtr> readAll()
{
    string query = "select * from Events;";
    ResultSetPtr result= _connector->executePrepared(query.c_str(), NULL);
    vector<EventPtr> vec;
    for(int32_t i = 0; i < result->size(); i ++){
        EventPtr event(new Event);
        // init event
        vec.push_back(EventPtr(event));
    }
    return vec;
}

// executePreparedWOresult
int32 executePreparedWOresult(const string& query, vector<DBData*> *args){
    sqlite3_stmt *stmt;
    cout << query ;
    sqlite3_prepare_v2(handle, query.c_str(), query.size(), &stmt, NULL);

    for(uint32 i = 0;args && i < args->size(); i ++){
        switch(args->at(i)->getType()){
              // statement bindings (sqlite3_bind_text, etc.) 
        }
    }

    int32 rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return rc;
}

// executePrepared
ResultSetPtr executePrepared(const char *query, const vector<DBData*> *args){
    ResultSetPtr res = ResultSetPtr(new ResultSet);
    sqlite3_stmt *stmt;
    int32_t rs = sqlite3_prepare_v2(handle, query, strlen(query), &stmt, NULL);

    for(uint32 i = 0;args && i < args->size(); i ++){
        switch(args->at(i)->getType()){
              // statement bindings (sqlite3_bind_text, etc.) 
        }
    }
    int32 count = sqlite3_column_count(stmt);
    int32 rc;
    ResultRow row;
    int32 rows = 0;
    while((rc = sqlite3_step(stmt)) == SQLITE_ROW){
         rows ++;
         for(int32 i = 0; i < count; i++){
              // multiple row.push_back-s for all columns
         }
         res->push_back(row);
         row.clear();
    }
    sqlite3_finalize(stmt);
    return res;
}
08:48 Post: Why data is lost in SQLite database with WAL mode on when connection is not closed properly? (artifact: 974675b288 user: chromesearcher)

Problem: data is not available to SELECT from new connection to SQLite DB with WAL mode (Write Ahead Log) on if previous connection hasn't been closed properly. Main question is: why data is lost and is there some way to get lost transactions?

I'm trying to store data in a SQLite table with WAL mode on. I'll describe 3 cases: case A results in transaction loss, cases B and C - don't.

Case A (quit app, don't close connection):

  1. Open application, open Connection 1, open Connection 2 (on the same DB)
  2. Begin transaction (Connection 1)
  3. INSERT (Connection 1)
  4. End transaction (Connection 1)
  5. Repeat 5 times steps 1-3
  6. SELECT (Connection 1) // All data is present.
  7. SELECT (Connection 2) // All data is present.
  8. Close application (without closing Connection 1)
  9. Open application, open Connection 3
  10. SELECT (Connection 3) // Some data is lost - select could return 2 out of 5 inserted transactions, for example. Or 0 out of 5 transactions. Totally random.

Case B (close connection before quit app):

  1. Open application, open Connection 1, open Connection 2 (on the same DB)
  2. Begin transaction (Connection 1)
  3. INSERT (Connection 1)
  4. End transaction (Connection 1)
  5. Repeat 5 times steps 1-3
  6. SELECT (Connection 1) // All data is present.
  7. SELECT (Connection 2) // All data is present.
  8. Close Connection 1
  9. Close application
  10. Open application, open Connection 3
  11. SELECT (Connection 3) // All data is present.

Case C (perform checkpoint before close app + don't close connection):

  1. Open application, open Connection 1, open Connection 2 (on the same DB)
  2. Begin transaction (Connection 1)
  3. INSERT (Connection 1)
  4. End transaction (Connection 1)
  5. Repeat 5 times steps 1-3
  6. SELECT (Connection 1) // All data is present.
  7. SELECT (Connection 2) // All data is present.
  8. Perform WAL checkpoint
  9. Close application (without closing Connection 1)
  10. Open application, open Connection 3
  11. SELECT (Connection 3) // All data is present.

To sum up, when i SELECT from the table before closing app, all data is present, but after incorrect closing of the application (for example if app crashed) some of the data i inserted is missing. However if i perform checkpoint before closing the app (or close connection before closing app)- all the data is available.

Extra info:

  • If i perform checkpoint after re-opening app (Case A), transactions don't appear (don't proceed from journal to main db file).
  • WAL hook: i've registered callback using sqlite3_wal_hook to check whether transactions are actually commited to WAL-journal file and it shows that pages being successfully written to the log file.
  • WAL file: i've tried to look into -wal file using Device File Explorer in Android Studio or by copying it to external storage from internal storage (/data/data/com.package.my/files) and most of the time it is either empty or doesn't exist.
  • Threadsafe: i've tried to turn on SERIALIZED threadsafe mode by using SQLITE_OPEN_FULLMUTEX flag when opening DB:

sqlite3_open_v2(db_name.c_str(), &handle, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX, nullptr);

It didn't make any difference. However, it lead to problems with reading from 2nd connection, so i use sqlite3_open without SQLITE_OPEN_FULLMUTEX.

  • Stack: android 7 - JNI - c++ 11 - sqlite 3.27.2

Code:


// DB init (executed once on app start)
void initDB() 
    int32 rc = sqlite3_open(db_name.c_str(), &handle); // rc = 0

    // check threadsafe mode
    int stResult = sqlite3_threadsafe(); // stResult = 1

    // register WAL hook
    char* pointerContext = new char[4]();
    pointerContext[0] = 't';
    pointerContext[1] = 'e';
    pointerContext[2] = 's';
    pointerContext[3] = 't';
    sqlite3_wal_hook(handle, wal_hook, pointerContext);

    // turn WAL mode on
    int32 rcWAL = sqlite3_exec(handle, "PRAGMA journal_mode=WAL;", processResults, &result, &errMsg); // rcWAL = 0
}

// close connection
int32 close() {
    return sqlite3_close(handle);
}

// WAL checkpoint
sqlite3_exec(handle, "pragma wal_checkpoint;", processResults, &result, &errMsg);

// Insert
EventPtr persist(EventPtr obj) {
    vector<DBData*> args;
    int beginResult = sqlite3_exec(_connector->handle, "BEGIN TRANSACTION;", NULL, NULL, NULL);

    try {
        args.push_back(&obj->eventId);
        // ... push more args

        string query = "insert into events values(?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14);";
        int32_t rc = _connector->executePreparedWOresult(query.c_str(),&args);
        if(rc == SQLITE_DONE) {
            int beginResult = sqlite3_exec(_connector->handle, "BEGIN TRANSACTION;", NULL, NULL, NULL);
            return obj;
        }
    } catch(...){ }
}

// SELECT
vector<EventPtr> readAll()
{
    string query = "select * from Events;";
    ResultSetPtr result= _connector->executePrepared(query.c_str(), NULL);
    vector<EventPtr> vec;
    for(int32_t i = 0; i < result->size(); i ++){
        EventPtr event(new Event);
        // init event
        vec.push_back(EventPtr(event));
    }
    return vec;
}

// executePreparedWOresult
int32 executePreparedWOresult(const string& query, vector<DBData*> *args){
    sqlite3_stmt *stmt;
    cout << query ;
    sqlite3_prepare_v2(handle, query.c_str(), query.size(), &stmt, NULL);

    for(uint32 i = 0;args && i < args->size(); i ++){
        switch(args->at(i)->getType()){
              // statement bindings (sqlite3_bind_text, etc.) 
        }
    }

    int32 rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return rc;
}

// executePrepared
ResultSetPtr executePrepared(const char *query, const vector<DBData*> *args){
    ResultSetPtr res = ResultSetPtr(new ResultSet);
    sqlite3_stmt *stmt;
    int32_t rs = sqlite3_prepare_v2(handle, query, strlen(query), &stmt, NULL);

    for(uint32 i = 0;args && i < args->size(); i ++){
        switch(args->at(i)->getType()){
              // statement bindings (sqlite3_bind_text, etc.) 
        }
    }
    int32 count = sqlite3_column_count(stmt);
    int32 rc;
    ResultRow row;
    int32 rows = 0;
    while((rc = sqlite3_step(stmt)) == SQLITE_ROW){
         rows ++;
         for(int32 i = 0; i < count; i++){
              // multiple row.push_back-s for all columns
         }
         res->push_back(row);
         row.clear();
    }
    sqlite3_finalize(stmt);
    return res;
}