Skip to content

state.db recurring B-tree corruption from WAL TRUNCATE checkpoint on large FTS5 databases #45383

Closed

Description

@lukedd666-sudo

Summary

state.db suffers recurring B-tree corruption. We have observed this twice in 24 hours (2026-06-12 and 2026-06-13) on the same database. The root cause is the PRAGMA wal_checkpoint(TRUNCATE) strategy in _try_wal_checkpoint(), combined with unbounded DB growth from FTS5 indexes.

Environment

  • Hermes: current main (d221e36)
  • OS: macOS 15.5 (Apple Silicon)
  • Profiles: 6 gateway profiles (default + 5 named), each with its own state.db
  • Affected DB: default profile only, 255 MB, ~65K pages, 347 sessions, ~25K messages

Observed Corruption

Both incidents produced identical corruption patterns in the same B-trees:

Tree 23 page 63857: btreeInitPage() returns error code 11
Tree 23 page 828 cell 0: invalid page number 218103808
Tree 23 page 21849 cell 0: Rowid 1374389534729 out of order
Tree 20 page 64517-64577: btreeInitPage() returns error code 11
Tree 20 page 63912: 24x "2nd reference to page" errors
  • Tree 20/23 = messages table + FTS5 index B-trees (the largest structures in the DB)
  • sessions table: always clean
  • All other tables (state_meta, schema_version, compression_locks): always clean

Root Cause Analysis

1. Unbounded FTS5 growth

maybe_auto_prune_and_vacuum() had never run on this database (no last_auto_prune in state_meta). With two FTS5 indexes (messages_fts + messages_fts_trigram), every message INSERT triggers multiple FTS index writes. The indexes grew to dominate the page space.

2. Aggressive TRUNCATE checkpoint

_try_wal_checkpoint() runs PRAGMA wal_checkpoint(TRUNCATE) every 50 successful writes (_CHECKPOINT_EVERY_N_WRITES = 50). On a 65K-page database, this means:

  • Copying thousands of WAL frames back into the main DB file
  • Complete WAL truncation (exclusive lock required)
  • High I/O pressure on every 50th write

The code comment explains PASSIVE was removed because "it never truncates the WAL file." But TRUNCATE on a large DB with deep FTS5 B-trees is the corruption trigger.

3. Silent error swallowing

def _try_wal_checkpoint(self) -> None:
    try:
        with self._lock:
            result = self._conn.execute("PRAGMA wal_checkpoint(TRUNCATE)")
    except Exception:
        pass  # Best effort — never fatal.

A partially-failed checkpoint corrupts pages silently. The next quick_check (which runs on a cron) discovers it, but by then the damage is done.

4. Timeline evidence

Time Event
2026-06-12 09:17 First corruption detected (errors.log)
2026-06-12 17:38 Manual repair (.dump/reimport)
2026-06-12 18:58 Repair confirmed, quick_check ok
2026-06-13 03:30 Last quick_check ok
2026-06-13 11:01 Corruption detected again (same B-trees)

No macOS sleep/wake events, no process crashes, no disk errors in system log. The machine was awake and idle between 03:30 and 11:01 with only a few cron/feishu sessions writing.

Suggested Fix

Short term

  1. Log checkpoint failures instead of except Exception: pass. A failed TRUNCATE checkpoint should at minimum emit a WARNING so operators can detect the onset of corruption.

  2. Reduce checkpoint frequency for large databases. Consider making _CHECKPOINT_EVERY_N_WRITES adaptive (e.g., scale with DB page count) or increase the default from 50 to 200+.

  3. Use PASSIVE or FULL instead of TRUNCATE for periodic checkpoints. Reserve TRUNCATE for explicit shutdown (close()). This trades WAL file size for safety.

Medium term

  1. Set PRAGMA wal_autocheckpoint = N explicitly so SQLite's built-in auto-checkpoint keeps the WAL manageable between application-level checkpoints.

  2. Ensure maybe_auto_prune_and_vacuum runs at startup for long-lived gateway processes. On our instance, the auto-prune metadata was never set, meaning the DB never got pruned or vacuumed despite months of operation.

Workaround

We are now running a cron quick_check every few hours and restoring from snapshots when corruption is detected. We also ran a manual vacuum() which optimized the FTS5 indexes and reduced the page count slightly.

