Stale -tshm after power-off (or mixed sqlite3 access) bricks the whole memory store — recovery recipe + self-heal tool #435

Activity

1. jkemp814 commented on Oct 26, 2026

   

   jkemp814

   on Oct 26, 2026

   More actions

   Independent confirmation of Class A, plus two measured findings that change the picture on Class B and on recovery. Different environment from the report (Linux/Fedora Silverblue, opencode-mem 2.34.0, OpenCode 2.1.4), same signature.

   Class A: two incidents, both detected on the next open

   2026-10-20 22:59   last successful write to user-profiles.db
   2026-10-21 00:00:43  first: I/O error: short read on WAL frame at offset 3588552:
                        expected 4096 bytes, got 0
   2026-10-22 06:06:14  second: ai-sessions.db, metadata.db, skills.db all stamped
                        within the same second
   2026-10-25 23:22      third: same error, user-profiles.db then ai-sessions.db
   

   Findings that may matter for a fix:

   • The main .db files were intact in every incident. PRAGMA integrity_check returned ok on user-profiles.db (72.8 MB, 1 profile row), ai-sessions.db (88 MB), metadata.db, skills.db. This was a 0-byte -wal beside a stale 86,016-byte -tshm, nothing more. Earlier reports of btree/rowid corruption are plausibly a worse instance of the same truncation rather than a separate defect — but in our case "unreadable store, zero data loss" was literally true.
   • Recovery is a sidecar operation, not a data operation. With the writer stopped: delete the 0-byte -wal and the -tshm, reopen. Data all present, no .recover, no per-row salvage. Doing it under a live server also worked twice here, but I would not recommend it — see below.
   • Detection cannot be based on sidecar presence. A 0-byte -wal next to a -tshm is also the normal shape of an idle store; turso recreates both on demand. The only reliable detector is attempting an open and matching the error string/class.
   • What triggers it here: the host kills the plugin's process. OpenCode's service restart sends SIGTERM, polls liveness every 50 ms for 100 attempts (5 s), then SIGKILLs unconditionally. Plugin databases live in that process. I cannot prove either incident was a restart — both were found by the next process, and concurrent short-lived servers opening the same DBs are equally plausible — so treat this as strongly correlated, not witnessed.

   Prevention that measurably works

   PRAGMA wal_checkpoint(TRUNCATE) immediately before database.close(), bounded by a timeout and never throwing, plus closing pooled DBs in parallel so N × bound still fits a shutdown window: #440.

   Measured by stopping the service and inspecting sidecars with no process alive so nothing could write in between:

                           before   while stopped
   metadata.db    wal=     74192 ->  0
   user-prompts.db wal=    45352 ->  0
   user-profiles.db wal=       0 ->  0
   

   So in a 5 s kill window, five databases closed in parallel with a 1.5 s per-DB bound all get checkpointed. That does not defend against a genuine SIGKILL mid-write — only the host giving plugins a real shutdown hook would — but it removes the common-case torn WAL.

   Class B: read-only access is enough to plant the sidecars, but maybe not to poison them

   This is the part I would want the maintainer to see before documenting "don't point sqlite3 at a live store" as the mitigation, because the behaviour is sneakier than that and my measurement contradicts the strong form of the claim.

   Plain sqlite (python stdlib, same SQLite that the CLI uses), on a WAL-mode database:

   after writer commits and closes:      proof.db
   after a READ-ONLY open (mode=ro):     proof.db  proof.db-shm (32768)  proof.db-wal (0)
   after the reader closes:              proof.db  proof.db-shm  proof.db-wal
   

   So a read-only open creates -shm and a 0-byte -wal, and leaves both behind after close. No write, no checkpoint, no lock released incorrectly — the sidecars simply appear. That is the exact sidecar shape Class A produces, created silently by a diagnostic read.

   But: in our case those -shm files sat next to live turso stores (I found ai-sessions.db-shm and metadata.db-shm from my own read-only integrity checks) and turso kept working normally for hours afterwards — memory tools, capture and the web server all fine, no Invalid page type error. One run, so not conclusive, but it suggests:

   • presence of a stray -shm is not by itself the poison, and
   • the likelier Class B trigger is a stock-sqlite checkpoint or write (which rewrites WAL state using -shm coordination) rather than a pure read.

   Worth pinning down in the plugin's own logic before recommending user-facing advice, because "read-only is safe" and "read-only is dangerous" lead to different docs, and any integrity check a user runs is read-only.

   Practical rules I now encode in a local repair script

   1. Never open the live DB with stock sqlite, not even mode=ro. Copy the .db to a temp dir and check the copy.
   2. Never delete a non-empty -wal; a non-empty WAL can hold committed frames. Only the 0-byte-plus-stale--tshm combination is safe to clear.
   3. Verify the main DB on the copy before touching sidecars, and skip any DB that fails — those need salvage, not deletion.
   4. Stop the writer, clear, start; the service already reopens and rebuilds sidecars.
   5. busy from wal_checkpoint is worth surfacing: a partial checkpoint means the truncate was blocked by a reader, which is exactly the state that survives a restart.

   Offer

   If a startup self-heal is wanted (the direction of the original report), I can follow up with a PR that: catches the class-A error from ensureTursoReady(), verifies the main DB on a copy, quarantines the sidecars into a timestamped directory, and retries the open once — behind a config flag, leaving the store untouched if the main file looks wrong. The checkpoint PR above is complementary: prevention on the way out, recovery on the way in. Say the word and I'll open it rather than speculate further here.
