bug report: invalid WAL frames written after rollback
(1) By Joe Lee (jleexx) on 2025-06-17 03:02:42 [source]
While testing our application, I found a case where SQLite appears to write invalid WAL frames after rolling back a savepoint. I managed to reduce it to this test case, based on walcksum-2.1
in test/walcksum.test
(originally reproduced against version 3.44.0
, but seems to also reproduce against 3.50.1
, and presumably others):
do_test walcksum-2.2 {
forcedelete test.db test.db-wal test.db-journal
sqlite3 db test.db
execsql {
PRAGMA synchronous = NORMAL;
PRAGMA page_size = 4096;
PRAGMA auto_vacuum = FULL;
PRAGMA journal_mode = WAL;
PRAGMA cache_size = 1;
CREATE TABLE t1 (i INTEGER PRIMARY KEY, s TEXT);
PRAGMA wal_checkpoint;
/* commits pages 1,2,3,4 to frames 1-4: */
INSERT INTO t1 (i, s) VALUES (0, randomblob(4096));
/* commits pages 1,2,3,5 to frames 5-8: */
INSERT INTO t1 (i, s) VALUES (1, randomblob(4096));
SAVEPOINT one;
/* spills pages 6,7,8,9,10 to frames 9-13: */
INSERT INTO t1 (i, s) VALUES (2, randomblob(4096));
INSERT INTO t1 (i, s) VALUES (3, randomblob(4096));
INSERT INTO t1 (i, s) VALUES (4, randomblob(4096));
INSERT INTO t1 (i, s) VALUES (5, randomblob(4096));
INSERT INTO t1 (i, s) VALUES (6, randomblob(4096));
INSERT INTO t1 (i, s) VALUES (7, randomblob(4096));
/* spills pages 11,2,12,2 to frames 14,15,16, rewriting frame 15: */
INSERT INTO t1 (i, s) VALUES (8, randomblob(4096));
/* rolls back to frame 8, writes commit frame 9 with zero checksum.
* restores current checksum to frame 14's, skips frame 9 rewrite: */
ROLLBACK TO one;
RELEASE one;
/* commits pages 1,2,3,6 to frames 10-13: */
INSERT INTO t1 (i, s) VALUES (9, randomblob(4096));
}
forcecopy test.db test2.db
forcecopy test.db-wal test2.db-wal
sqlite3 db2 test2.db
execsql {
PRAGMA integrity_check;
SELECT count(*) FROM t1;
} db2
} {ok 3}
catch { db close }
catch { db2 close }
The test fails because the restored database has 2 records instead of 3, because it is missing the record inserted after the rollback.
(2) By Richard Hipp (drh) on 2025-06-17 19:37:37 in reply to 1 [link] [source]
Thanks for the bug report. Dan has a fix now on trunk and on branch-3.50. It looks like this problem goes back to the wal-overwrite-frames optimization in 2016, first released in version 3.11.0.