Database Page Validation Failure During B-Tree Cursor Initialization

Page Number Mismatch and Corruption Chain Analysis

The assertion failure in getAndInitPage() occurs when SQLite’s B-tree module detects an invalid page number during cursor initialization. This failure manifests as:

sqlite3.c:70965: int getAndInitPage(...): Assertion '(*ppPage)->pgno==pgno || CORRUPT_DB' failed

This indicates one of two scenarios:

1. The retrieved database page’s header contains a page number that doesn’t match the expected pgno value
2. The database corruption flag (CORRUPT_DB) wasn’t properly set despite structural inconsistencies

The error chain in the test script reveals a progression from operational limits (disk full) to irreversible corruption:

1. database or disk is full during initial VACUUM
2. Cascading malformed database errors on subsequent operations
3. Final assertion failure during table deletion

Key components interacting in this failure:

• Page cache subsystem: Manages database page retrieval and locking
• B-tree cursor logic: Handles page navigation for data operations
• VACUUM command: Rebuilds database file by copying pages
• Auto-vacuum: Adjusts page pointers during delete operations
• Pgno validation: Cross-checks page numbers against B-tree expectations

Database Configuration Extremes and Transaction Boundary Violations

Cause 1: Aggressive Page Count Limitation

PRAGMA max_page_count=2 forces the database file to shrink to 2 pages (typically 8KB). With auto_vacuum=1 enabled, SQLite attempts to maintain contiguous free pages but cannot accommodate the 1001-row insert from the recursive CTE. This creates:

• Page allocation starvation: INSERT operation exhausts available pages
• Transaction journal overflow: Without rollback space (journal_mode=off)
• Metadata corruption: System tables cannot update structure details

Cause 2: Disabled Journaling With Destructive Operations

PRAGMA journal_mode=off (assuming ‘o’ was intended as ‘off’) removes atomic transaction protection. When combined with VACUUM – which performs full database rewrites – any interruption leaves no recovery path. Two consecutive VACUUM commands compound this:

• First VACUUM fails due to max_page_count=2, leaving partial writes
• Second VACUUM attempts to rebuild an already broken page map

Cause 3: Premature Page Recycling

The auto_vacuum=1 mode immediately reuses pages from dropped tables. With max_page_count=2, SQLite’s free page list becomes contaminated:

1. INSERT into t0 allocates pages up to max_page_count
2. DROP TABLE t0 marks all its pages as free
3. CREATE TABLE t(Y) attempts to reuse free pages
4. Page numbering conflicts arise when old t0 pages get reassigned

Cause 4: Debug Build Sensitivity

Compilation with -DSQLITE_DEBUG enables internal sanity checks that retail builds skip. The assertion failure specifically triggers because:

• Debug builds verify pgno consistency at cursor init
• Retail builds would return SQLITE_CORRUPT without crashing
• Tree tracing flags amplify page validation checks

Page Cache Forensic Analysis and Recovery Protocol

Step 1: Reproduce With Page Trace Logging

Re-run the test script with additional debug flags to capture page states:

export CFA="\
-DSQLITE_DEBUG \
-DSQLITE_ENABLE_TREETRACE \
-DSQLITE_DBSTATUS_LOOKASIDE_USED \
-DSQLITE_DIRECT_OVERFLOW_READ \
-DSQLITE_DEBUG_PAGECACHE"

Monitor page acquisition:

PRAGMA cell_size_check=ON;
PRAGMA page_count;
SELECT * FROM sqlite_schema WHERE name NOT LIKE 'sqlite_%';

Step 2: Analyze Vacuum Sequence Failures

The failed VACUUM attempts indicate broken page chains. Use sqlite3_analyzer on the corrupted database to map page ownership:

./sqlite3 corrupted.db 'PRAGMA writable_schema=ON;' 
./sqlite3_analyzer corrupted.db > analysis.txt

Check for:

• Multiple roots in sqlite_schema table (indicates B-tree corruption)
• Free pages marked as in-use
• Page size mismatches between header and parent indexes

Step 3: Validate Journal Mode Impact

Test journal mode combinations to isolate corruption vectors:

PRAGMA journal_mode=WAL; -- Test with write-ahead logging
PRAGMA journal_mode=TRUNCATE; -- Default rollback journal
PRAGMA journal_mode=OFF; -- Original failing case

Measure crash consistency using:

./mptester test.db crash-consistency-test.sql

Step 4: Page Size and Count Calibration

Adjust max_page_count and auto_vacuum settings systematically:

PRAGMA auto_vacuum=NONE; -- Disable page moving
PRAGMA max_page_count=2000; -- Allow growth
PRAGMA page_size=4096; -- Larger pages reduce fragmentation

Monitor page allocation patterns with:

PRAGMA schema.page_cookie; -- Tracks schema changes
PRAGMA freelist_count; -- Count of unused pages

Step 5: Repair Corrupted Databases

When encountering "malformed database" errors:

1. Immediately stop write operations
2. Dump salvageable data:

./sqlite3 corrupted.db .dump > backup.sql

3. Use .recover command:

PRAGMA writable_schema=RESET;
PRAGMA quick_check; -- Identify corruption location
PRAGMA integrity_check;

4. If unrecoverable, use third-party tools like SQLite Repair Kit or Disksector.

Step 6: Hardening Against Allocation Failures

Modify the test script to handle full database errors gracefully:

PRAGMA soft_heap_limit=1; -- Force early allocation failures
BEGIN;
INSERT INTO ... -- Wrap in transaction
COMMIT;

Handle errors in application code:

if( sqlite3_exec(db,"VACUUM",0,0,&errmsg)!=SQLITE_OK ){
  if( strstr(errmsg,"full") ){ /* Expand database or alert */ }
}

Step 7: Debug Build-Specific Assertion Mitigation

For development environments triggering getAndInitPage assertions:

1. Override the assertion with custom validation:

sqlite3_test_control(SQLITE_TESTCTRL_ASSERT, my_assert_handler);

2. Enable corruption propagation:

PRAGMA cell_size_check=ON;
PRAGMA recursive_triggers=OFF; -- Prevent trigger cascades

3. Use sqlite3_db_status() to monitor page cache health.

Final Configuration Recommendations

1. Avoid journal_mode=OFF for schema changes and VACUUM operations
2. Set max_page_count to reasonable limits (≥64 for most apps)
3. Combine auto_vacuum=FULL with periodic VACUUM maintenance
4. Enable PRAGMA busy_timeout to handle concurrent access
5. Use PRAGMA page_size matching OS allocation units (4KB blocks)

This comprehensive approach addresses both the immediate assertion failure and underlying configuration flaws that lead to database corruption. Subsequent testing should focus on boundary conditions around page allocation and transaction durability.