2. jkemp814 commented on Oct 26, 2026

   

   jkemp814

   on Oct 26, 2026

   More actions

   Reproduced from scratch, and the result reframes both classes. Short version: Class A's exact sidecar state is reachable without any crash — a stock sqlite write that closes while turso holds the store produces it deterministically — while a stock sqlite read is harmless.

   Reproducing correctly requires the plugin's open flags

   connection-manager.ts opens every database as { experimental: ["encryption", "multiprocess_wal"] } on Unix. My first attempt omitted those flags and stock sqlite could not open the store at all (sqlite3.OperationalError: database is locked, no -tshm created). multiprocess_wal is what creates the -tshm sidecar and what lets a foreign process in. Any repro needs it:

   import { connect } from "@tursodatabase/database";
   const db = await connect(path, { experimental: ["encryption", "multiprocess_wal"] });
   await db.run("create table t(v text)"); await db.run("insert into t(v) values ('turso-1')");
   // now run stock sqlite (python/CLI) against `path` from another process, then:
   await db.all("select v from t"); await db.run("insert into t(v) values ('turso-after')");
   await db.close();

   Results (turso holds the store open throughout)

   intruder	sidecars after intruder finished	turso's next read	rows after turso writes + closes + reopens
   none (control)	-tshm=86016 -wal=20632	turso-1, turso-2	turso-1, turso-2, turso-after ✅
   stock read-only	-shm=32768 -tshm=86016 -wal=20632	turso-1, turso-2	turso-1, turso-2, turso-after ✅
   stock read-write	-tshm=86016 -wal=0 (-shm gone)	turso-1, turso-2 — stale, missed stock-row	turso-1, turso-2, stock-row — turso-after silently lost ❌

   Three things fall out:

   1. Read-only is benign in this configuration. It creates -shm next to -tshm and turso carries on correctly — no divergence, no loss, verified by value-level assertion across close+reopen. The advice "don't point sqlite3 at a live store" overstates the read case; the hazard is writes.
   2. A stock write + clean close reproduces Class A exactly, with no crash anywhere. The store ends at -wal = 0 bytes, -shm deleted, -tshm still 86,016 bytes. That is the byte-for-byte sidecar state the original report attributes to power-off, produced by sqlite3 db.db "insert ..." followed by process exit.
   3. The turso process then loses data silently. It kept reading its pre-existing snapshot (2 rows, missing stock-row) and its next write went into a WAL that got discarded by the close — 4 expected rows, 3 present after reopen. No exception at any point.

   Unified mechanism

   Stock sqlite's last-connection close runs a checkpoint and resets/deletes the WAL, using -shm coordination it owns. Turso keeps its index in -tshm, which still claims the old frames. Same root condition for both reported classes — a -tshm that outlives the WAL it indexes:

   • next turso open reads frames that no longer exist → short read on WAL frame ... expected 4096 bytes, got 0 (Class A);
   • turso maps the index while the WAL was reset underneath it → Invalid page type: 0 (Class B);
   • or, as measured here, it keeps a stale snapshot and discards its own writes (silent data loss).

   So a crash is one way to desynchronise the pair, not the only one, and the "diagnose it with sqlite3" path is only dangerous when someone writes.

   Implications for a fix, cheapest first

   1. Plugin-side guard (no upstream dependency). At open, treat <db>-shm presence as a foreign-writer footprint: verify the main DB on a copy, quarantine -shm/-tshm/-wal into a timestamped dir, reopen once. That converts today's silent loss and hard failures into a recoverable event, and it is the same code path a startup self-heal needs.
   2. Expose a config knob for multiprocess_wal. Turning it off makes the store hostile to intruders (database is locked — loud, recoverable) instead of silently divergent. For a personal memory store, single-process may simply be the right default; concurrency is what the plugin gains from it.
   3. Upstream turso. When mapping -tshm, validate that the WAL it indexes is still the same WAL (inode/size/header change-counter). Reset the index instead of trusting it. That fixes both classes at the source, and #8536 is presumably the place.
   4. Prevention on the way out stays useful and is independent: checkpoint before close (fix(turso): checkpoint the WAL before closing a database #440), which removes the torn-WAL shape on graceful stops.

   Happy to send (1) as a PR behind a flag, and (2) as a config default proposal, if either direction is wanted. The harness is ~60 lines of bun plus two python one-liners — say the word and I'll attach it rather than paste it into the thread.