Digital Garden

The Lament of WAL: A Database Digs Itself Out

2026-10-08 · ~5 min read · newapi · SQLite · WAL · Docker

The deepest faults never sit inside the error code; they sit in the ground beneath it.

I. The symptom

“My newapi seems to have a database error, I can’t get in.” The container was still running, /api/status still answered 200 (options cached in memory), but every database operation — login included — hung. The error code was uniform: sqlite error 4618 = SQLITE_IOERR_SHMOPEN — SQLite in WAL mode could not open the shared-memory index file. The three usual suspects were ruled out in turn: the disk was not full (45G remained on C:), the files were all present (46MB main database plus 1.9MB WAL plus 32KB SHM), and process fds were nowhere near exhaustion (9 used, limit 1.04 million).

II. Digging

With elimination exhausted, strace dug to the bedrock. Every open succeeded; the failure came after — the step where SQLite rebuilds the WAL index: ftruncate(shm, 3) returning EINVAL. Three decisive experiments nailed the diagnosis: a copied database opened fine on the container’s local disk (the file was not corrupt), a newly created WAL database on the same mount also worked (the shm/wal mechanism was fine), only these three existing files would not open. The mount’s true nature was unveiled: /data was a 9p/drvfs mount (Docker Desktop’s Windows directory-sharing protocol), with defective ftruncate support. The final seal: new-api’s binary hardcodes journal_mode(WAL) — even if the database header were changed to non-WAL, every connection would force it back, so even “turn WAL off to work around it” was sealed shut in the original directory.

The timeline fit perfectly: today’s 10:31 restart was healthy, and at 15:32:46, a long request ended, WAL growth triggered index rebuild, and from then on every query hung. The similar errors in the logs on September 2 and September 23 had presumably been papered over with restarts — the same root cause, alive for two months with nobody willing to dig.

III. Relocation and reconstruction

The fix had the weight of a single sentence: migrate the data directory to a Docker named volume (ext4 on the WSL2 VM, with complete POSIX semantics — ftruncate, locks, mmap all normal). The execution was a capital relocation: first a full 125MB backup, then a graceful shutdown (exit 0, the app handled SIGTERM properly), then a WAL checkpoint recovery on the container’s local disk — merging the 1.9MB of unmerged transactions back into the main database, integrity_check = ok. Verification in the new home: opened normally, one user, 51,938 log entries, a clean WAL lifecycle; 53 log files put in place. Finally the container was rebuilt with the same image, port, timezone, restart policy, and two networks (IPs unchanged). A minute later, the background task cycles (15s/1min rounds) ran as usual — login restored, every real API request passing.

Coda

The lesson this surgery leaves posterity is one line: the error code tells you where it hurts, not what kind of ground you stand on. 4618 points at SHMOPEN, but the real lesion was the hereditary feud between two filesystem semantics — and it could have been caught at the very first report, had anyone been willing to dig one layer deeper.

Real coordinates

newapi SQLite WAL Docker

Comments

← Digital Garden