Assertion Failure in sqlite3VdbeRecordCompareWithSkip Due to Database Corruption

Database Corruption Chain from Disk Full Conditions and Malformed Schema Operations

Issue Overview
The assertion failure in sqlite3VdbeRecordCompareWithSkip occurs during record comparison operations when the database engine encounters inconsistencies in stored data structures. This specific failure is a symptom of deeper database corruption caused by a sequence of disk space exhaustion, schema manipulation errors, and invalid page configurations. The root cause involves three interrelated factors:

1. Disk Space Exhaustion During VACUUM Operations:
   The script sets PRAGMA max_page_count=2 and PRAGMA auto_vacuum=incremental, which restricts the database to two pages. Executing VACUUM under these conditions triggers a disk-full error (error code 13) because SQLite cannot reorganize pages within the constrained page count. The first VACUUM fails, leaving the database in an inconsistent state. Subsequent operations compound the corruption.

2. Malformed Index and Data Manipulation:
   The CREATE INDEX x ON t1("b") followed by UPDATE t1 SET b=zeroblob(3000) writes a 3000-byte BLOB into a column indexed by x. This forces SQLite to generate overflow pages, which conflict with the earlier max_page_count restriction and the corrupted state from the failed VACUUM. The index structure becomes unreadable due to truncated or invalid page linkages.

3. Debug-Build Assertions and Corrupted Header Metadata:
   The compilation flags enable strict internal sanity checks (e.g., SQLITE_DEBUG). When the corrupted index is accessed during the DELETE FROM t1 WHERE rowid operation, the sqlite3VdbeRecordCompareWithSkip function detects header size mismatches (idx1<=szHdr1 assertion failure). This indicates that the database engine is attempting to compare records with malformed header data, a direct consequence of index/page corruption.

Failure Progression and Symptom Correlation
The initial disk-full error during VACUUM creates a malformed database image. Subsequent VACUUM, DROP TABLE, and INSERT operations operate on this corrupted image, leading to cascading failures. The final DELETE operation triggers index traversal, exposing the corrupted B-tree structure to the query executor. The sqlite3VdbeRecordCompareWithSkip function is invoked during index lookups or row deletion comparisons, where it validates record headers against stored sizes. Corrupted headers violate the idx1<=szHdr1 invariant, crashing the debug build.

Root Causes: Page Allocation Errors, Schema Misconfiguration, and Overflow Chain Corruption

1. Page Allocation Contention from max_page_count=2
The max_page_count=2 pragma limits the database to two pages. In incremental auto_vacuum mode, SQLite attempts to reuse free pages during VACUUM, but the two-page limit prevents necessary page reallocations. This forces the VACUUM to fail with a disk-full error, leaving the database in a state where free page management metadata is inconsistent.

2. Overflow Page Chain Corruption via Zeroblob Insertion
The zeroblob(3000) operation inserts a BLOB requiring multiple overflow pages. However, the corrupted free page list from the earlier VACUUM failure causes SQLite to misallocate overflow pages. The index x on column b references these invalid pages, creating B-tree structures with broken parent-child linkages.

3. Header Size Mismatch in Index Records
The sqlite3VdbeRecordCompareWithSkip function assumes that record headers (storing field sizes and types) are consistent with the physical record size. Corruption in overflow chains or index pages can cause header fields to reference out-of-bounds offsets, violating the idx1<=szHdr1 assertion.

4. Test Control Interference (.testctrl n 3)
The .testctrl n 3 command simulates a failure mode for testing purposes, potentially disabling critical recovery mechanisms. This exacerbates the impact of the disk-full error by preventing SQLite from rolling back incomplete transactions, leaving the database in an unrecoverable state.

Repair Strategy: Database Recovery, Schema Redesign, and Debugging Mitigations

Step 1: Immediate Database Recovery and Integrity Checks

• Run PRAGMA integrity_check: This identifies corrupted pages and invalid schema elements. Expect errors like "malformed database page" or "invalid page type".
• Restore from Backup: If corruption is widespread, replace the database file from a known-good backup.
• Use .recover Command-Line Tool: SQLite’s .recover utility can extract data from corrupted databases, which can then be reimported into a new database.

Step 2: Schema Modifications to Prevent Corruption

• Avoid max_page_count with auto_vacuum=incremental: These settings are mutually incompatible, as incremental vacuuming requires free page space for reorganization.
• Replace zeroblob with Dynamic Size Management: Use BLOB columns with size constraints or external storage for large objects to prevent overflow chain fragmentation.
• Remove Unused Indexes and Constraints: The CREATE INDEX c ON v1(0) WHERE 0 creates an empty partial index, which wastes schema metadata space and complicates vacuum operations.

Step 3: Debug Build Mitigations and Assertion Handling

• Disable Debug-Only Assertions: In production builds, the CORRUPT_DB macro allows SQLite to return generic errors instead of crashing. Recompile without SQLITE_DEBUG to avoid assertion failures.
• Monitor Disk Space Programmatically: Before executing VACUUM or large writes, check available disk space using OS APIs to prevent disk-full conditions.
• Isolate Test Control Commands: Ensure .testctrl is never used in production scripts, as it destabilizes the database engine’s error handling.

Step 4: Query and Transaction Flow Adjustments

• Wrap Schema Changes in Explicit Transactions:

  BEGIN;  
  CREATE TABLE ...;  
  CREATE INDEX ...;  
  COMMIT;  
  

  This minimizes partial schema updates during disk-full errors.

• Use PRAGMA journal_mode=WAL: Write-Ahead Logging provides better concurrency and reduces the risk of corruption during vacuum operations.
• Implement Retry Logic for Disk-Full Errors: Catch error code 13 and trigger garbage collection or user intervention before retrying.

Step 5: Advanced Corruption Diagnostics

• Analyze Database Pages with sqlite3_analyzer: This tool dumps page structures, helping identify corrupted B-trees or overflow chains.
• Enable I/O Tracing: Compile with -DSQLITE_ENABLE_IOTRACE to log low-level I/O operations and pinpoint write errors during VACUUM.
• Inspect Virtual Database Engine (VDBE) Output: Run queries with EXPLAIN to see how the VDBE accesses indexes and tables, revealing misoptimized plans that expose corruption.

Final Code Fixes and Workarounds

• Patch sqlite3VdbeRecordCompareWithSkip: Add explicit corruption checks before header parsing:

  if( idx1 > szHdr1 ){  
    return SQLITE_CORRUPT; // Instead of asserting  
  }  
  

• Upgrade to SQLite 3.43.0+: Later versions include improved corruption resilience and more descriptive error messages.
• Use SQLITE_DEFAULT_AUTOVACUUM=0: Disable auto-vacuum unless required, reducing background page reallocation complexity.

By addressing the root causes of page allocation contention, schema fragility, and debug-time assertions, developers can eliminate the sqlite3VdbeRecordCompareWithSkip assertion failure while hardening the database against future corruption scenarios.