Assertion Failure in sqlite3WalFrames During VACUUM: Page Size Mismatch

Understanding the sqlite3WalFrames Assertion Failure: Page Size Validation

The core issue revolves around an assertion failure triggered during execution of the VACUUM command in SQLite, specifically within the sqlite3WalFrames function. The assertion (int)pWal->szPage == szPage compares two page size values:

1. pWal->szPage: The page size stored in the Write-Ahead Logging (WAL) structure tied to the database connection.
2. szPage: The page size parameter passed to sqlite3WalFrames during WAL frame writing.

When these values diverge, SQLite halts execution with a fatal error. This failure occurs exclusively in debug builds with assertions enabled, as non-debug builds might exhibit silent corruption or undefined behavior instead. The VACUUM command’s interaction with WAL-mode databases creates a unique stress test for page size consistency across multiple subsystems.

Root Causes of Page Size Mismatch in WAL Transactions

Database File Corruption or Improper Initialization
A malformed database header can report an incorrect page size during WAL initialization. SQLite derives pWal->szPage from the database file’s header when opening a WAL connection. If the header’s page size field (bytes 16-17) contains invalid data (e.g., zero, non-power-of-two values), subsequent WAL operations inherit this corrupted value. The malform database provided exhibits this behavior, with its header potentially altered by external tools or incomplete write operations.

WAL File Desynchronization
The malform-wal file might contain frame data incompatible with the current database page size. WAL files store frames aligned to the database page size, with each frame header containing a salt value derived from the original database parameters. If the WAL file was created with a different page size (e.g., from a prior database configuration), attempts to replay frames during VACUUM trigger size mismatches. This is exacerbated when WAL files are reused across databases or manually manipulated.

VACUUM Command Edge Cases
The VACUUM process creates a temporary database to rebuild the main database file. In WAL mode, this involves:

• Committing all changes from the WAL to the main database (checkpointing)
• Creating a new temporary database with the same page size
• Copying data from the original to the temporary database
• Replacing the original with the temporary

If the temporary database’s page size initialization fails to match the original (due to filesystem quirks or interrupted prior operations), subsequent WAL operations on the new database inherit an inconsistent page size. Debug builds catch this when sqlite3WalFrames validates parameters during the copy phase.

Compilation Flag Interactions
While the provided compilation flags enable debugging features (SQLITE_DEBUG, SQLITE_ENABLE_STAT4), none directly alter page size handling. However, SQLITE_COUNTOFVIEW_OPTIMIZATION and cursor hinting could influence query planner decisions during VACUUM, indirectly affecting how database pages are accessed and written. A mismatch between optimized page access paths and WAL frame writing routines might surface latent corruption issues.

Comprehensive Diagnosis and Resolution Strategies

Step 1: Validate Database and WAL File Integrity
Execute PRAGMA integrity_check; on the malform database before running VACUUM. This detects header corruption, invalid page sizes, or WAL frame misalignments. If the integrity check reports errors, recover data using .dump and rebuild the database. For the WAL file, use sqlite3 malform 'PRAGMA wal_checkpoint(TRUNCATE);' to attempt checkpointing and WAL truncation. If checkpointing fails, delete the WAL file and reopen the database in DELETE journaling mode temporarily.

Step 2: Inspect Page Size Consistency Across Components
Extract the page size from the database header using a hex editor. Offset 16 (0x10) contains the page size as a big-endian 2-byte integer. For malform, this should match PRAGMA page_size; output. Next, inspect the WAL file header (first 32 bytes):

• Bytes 24-25: Salt-1 (derived from database page size)
• Bytes 28-29: Salt-2 (derived from database file change counter)

Compare these values with the database header’s page size and change counter (offset 24, 4-byte big-endian). Mismatched salts indicate WAL/database desynchronization.

Step 3: Debug SQLite with Custom Instrumentation
Recompile SQLite with additional print statements in sqlite3WalFrames to log pWal->szPage and szPage values. Modify the assertion line (sqlite3.c:67138) to:

assert( (int)pWal->szPage == szPage && "WAL page size mismatch. Expected %d, got %d", szPage, pWal->szPage);

(Note: Actual implementation requires using fprintf before assert due to assertion message limitations.) This reveals whether the mismatch stems from an invalid szPage parameter or corrupted pWal->szPage.

