Fix FTS5 corruption detection: use MATCH query instead of rowid LIMIT 1#30514
Open
bgriffin83 wants to merge 1 commit into
Open
Fix FTS5 corruption detection: use MATCH query instead of rowid LIMIT 1#30514bgriffin83 wants to merge 1 commit into
bgriffin83 wants to merge 1 commit into
Conversation
The _ensure_fts_table() verification used SELECT rowid FROM {table} LIMIT 1
which only reads from the FTS5 content table (a regular b-tree), not the
inverted-index b-trees. FTS5 corruption from stale WAL after unclean shutdown
lives in the index b-trees, so the check passed even when the index was trashed.
This caused silent search failures — session_search returned nothing while the
messages table remained intact.
Fix: use WHERE {table} MATCH 'the' LIMIT 1, which forces SQLite to traverse
the FTS5 inverted-index b-tree. A corrupted index throws OperationalError
immediately, triggering the drop-recreate-backfill path.
Also refactored the duplicate FTS5 setup code for messages_fts and
messages_fts_trigram into a shared _ensure_fts_table() helper, and added
a backfill verification step after schema init to catch partial index
rebuilds.
Author
|
My agent became "dumb" wrt memory from yesterday.. turns out some corruption on the sqlite DB was the cause. Not a pro, but hoping this PR will help others |
This was referenced May 28, 2026
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
2 participants
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Summary
Fixes silent FTS5 corruption where
session_searchreturns zero results even though the messages table is intact.Root Cause
The FTS5 corruption detection in
_ensure_fts_table()used:SELECT rowidonly reads from the FTS5 content table (a regular b-tree), not the inverted-index b-trees. FTS5 corruption from stale WAL after an unclean shutdown lives exclusively in the index b-trees. So the check passed every time even when the index was trashed, andsession_searchsilently returned nothing.Fix
Changed the verify query to:
MATCH 'the'forces SQLite to traverse the FTS5 inverted-index b-tree. A corrupted index throwsOperationalErrorimmediately, which triggers the existing drop-recreate-backfill path — auto-healing on next restart.Additional Improvements
messages_ftsandmessages_fts_trigraminto a shared_ensure_fts_table()helpermessagestable, and backfills any missing rows — catches partial index rebuilds that the simple existence check missesTesting
test_hermes_state.py+test_hermes_state_wal_fallback.py)