Resolving SQLite Database Corruption: Page Reuse Errors and showdb Double Free Fixes

Analyzing Page Reuse Errors and Memory Corruption in SQLite’s showdb Tool

Understanding Overflow Page Conflicts in a Corrupted SQLite Database

The core issue revolves around a corrupted SQLite database file that exhibits page reuse conflicts, where multiple database structures claim ownership of the same physical pages. This manifests in showdb tool output as entries like:

ERROR: page 641 used multiple times:
ERROR:  previous: overflow 1 from cell 0 of page 128
ERROR:  current: overflow 1 from cell 0 of page 128

These errors indicate that page 641 is referenced as an overflow page by two different cells, both located on page 128. The corruption likely stems from improper handling of overflow chains during database modifications (e.g., inserts, updates, or deletes) that were interrupted or executed without proper transactional guarantees.

Further analysis reveals deeper structural issues. For example:

ERROR: page 128 used multiple times:
ERROR:  previous: leaf of table [sqlite_schema], child 1 of page 146247, 9 rows
ERROR:  current: root leaf of table [t244f7970_2Dfcdc...], 9 rows

Here, page 128 is claimed by both the sqlite_schema (the internal schema storage table) and a user-defined table. This violates SQLite’s requirement that each page belong to exactly one b-tree structure. Such conflicts destabilize the database’s logical consistency, making recovery non-trivial.

The showdb tool itself encountered a double-free memory corruption due to improper handling of these errors. The original code attempted to sqlite3_free() a pointer stored in zPageUse[pgno] before reassigning it, but in cases where multiple errors occurred on the same page, this led to freeing memory that was already deallocated. The patch resolved this by removing the sqlite3_free() call, ensuring the tool could process the corrupted database without crashing.

Root Causes of Page Reuse and Memory Corruption in showdb

1. Database Corruption from Interrupted Writes

SQLite relies on atomic commits and rollback journals/WAL files to ensure consistency. If a write operation (e.g., updating overflow chains) is interrupted (power loss, application crash), the database may enter a state where page linkages are partially updated. For example:

• A cell on page 128 might have been modified to point its overflow chain to page 641, but the original owner of page 641 (e.g., another table) was not properly updated to relinquish its claim.
• The sqlite_schema page conflict suggests corruption in the schema layer itself, which is catastrophic because this table defines the structure of all other database objects.

2. showdb’s Page Tracking Logic Limitations

The showdb tool maps page numbers to their usage descriptions (e.g., “overflow 1 from cell 0 of page 128”). The original implementation stored these descriptions in zPageUse[], an array of dynamically allocated strings. When a page was flagged as reused, the tool attempted to:

1. Print the conflict.
2. Free the existing description string.
3. Assign a new description.

However, in scenarios where the same page was flagged multiple times (e.g., page 128 appearing in multiple btrees), the sqlite3_free(zPageUse[pgno]) call would deallocate memory, but subsequent assignments to zPageUse[pgno] could trigger use-after-free or double-free errors if the page was reused yet again.

3. Schema and B-Tree Integrity Violations

The presence of invalid page references in critical structures like the sqlite_schema table or root pages of user tables indicates that the corruption is not confined to a single subtree. For instance:

• Page 79 is claimed by two different tables, one as a leaf node and another as a root leaf.
• Page 62 alternates between being a root leaf and a child of page 1004.

This suggests that the freelist (a list of unused pages maintained by SQLite) was corrupted, causing pages to be allocated to multiple btrees.

Diagnosing Corruption and Restoring Stability

Step 1: Validate the Database with PRAGMA integrity_check

Before diving into low-level tools, run:

PRAGMA integrity_check;

This will report high-level inconsistencies. In severe corruption cases, this command may fail to execute or return generic errors, necessitating deeper analysis.

Step 2: Interpret showdb Output for Structural Clues

The showdb tool walks the database page-by-page, reconstructing b-tree linkages. Key observations from the output:

• Page 128 Conflicts: Its dual role in sqlite_schema and a user table implies schema damage. The sqlite_schema table is a b-tree of type 5 (leaf-table), storing SQL definitions. Corruption here means the database cannot reliably parse its own schema.
• Overflow Chain Conflicts: Pages 641, 604, etc., being referenced by multiple cells on page 128 suggest that page 128’s cells point to overflow pages that are also claimed elsewhere. This could mean:
  • The original overflow chain was not properly truncated during a delete/update.
  • Page 128 itself is corrupted, with cell offsets pointing to invalid locations.

Step 3: Apply the showdb Patch to Avoid Tool Crashes

The double-free in showdb arises because the tool’s zPageUse[] array tracks page usage with dynamically allocated strings. The original code:

if( zPageUse[pgno]!=0 ){
  printf("ERROR: page %d used multiple times:\n", pgno);
  printf("ERROR:  previous: %s\n", zPageUse[pgno]);
  printf("ERROR:  current: %s\n", zMsg);
  sqlite3_free(zPageUse[pgno]);  // Freed here
}
zPageUse[pgno] = zMsg;           // Reassigned without reallocating

If pgno is reused a third time, zPageUse[pgno] would point to freed memory. The fix removes the sqlite3_free(), sacrificing memory cleanup for stability:

if( zPageUse[pgno]!=0 ){
  // Error messages printed, but no free()
}
zPageUse[pgno] = zMsg;

This prevents crashes but leaks memory. A better approach (as implemented in the SQLite update) resets zPageUse[pgno] to NULL after logging the conflict, ensuring subsequent uses don’t trigger invalid frees.

Step 4: Salvage Data Using .dump and .recover

If PRAGMA integrity_check fails, attempt to export data:

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

If this fails due to schema corruption, use the SQLite Recovery Extension (e.g., sqlite3 corrupted.db ".recover" | sqlite3 clean.db), which bypasses the schema and reconstructs data by scanning all pages.

Step 5: Repair Using Vacuum or Manual Schema Reconstruction

For less severe corruption:

PRAGMA writable_schema = 1;
-- Manually correct entries in sqlite_schema
PRAGMA writable_schema = 0;
VACUUM;

The VACUUM command rebuilds the database, reallocating pages and reconstructing b-trees.

Step 6: Prevent Future Corruption

• Ensure proper file system synchronization: Avoid storing databases on network filesystems without atomic write guarantees.
• Use transactions: Group related changes in transactions to ensure atomicity.
• Regularly back up the database: Use sqlite3_backup_init() or offline copies.

This guide provides a comprehensive pathway from diagnosing page conflicts using showdb to recovering data and hardening the database against future corruption. By understanding the interplay between SQLite’s storage engine and the tooling used to probe it, developers can effectively navigate even severe corruption scenarios.