Resolving Malformed Inverted Index Errors in SQLite FTS4 Tables

---

Understanding the FTS4 Integrity Check Failure After SQLite 3.44 Updates

Issue Overview
The error "malformed inverted index for FTS4 table" indicates structural corruption within the Full-Text Search (FTS4) virtual table’s underlying index data. This problem surfaces during database integrity checks (via PRAGMA integrity_check) after upgrading to SQLite version 3.44 or later. The FTS4 table in question is maintained through triggers that synchronize it with a base table ("wms_order"), using ICU tokenization for Japanese text. The corruption recurs after rebuilding the FTS4 table and allowing transactional activity over a day. Key factors include:

1. SQLite 3.44 Enhancements: Version 3.44 introduced stricter validation of virtual tables during integrity checks. Prior versions did not rigorously inspect FTS indexes, potentially masking pre-existing corruption.
2. Trigger-Based Synchronization: The FTS4 table relies on triggers for INSERT/UPDATE/DELETE operations. Any flaw in trigger logic or race condition during concurrent transactions could introduce inconsistencies.
3. ICU Tokenization Complexity: Tokenizing Japanese text with icu ja_JP involves locale-specific rules for word boundaries and compound terms. Errors during tokenization (e.g., invalid UTF-8 sequences) might corrupt the inverted index.
4. Hardware or Storage Layer Issues: Intermittent disk/memory faults could corrupt database pages during high transaction volumes.

---

Root Causes of FTS4 Inverted Index Corruption

1. Post-3.44 Integrity Check Behavior
SQLite 3.44 expanded PRAGMA integrity_check to validate virtual tables, including FTS4’s shadow tables (e.g., %_content, %_segdir). Prior versions only checked B-tree structures. If the FTS4 index was already corrupt but undetected, the upgrade would expose it.

2. Flawed Trigger Logic
Triggers that manage FTS4 synchronization must atomically mirror base table changes. Common pitfalls include:

• Partial Updates: Triggers that fail to account for all columns or edge cases (e.g., NULL values in ship_address2).
• Concurrency Conflicts: Without proper transaction isolation, simultaneous writes to the base table and FTS4 shadow tables may create race conditions.
• Binary Data in Text Fields: Storing non-UTF8 or binary data in FTS4-indexed columns (e.g., ship_name) disrupts tokenization.

3. ICU Tokenizer Edge Cases
The icu ja_JP tokenizer splits text using Unicode rules. If the input contains invalid UTF-8 (e.g., truncated strings from application bugs), the tokenizer may produce malformed terms, leading to index corruption.

4. Storage Subsystem Failures
Hardware faults (failing disks, faulty RAM) or filesystem errors can corrupt database pages. High-write environments exacerbate this risk, especially if the FTS4 shadow tables are frequently updated.

5. FTS4 Design Limitations
FTS4 is deprecated in favor of FTS5. Known issues include inefficient merge operations during segment deletion and occasional index inconsistencies under heavy load.

---

Diagnosing and Resolving FTS4 Index Corruption

Step 1: Confirm Corruption Scope
Run PRAGMA quick_check; and PRAGMA integrity_check; to isolate the error to the FTS4 table. If the error persists after rebuilding the FTS4 table, focus on triggers or external factors.

Step 2: Audit Trigger Logic
Review triggers for:

• Atomicity: Ensure base table and FTS4 updates occur within the same transaction.
• Column Coverage: Verify that all indexed columns (orderno, ship_name, etc.) are included in trigger logic.
• Error Handling: Triggers should rollback transactions on errors (e.g., constraint violations).

Step 3: Validate Input Data
Check for invalid UTF-8 sequences in FTS4-indexed columns:

SELECT * FROM wms_order 
WHERE ship_name <> CAST(ship_name AS TEXT) 
   OR hex(ship_name) LIKE '%FFFD%'; -- Unicode replacement character

Rewrite application code to sanitize inputs before insertion.

Step 4: Test Hardware and Storage

• Use sqlite3 test.db "PRAGMA integrity_check" on a database copy to rule out runtime corruption.
• Monitor disk health with SMART tools.
• Test memory with memtest86 or equivalent.
• Enable SQLite’s PRAGMA journal_mode=WAL; to reduce write contention.

Step 5: Migrate to FTS5
FTS5 offers improved robustness. Recreate the virtual table:

CREATE VIRTUAL TABLE wms_order_fts USING fts5(
  orderno, ship_name, ..., 
  content='wms_order', 
  tokenize='icu ja_JP'
);

Update triggers to use FTS5 syntax (INSERT INTO ... VALUES(new.rowid, ...)).

Step 6: Enable Debugging Aids

• Set PRAGMA foreign_keys=ON; to catch constraint errors early.
• Use PRAGMA secure_delete=ON; to overwrite deleted data, reducing storage artifacts.
• Log all FTS4 operations via application-side auditing.

Step 7: Leverage SQLite Recovery Tools
If corruption is unrecoverable, use .dump to export schema and data:

sqlite3 corrupted.db ".recover" | sqlite3 clean.db

Recreate the FTS4 table from the exported schema.

Step 8: Monitor for Recurrence
Implement periodic integrity checks and alerting. Use SQLite’s sqlite3_analyzer tool to profile index health.

Final Note: If all else fails, consider external factors like networked filesystems (NFS) with unreliable locking or antivirus software interfering with writes.