SQLite User Forum

bug report: invalid WAL frames written after rollback
Login

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.