Step 4: Analyze VACUUM’s Temporary Database Creation
Trace the VACUUM command’s execution using SQLite’s debugging shell (sqlite3 -v malform). Focus on temporary database initialization steps:

• sqlite3RunVacuum calls execSqlFmt to create vacuum_db
• Temporary database page size is set via PRAGMA vacuum_db.page_size = %d
• Data is copied using INSERT INTO vacuum_db...

Verify that the page_size PRAGMA succeeds and that subsequent operations on vacuum_db use the correct size. If the temporary database defaults to a different page size (e.g., due to filesystem block size interference), explicitly set page_size before copying data.

Step 5: Test with Alternative Page Sizes and Journal Modes
Reproduce the issue with varying page sizes:

PRAGMA page_size = 4096; -- Reinitialize database with explicit size
VACUUM;

If the assertion persists, the corruption likely resides in WAL handling logic rather than page size configuration. Switch to DELETE journal mode (PRAGMA journal_mode = DELETE;) before running VACUUM to bypass WAL entirely. Successful execution implicates WAL subsystem bugs; continued failures suggest deeper corruption.

Step 6: Patch and Rebuild SQLite with Workarounds
If immediate data recovery is critical, modify the SQLite source to suppress the assertion or force page size alignment:

// In sqlite3WalFrames, replace:
assert( (int)pWal->szPage == szPage );
// With:
if( (int)pWal->szPage != szPage ) {
  sqlite3_log(SQLITE_ERROR, "Page size mismatch: %d vs %d", pWal->szPage, szPage);
  return SQLITE_CORRUPT;
}

Rebuild SQLite and attempt data extraction. This converts a fatal assertion into an error, potentially allowing continued operation. However, this risks data corruption and should only be used for recovery purposes.

Step 7: Leverage Backup API for Data Recovery
Bypass VACUUM by using the Online Backup API to clone the database:

sqlite3_backup *pBackup = sqlite3_backup_init(dest_db, "main", source_db, "main");
if(pBackup){
  sqlite3_backup_step(pBackup, -1); // Copy entire database
  sqlite3_backup_finish(pBackup);
}

This method often handles corruption more gracefully than VACUUM, as it reconstructs the database through sequential page copying rather than in-place reorganization.

Step 8: Engage in Forensic Analysis of Provided Artifacts
Using the malform and malform-wal files:

1. Database Header Analysis:

   hexdump -n 32 malform
   0000000 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00
   0000010 00 10 00 01 01 00 40 20 20 00 00 00 00 00 00 00
   

   Bytes 16-17 (00 10) indicate a page size of 4096 (0x1000 in big-endian). Verify that this matches PRAGMA page_size.

2. WAL Header Analysis:

   hexdump -n 32 malform-wal
   0000000 37 7f 06 82 53 51 4c 69 74 65 20 66 6f 72 6d 61
   0000010 74 20 33 00 00 00 10 00 04 00 00 00 00 00 00 00
   

   Bytes 24-25 (00 10) should match the database page size. Bytes 28-29 (04 00) represent Salt-2; compare with the database header’s change counter at offset 24 (00 00 00 00 in the example). A non-zero Salt-2 with a zero change counter indicates WAL/database divergence.

Step 9: Investigate SQLite Version-Specific Bugs
The commit 92ade220dcf5c1b7 corresponds to SQLite 3.41.2 (2023-03-22). Review the changelog for WAL or VACUUM-related fixes post this version. If the bug was patched in a later release (e.g., 3.42.0), upgrading resolves the issue. For unresolved bugs, submit a minimal reproducer to SQLite’s GitHub issues with disassembly of the failing function.

Step 10: Implement Preventive Measures for Production Systems

• Regular Integrity Checks: Schedule PRAGMA quick_check during low-load periods to detect early signs of corruption.
• WAL Size Monitoring: Use PRAGMA wal_checkpoint(TRUNCATE); post-transaction to keep WAL files small.
• Page Size Immutability: Initialize databases with PRAGMA page_size before creating tables, and avoid post-hoc changes.
• Backup Strategy: Replace VACUUM with incremental ATTACH DATABASE backups to avoid WAL interactions during maintenance.

By methodically validating page size consistency across database components, instrumenting SQLite for targeted debugging, and employing alternative data recovery pathways, developers can resolve the sqlite3WalFrames assertion failure while hardening systems against similar WAL-related corruption scenarios.