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;
}

One test I can think of is to have your App close the SQLite database at some point in the procedure and reopen it again. Does this make the WAL file magically appear ? This is just for diagnostic purposes, to investigate the problem. You should not need to do this in production code.

Another test would be to run your test code on a desktop computer, rather than the Android hardware. Do you get the same behaviour ?

A recent question to this forum about the Android platform leads me to believe that Android does some sort of internal caching, and doesn't show files on disk until they get big (and the cache busts) or they are closed.

The person who started that thread was unable to reproduce their problem on their computer: on computer the WAL and shm files showed up when expected. On Android they were sometimes visible and sometimes not visible.

The App running SQLite ran correctly until the App was killed, accessing the data correctly. It's just that a directory listing didn't show any actual file. It caused a problem only when the SQLite App was terminated by lost power, or by being killed by the operating system. If the Android App was allowed to quit normally, the WAL file appeared at that time.

Since you are using a smart terminal and not a mobile phone, perhaps you won't have a problem with this. Your users are less likely to run out of power.

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

Something just occurred to me. SQLite does automatically delete the WAL file if there are no open connections to it. Is it possible that you're checking while all connections are closed ? Perhaps whatever SQLite library you're using closes connections automatically if your program is not in the foreground, or something.

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.

How do you mean "connection is still open". What proof do you have that this is the case? In other words, how do you know the connection is open?

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

All that on your phone? But is something really wrong? Not seeing the file is not valid. You have to program that part. Does the same happen without loose threads? Do not leave any loose thread. Could it be that SQLite gives you Busy? :-)

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