Manual checkpoint fails after setting WAL mode on new database and no writes performed

(1) By anonymous on 2022-02-09 15:10:05 [source]

Hi,

We encountered a problem when using a background connection solely for checkpoint purposes, and separate read/write connections (with automatic checkpoints disabled).

Steps to reproduce:

Open a connection to a new database
Set WAL mode on the connection
Run sqlite3_wal_checkpoint_v2(): this returns SQLITE_OK, but with pnLog and pnCkpt set to -1, meaning the checkpoint could not run due to an error or because the database is not in WAL mode.

After writing to the database from a secondary connection, the checkpoint still fails. If you write once from the first connection, the checkpoint succeeds.

Reopening the database (even with no writes performed) makes the checkpoint succeed as well.

Test case (uncomment the user_version write to make it pass):

sqlite3* checkpointer;
sqlite3_open("db.sqlite3", &checkpointer);
sqlite3_exec(checkpointer, "PRAGMA journal_mode=WAL;", 0, 0, 0);
sqlite3_exec(checkpointer, "PRAGMA synchronous=NORMAL;", 0, 0, 0);

//sqlite3_exec(checkpointer, "PRAGMA user_version=0;", 0, 0, 0);

int pnLog = 0;
int pnCkpt = 0;
auto resultCode = sqlite3_wal_checkpoint_v2(checkpointer, nullptr, SQLITE_CHECKPOINT_PASSIVE, &pnLog, &pnCkpt);
if (resultCode == SQLITE_OK && pnLog == -1 && pnCkpt == -1) {
   assert(false);
}

It fails because in sqlite3PagerCheckpoint, pPager->pWal is NULL.

SQLite version: 3.36.0

(2) By Richard Hipp (drh) on 2022-02-09 15:41:21 in reply to 1 [link] [source]

WAL mode is not a property of the database connection - it is a property of the database file. It can only be changed when there is a single connection to the database file. It sounds like you have two connections open on the same database file, and you are trying to change the journal mode from DELETE to WAL. That is not allowed, as it would delete information out from under the other database connection.

(4) By anonymous on 2022-02-09 16:04:39 in reply to 2 [link] [source]

Hi Richard,

WAL mode is enabled using the first and only connection to the database. That connection is used for checkpointing. There is no concurrent behavior, other connections are opened later.

The test case only has a single connection. Is it expected to fail in this way?

(5) By Richard Hipp (drh) on 2022-02-09 16:18:05 in reply to 4 [link] [source]

OK. I'll work on it. I see that you already have a work-around which is to run "PRAGMA user_version=0" after setting WAL mode to force transaction comment and thus create the WAL file. I don't know yet if I will "fix" this so that it works without running a transaction of some kind, or merely document the current behavior - it depends on performance implications.

(3.1) By Richard Hipp (drh) on 2022-02-09 15:41:35 edited from 3.0 in reply to 1 [link] [source]

Deleted

(6) By Richard Hipp (drh) on 2022-02-09 18:55:45 in reply to 1 [link] [source]

Should now be fixed on trunk

Problem Analysis

The following notes are for historical reference - in case in 10 years we come back and want to know "why is this code here?".

When a "PRAGMA journal_mode" statement changes to WAL mode, that statement itself cannot open the WAL file, since the transaction that makes this change is in rollback mode. The opening of the WAL file must be deferred until the start of the next transaction.

But then if sqlite3_wal_checkpoint() is run without an intervening transaction, it does not have a WAL file to checkpoint, and fails. The sqlite3_wal_checkpoint() cannot open the WAL file itself, because that can only be done from within a transaction.

The solution is tricky but seems to work: If sqlite3_wal_checkpoint() sees that the connection is in WAL mode but the WAL file is not open, it executes an innocuous PRAGMA statement that starts a transaction (both on "main" and on all ATTACH-ed databases). That transaction will cause the WAL file to be opened so that the sqlite3_wal_checkpoint() can proceed.