Fixing “Database Disk Image is Malformed” Error in SQLite FTS5 Migration

Understanding the "Database Disk Image is Malformed" Error After FTS5 Table and Trigger Modifications

The error "database disk image is malformed" in SQLite is a critical issue that indicates the database file has become corrupted or inconsistent. In this specific scenario, the error arises after performing a migration involving the renaming and recreation of a Full-Text Search (FTS5) table and its associated triggers. The migration process involves dropping the old FTS table (mms_fts) and its triggers, then creating a new FTS table (message_fts) and corresponding triggers. While the migration appears to complete successfully, subsequent DELETE operations on the message table trigger the malformed database error.

The root cause of this issue lies in the inconsistency between the FTS5 index and the external content table (message). When the FTS5 table is recreated, its index does not automatically synchronize with the content table. This inconsistency is not immediately apparent, as SELECT statements and even PRAGMA integrity_check may return normal results. However, when triggers attempt to modify the FTS5 index during DELETE operations, the inconsistency is exposed, leading to the malformed database error.

Why FTS5 Index and Content Table Inconsistency Causes Corruption

The FTS5 extension in SQLite is designed to provide full-text search capabilities by maintaining an index of the content stored in a virtual table. When an FTS5 table is configured with an external content table (using the content= option), it relies on the content table to provide the data for indexing. The FTS5 table does not store the actual content but instead maintains an index that references the content table.

In this scenario, the migration process involves dropping the old FTS5 table (mms_fts) and creating a new one (message_fts) with the same structure but pointing to the renamed message table. However, the new FTS5 table is created without automatically rebuilding its index to match the content table. This results in a mismatch between the FTS5 index and the actual data in the message table.

The triggers associated with the FTS5 table (message_ai, message_ad, and message_au) are designed to keep the FTS5 index synchronized with the content table. When a DELETE operation is performed on the message table, the message_ad trigger attempts to update the FTS5 index by marking the deleted row as removed. However, because the FTS5 index is inconsistent with the content table, this operation fails, resulting in the "database disk image is malformed" error.

The inconsistency is not detected by PRAGMA integrity_check because the corruption is specific to the FTS5 virtual table and its relationship with the external content table. This type of corruption is not covered by the standard integrity check, which primarily focuses on the structural integrity of the database file.

Resolving FTS5 Index Inconsistency and Preventing Future Corruption

To resolve the issue, the FTS5 index must be rebuilt to ensure it is consistent with the content table. This can be achieved using the rebuild command, which reconstructs the FTS5 index from the data in the content table. The following steps outline the troubleshooting process and solution:

1. Rebuild the FTS5 Index
   After creating the new FTS5 table (message_fts), execute the following command to rebuild the index:

   INSERT INTO message_fts(message_fts) VALUES('rebuild');
   

   This command forces the FTS5 table to synchronize its index with the content table, ensuring consistency.

2. Verify FTS5 Index Consistency
   To check whether the FTS5 index is consistent with the content table, use the integrity_check command specific to FTS5:

   INSERT INTO message_fts(message_fts, rank) VALUES('integrity-check', 1);
   

   This command verifies that the FTS5 index matches the content table and reports any inconsistencies.

3. Update Migration Code
   Modify the migration code to include the rebuild command after creating the new FTS5 table and triggers:

   DROP TABLE mms_fts;
   DROP TRIGGER IF EXISTS mms_ai;
   DROP TRIGGER IF EXISTS mms_ad;
   DROP TRIGGER IF EXISTS mms_au;
   CREATE VIRTUAL TABLE message_fts USING fts5(body, thread_id UNINDEXED, content=message, content_rowid=_id);
   CREATE TRIGGER message_ai AFTER INSERT ON message BEGIN INSERT INTO message_fts(rowid, body, thread_id) VALUES (new._id, new.body, new.thread_id); END;
   CREATE TRIGGER message_ad AFTER DELETE ON message BEGIN INSERT INTO message_fts(message_fts, rowid, body, thread_id) VALUES ('delete', old._id, old.body, old.thread_id); END;
   CREATE TRIGGER message_au AFTER UPDATE ON message BEGIN INSERT INTO message_fts(message_fts, rowid, body, thread_id) VALUES('delete', old._id, old.body, old.thread_id); INSERT INTO message_fts(rowid, body, thread_id) VALUES (new._id, new.body, new.thread_id); END;
   INSERT INTO message_fts(message_fts) VALUES('rebuild');
   

   This ensures that the FTS5 index is consistent with the content table immediately after the migration.

4. Handle Existing Inconsistencies
   For users who have already completed the migration and are experiencing the malformed database error, the rebuild command can be executed as part of an update or maintenance script:

   INSERT INTO message_fts(message_fts) VALUES('rebuild');
   

   This resolves the inconsistency and prevents further errors during DELETE operations.

5. Prevent Future Issues
   To avoid similar issues in future migrations, ensure that any changes to FTS5 tables or their associated content tables are accompanied by a rebuild of the FTS5 index. Additionally, consider adding a check for FTS5 index consistency as part of the migration process.

By following these steps, the "database disk image is malformed" error can be resolved, and future occurrences can be prevented. The key takeaway is to ensure that the FTS5 index remains consistent with its content table, especially during schema migrations or modifications.