SQLite Forum

Why data is lost in SQLite database with WAL mode on when connection is not closed properly?
Login

Why data is lost in SQLite database with WAL mode on when connection is not closed properly?

(1.1) By chromesearcher on 2021-07-29 09:49:16 edited from 1.0 [link]

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):
-------

0. Open application, open Connection 1, open Connection 2 (on the same DB)
1. Begin transaction (Connection 1)
2. `INSERT`          (Connection 1)
3. End transaction   (Connection 1)
4. Repeat 5 times steps 1-3 
5. `SELECT`          (Connection 1) // All data is present.
6. `SELECT`          (Connection 2) // All data is present.
7. **Close application (without closing Connection 1)**
8. Open application, open Connection 3
9. `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):
-------

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

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

0. Open application, open Connection 1, open Connection 2 (on the same DB)
1. Begin transaction (Connection 1)
2. `INSERT`          (Connection 1)
3. End transaction   (Connection 1)
4. Repeat 5 times steps 1-3 
5. `SELECT`          (Connection 1) // All data is present.
6. `SELECT`          (Connection 2) // All data is present.
7. **Perform WAL checkpoint**
8. Close application (without closing Connection 1)
9. Open application, open Connection 3
10. `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;
}
```

(2) By Dan Kennedy (dan) on 2021-07-29 11:08:53 in reply to 1.1 [link]

That's not supposed to happen. Although I suppose you already knew that.

Can you try to compose an example of the problem that we can use to reproduce it?

Dan.

(3.1) By Bill Wade (billwade) on 2021-07-29 12:12:04 edited from 3.0 in reply to 1.1 [link]

By default, the WAL is not fsync'd after every transaction (transactions aren't guaranteed to be durable).

I suspect that the file is fsync'd when the connection is closed, but I didn't see that with a quick look at the documentation.

If

(8) Close application (without closing Connection 1)

also involves crashing the OS, I'd say the behavior you describe is documented, unless you set pragma synchronous=FULL.

"A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash."

(4) By chromesearcher on 2021-07-29 12:25:49 in reply to 3.1 [link]

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?

(5) By chromesearcher on 2021-07-29 12:31:03 in reply to 2 [link]

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?

(6) By Richard Hipp (drh) on 2021-07-29 13:44:49 in reply to 5 [link]

> What should i add

Working code that we can compile and run to see the problem.  The code
fragment you supplied in your original post appears to be incomplete.
In other words, send in a reproducible test case.

(7) By Simon Slavin (slavin) on 2021-07-29 13:48:16 in reply to 4

I'm beginning to suspect this is an Android thing.  Please try

A) Run your original App (or an adapted version of it) on a computer, rather than your Android platform.  Kill it at the same place.

1) Check to see whether opening that database on the same computer retrieves the 'lost' data.
2) Do it again, but this time open the database after kill on your Android platform.

B) Then do it the other way. around. Prepare the database and WAL file on your android platform by killing the app as above.

 1) You already know that opening that database on the Android platform loses data.
 2) Do it again, instead of opening on Android, copy the database and WAL file to your computer, and open it on that.

(8) By chromesearcher on 2021-07-30 07:58:53 in reply to 6 [link]

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

(9.1) By chromesearcher on 2021-07-30 08:09:36 edited from 9.0 in reply to 7 [link]

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)

(10.1) By Simon Slavin (slavin) on 2021-07-30 12:39:43 edited from 10.0 in reply to 9.1 [link]

Deleted

(11) By Gunter Hick (gunter_hick) on 2021-09-02 14:01:49 in reply to 7 [link]

As confessed in another thread, the OP is running two different versions of SQLite within the same "app" and is unable to discern if he is doing so within different threads or different processes.

I guess it is just interference from using two different SQLite libraries, possibly compounded by loss of POSIX locks if runnign within the same process.

(12) By chromesearcher on 2021-09-07 09:24:18 in reply to 1.1 [link]

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