Corrupted SQLite Cell Count Automatically Fixed After Process Restart

Fragmentation Errors and Silent Corruption Repair in SQLite WAL Mode

When working with SQLite in Write-Ahead Logging (WAL) mode, database corruption can manifest in subtle and unexpected ways. One such scenario involves fragmentation errors reported during an integrity check, where the corruption appears to be silently repaired after a process restart. This behavior can be particularly perplexing when the corruption is intentionally introduced for testing purposes, such as modifying the cell count in a B-Tree page header. The issue becomes even more complex when considering the role of WAL recovery and checkpointing in the process. Understanding the interplay between these mechanisms is crucial for diagnosing and resolving such issues.

The core of the problem lies in the way SQLite handles corrupted pages during WAL recovery and checkpointing. When a page is corrupted, SQLite may attempt to repair it silently, especially if the corruption is minor and does not affect the overall structure of the database. However, this repair process can be influenced by the state of the WAL file and the specific type of checkpoint performed before the process restart. Additionally, the position of the corruption within the page can determine whether the corruption is repaired or ignored during recovery.

To fully grasp the issue, it is essential to delve into the specifics of how SQLite manages WAL files, performs recovery, and handles corrupted pages. This includes understanding the role of checksums in WAL frames, the impact of different checkpoint modes, and the conditions under which SQLite may silently repair corrupted pages. By examining these factors, we can identify the root causes of the observed behavior and develop effective troubleshooting strategies.

WAL Recovery and Checkpointing Behavior in Corrupted Databases

The behavior of SQLite in the presence of corrupted pages is influenced by several factors, including the type of corruption, the state of the WAL file, and the checkpointing mode used. When a database is operating in WAL mode, changes are first written to the WAL file before being applied to the main database file. This allows for concurrent read and write operations, but it also introduces additional complexity when dealing with corruption.

One key aspect of WAL mode is the use of checksums to verify the integrity of WAL frames. Each frame in the WAL file contains a checksum that is used to detect corruption. During WAL recovery, SQLite reads through the WAL file and applies valid frames to the database. If a frame is found to be corrupted (i.e., its checksum does not match), SQLite will stop applying frames from that point onward. This means that any changes made after the corrupted frame will be lost, and the database will revert to an earlier state.

In the context of the observed issue, the corruption introduced in the B-Tree page header may not be detected immediately if the checksum of the corresponding WAL frame is still valid. This can lead to a situation where the corruption is only detected during an integrity check, but is silently repaired during WAL recovery. The repair occurs because SQLite may overwrite the corrupted page with an older, uncorrupted version from the WAL file. This behavior is more likely to occur if the corruption is minor and does not affect the overall structure of the database.

Another factor that can influence the behavior is the type of checkpoint performed before the process restart. A standard checkpoint (SQLITE_CHECKPOINT_PASSIVE) will only write pages from the WAL file to the database if there are no active readers. However, a truncate checkpoint (SQLITE_CHECKPOINT_TRUNCATE) will forcibly truncate the WAL file, removing all frames that have not been applied to the database. This can prevent WAL recovery from accessing older, uncorrupted versions of the page, thereby avoiding the silent repair behavior.

Diagnosing and Resolving Silent Corruption Repair in SQLite

To diagnose and resolve the issue of silent corruption repair in SQLite, it is important to follow a systematic approach that includes verifying the integrity of the database, examining the state of the WAL file, and understanding the impact of different checkpointing modes. The following steps outline a comprehensive troubleshooting process:

Step 1: Verify Database Integrity
The first step in diagnosing corruption issues is to perform a full integrity check of the database. This can be done using the PRAGMA integrity_check command, which will scan the entire database and report any inconsistencies. In the case of the observed issue, the integrity check may report fragmentation errors or other anomalies that indicate corruption. It is important to note the specific pages and types of errors reported, as this information can help identify the root cause of the corruption.

Step 2: Examine the WAL File
After verifying the integrity of the database, the next step is to examine the state of the WAL file. This includes checking the size of the WAL file, the number of frames it contains, and the checksums of those frames. If the WAL file is still present after a checkpoint, it may contain older, uncorrupted versions of the pages that are being used during WAL recovery. This can be confirmed by increasing the log level in the application and monitoring the WAL recovery process. If the recovery process reports that frames are being recovered from the WAL file, this indicates that the corruption is being repaired silently.

Step 3: Modify the Corruption VFS
To further investigate the issue, it may be necessary to modify the custom VFS used to introduce corruption. Specifically, the VFS can be modified to corrupt the checksums of the WAL frames in addition to the cell count in the B-Tree page header. This will prevent SQLite from using the corrupted frames during WAL recovery, thereby avoiding the silent repair behavior. By comparing the behavior with and without checksum corruption, it is possible to determine whether the checksums are playing a role in the observed behavior.

Step 4: Perform a Truncate Checkpoint
Another approach to resolving the issue is to perform a truncate checkpoint before the process restart. This can be done using the PRAGMA wal_checkpoint = truncate command or by setting the checkpoint mode to SQLITE_CHECKPOINT_TRUNCATE. A truncate checkpoint will forcibly truncate the WAL file, removing all frames that have not been applied to the database. This will prevent WAL recovery from accessing older, uncorrupted versions of the pages, thereby avoiding the silent repair behavior. It is important to note that this approach may result in the loss of some changes if the WAL file contains unapplied frames.

Step 5: Monitor the Database After Restart
Finally, it is important to monitor the database after the process restart to ensure that the corruption has been properly addressed. This includes performing another integrity check to verify that the corruption has not been silently repaired. If the corruption is still present, it may be necessary to investigate other potential causes, such as issues with the custom VFS or the specific type of corruption being introduced.

By following these steps, it is possible to diagnose and resolve the issue of silent corruption repair in SQLite. The key is to understand the role of WAL recovery and checkpointing in the process, and to use this knowledge to develop effective troubleshooting strategies. With a thorough understanding of these mechanisms, it is possible to ensure the integrity of the database and prevent unexpected behavior in the presence of corruption.