sqlite-fts5-corruption-rebuild

SQLite FTS5 Index Corruption from Dirty Data

Problem

SQLite returns "database disk image is malformed" (error code 11) when trying to UPDATE or INSERT records, even though PRAGMA integrity_check reports the database as "ok". The error only affects specific records, not all writes.

Context / Trigger Conditions

• Error message: Error: stepping, database disk image is malformed (11)
• PRAGMA integrity_check returns ok (misleading!)
• SELECT queries on the same records work fine
• Only certain records fail to update; other records in the same table update successfully
• The affected records contain text with embedded tabs (\t), newlines (\n), or other control characters in columns that are indexed by FTS5
• A concurrent process (like a Node.js server) may have the database open

Solution

1. Identify FTS5 tables linked to the affected table:

   SELECT name FROM sqlite_master WHERE type='table' AND name LIKE '%fts%';
   

2. Stop any processes holding the database open (the rebuild requires exclusive access):

   kill $(lsof -ti :PORT)  # Kill the server process
   

3. Rebuild the FTS5 index:

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

   Replace table_fts with your actual FTS5 table name (e.g., people_fts).

4. Retry the failed operation - it should now succeed.

5. Restart your application server.

Verification

• The previously failing UPDATE/INSERT statement now succeeds
• Full-text search queries still return correct results
• No more "malformed" errors on subsequent writes

Example

# Error occurs:
sqlite3 data/tracker.db "UPDATE people SET name = 'More' WHERE id = 11;"
# Error: stepping, database disk image is malformed (11)

# But integrity check passes:
sqlite3 data/tracker.db "PRAGMA integrity_check;"
# ok

# And SELECT works fine:
sqlite3 data/tracker.db "SELECT id, name FROM people WHERE id = 11;"
# 11|More Masajes\t\n\t\tEscort 23 años...

# Fix: Stop server, rebuild FTS5 index
kill $(lsof -ti :3001)
sqlite3 data/tracker.db "INSERT INTO people_fts(people_fts) VALUES('rebuild');"

# Now the update works:
sqlite3 data/tracker.db "UPDATE people SET name = 'More' WHERE id = 11;"
# Success!