Reproduction

This is a slow-onset issue. It manifests on any Hermes instance where:

  • The default gateway processes high message volume
  • FTS5 indexes grow large (tens of thousands of pages)
  • _CHECKPOINT_EVERY_N_WRITES = 50 triggers frequent TRUNCATE checkpoints

The corruption may not reproduce on small databases or low-traffic instances.

Activity

  1. added
    type/bugSomething isn't working
    P2Medium — degraded but workaround exists
    comp/agentCore agent runtime: loop, agent_init, prompt builder, context-compression, responses endpoint
    area/configConfig system, migrations, profiles
    on Jun 13, 2026
  2. liuhao1024 commented on Jun 13, 2026

    @liuhao1024

    This is fixed by #45396 — switches periodic WAL checkpoint from TRUNCATE to PASSIVE mode, which is a read-only no-op that cannot corrupt B-tree pages. Includes regression tests for the checkpoint mode selection.

  3. benegessarit commented on Jun 15, 2026

    @benegessarit

    Confirming this on a large production instance — same signature.

    Environment

    Signature

    • PRAGMA integrity_check reports a malformed inverted index on messages_fts_trigram, while the DB otherwise opens fine and sessions/messages are fully intact — i.e. latent index corruption, not a schema-parse failure. The hermes doctor session check (SELECT COUNT(*) FROM sessions) passes and never flags it; only an explicit integrity check surfaces it. Ours is caught by a weekly backup integrity check, and it has returned after a prior clean — consistent with an ongoing cause rather than a one-off crash.
    • Confirmed our hermes_state.py runs PRAGMA wal_checkpoint(TRUNCATE) every 50 writes, matching the mechanism described here.

    Recovery

    • Rebuilding the FTS layer from the intact messages table (the repair_state_db_schema drop-FTS path / sessions repair) restores a clean integrity check with no data loss.

    Note

  4. bennybuoy commented on Jul 13, 2026

    @bennybuoy

    Additional Linux field report for the same latent FTS corruption signature.

    Environment at detection:

    • Hermes Agent v0.18.2 (2026.7.7.2), pip/uv installation
    • Linux 6.8
    • state.db: 1,731,452,928 bytes, WAL mode, 1 s busy timeout
    • several default-profile processes had the database open (gateway, dashboard and CLI sessions)

    Both checks returned exactly:

    PRAGMA quick_check;
    malformed inverted index for FTS5 table main.messages_fts_trigram
    
    PRAGMA integrity_check;
    malformed inverted index for FTS5 table main.messages_fts_trigram
    

    The canonical sessions and messages tables remained readable. A separate plugin database using WAL passed quick_check, so this is isolated to Hermes state.db's trigram FTS layer.

    The current hermes_state.py contains a least-destructive recovery path that backs up the database and executes FTS5 rebuild against messages_fts and messages_fts_trigram. I have not run that rebuild while multiple writers are active. Detection on v0.18.2 does not establish when the corruption was introduced; the database may predate the PASSIVE-checkpoint fix.

    Two operational gaps remain useful to track here:

    1. normal health checks can miss latent FTS corruption unless they run quick_check/integrity_check or an FTS integrity command;
    2. repair should quiesce writers (or clearly require that) before rebuilding a multi-gigabyte FTS index.
  5. added
    sweeper:risk-session-stateSweeper risk: may lose/corrupt/mis-associate session or context state
    P1High — major feature broken, no workaround
    and removed
    P2Medium — degraded but workaround exists
    on Jul 13, 2026
  6. added a commit that references this issue on Jul 15, 2026
    c2a3b9c
  7. added a commit that references this issue on Jul 16, 2026
    57e3873
  8. added a commit that references this issue on Jul 16, 2026
    5cbb4fe
  9. 17 remaining items

  10. added 5 commits that reference this issue on Sep 3, 2026
    47bd802
    aa19795
    e8ea41b
    55dea83
    591a3db
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    P1High — major feature broken, no workaroundarea/configConfig system, migrations, profilescomp/agentCore agent runtime: loop, agent_init, prompt builder, context-compression, responses endpointsweeper:risk-session-stateSweeper risk: may lose/corrupt/mis-associate session or context statetype/bugSomething isn't working

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions