SQLite Index Corruption: Causes, Diagnosis, and Repair

SQLite Index Corruption Manifesting as Missing Rows and Incorrect Entry Counts

SQLite index corruption is a critical issue that can disrupt database operations, leading to failed queries and malformed database states. The primary symptom of this issue is the SQLITE_CORRUPT error, which occurs when a SELECT query attempts to access data through a corrupted index. In the case under discussion, the corruption manifests as "row missing from index" and "wrong # of entries" errors, as reported by the PRAGMA integrity_check command. These errors indicate that the index structure, while seemingly intact at the b-tree level, contains inconsistencies in its data.

The affected indexes include a simple unique index on an integer column (sequence) and more complex indexes that rely on a custom function (customfn) to extract key-value pairs from a structured blob (body). The custom function, which behaves similarly to json_extract, has been in use for years without issues, making its role in the corruption unlikely but not impossible. The simplicity of the seq index further complicates the diagnosis, as it is unclear how such a straightforward index could become corrupted.

The database in question is running on SQLite 3.28, statically linked into an Android application. The corruption was detected after a power failure, which is a common cause of database corruption. However, the specific nature of the corruption—limited to indexes rather than the entire database file—suggests a more nuanced underlying cause.

Interrupted Write Operations and Custom Function Determinism

The most likely cause of SQLite index corruption in this scenario is interrupted write operations, particularly during power failures or system crashes. SQLite uses a write-ahead log (WAL) or rollback journal to ensure atomic transactions, but these mechanisms are not foolproof. If a write operation is interrupted, the index may end up in an inconsistent state, with missing rows or incorrect entry counts. This is especially true for indexes that rely on custom functions, as any non-deterministic behavior in these functions can exacerbate the issue.

Custom functions in SQLite must be deterministic, meaning they must always return the same output for the same input. If a custom function is not strictly deterministic, it can lead to index corruption over time. For example, if customfn returns different values for the same body and key combination under certain conditions, the index entries will become inconsistent. This inconsistency can manifest as "row missing from index" or "wrong # of entries" errors.

Another potential cause is the use of multiple SQLite instances accessing the same database file concurrently. If the application or another process is using the stock Android SQLite library to access the database while the custom library is also accessing it, this can lead to corruption. SQLite is designed to handle concurrent access, but only if all accesses go through the same SQLite instance. Mixing different instances can result in race conditions and corruption.

Finally, filesystem-level corruption can also manifest as index corruption. While this is less common, it is not unheard of for filesystem issues to affect only specific parts of a database file, such as indexes. This type of corruption is often random and difficult to diagnose, but it can be ruled out by checking the integrity of the underlying storage.

Diagnosing and Repairing Index Corruption with PRAGMA Commands and Reindexing

The first step in diagnosing SQLite index corruption is to run the PRAGMA integrity_check command. This command scans the entire database for inconsistencies and reports any errors it finds. In this case, the command reported "row missing from index" and "wrong # of entries" errors, which are clear indicators of index corruption. The next step is to identify the specific indexes that are affected. This can be done by examining the output of PRAGMA integrity_check and cross-referencing it with the database schema.

Once the corrupted indexes have been identified, the next step is to attempt a repair. The simplest and most effective way to repair a corrupted index is to use the REINDEX command. This command rebuilds the specified index from scratch, eliminating any inconsistencies. In this case, running REINDEX resolved the corruption, as confirmed by a subsequent PRAGMA integrity_check.

However, REINDEX is a temporary fix and does not address the underlying cause of the corruption. To prevent future corruption, it is essential to implement robust error handling and recovery mechanisms. One such mechanism is to enable the WAL mode using PRAGMA journal_mode=WAL. WAL mode provides better concurrency and crash recovery compared to the default rollback journal mode. It also reduces the likelihood of corruption during interrupted write operations.

Another important step is to ensure that all custom functions used in indexes are strictly deterministic. This can be verified by testing the functions under various conditions and ensuring that they always return the same output for the same input. If a function is found to be non-deterministic, it should be modified or replaced with a deterministic alternative.

Finally, it is crucial to avoid accessing the same database file from multiple SQLite instances. If the application must use multiple libraries or processes to access the database, it should ensure that all accesses go through the same SQLite instance. This can be achieved by using a single shared library or by implementing a locking mechanism to prevent concurrent access.

In cases where filesystem-level corruption is suspected, it is advisable to run a filesystem check on the underlying storage. This can be done using tools like fsck on Linux or chkdsk on Windows. If filesystem corruption is detected, the affected files should be repaired or replaced, and the database should be restored from a backup.

By following these steps, it is possible to diagnose and repair SQLite index corruption effectively. Implementing preventive measures such as WAL mode, deterministic custom functions, and single-instance access can significantly reduce the risk of future corruption, ensuring the long-term integrity and reliability of the database.