SQLite Forum

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

```