dbcheck check a file "disk image is malformed" empty backup deleted rows "database is locked" "the -wal is gone" integrity_check says ok

You deleted the -wal. If a writer was still running, nothing was lost.

The same command, on two databases that look alike, and no error either way. Running rm app.db-wal while the application still had it open lost nothing: the writer kept reading all 500 rows, committed one more, and the database was intact when it closed. Running the identical rm app.db-wal on a stopped copy left 0 of those 500 rows, PRAGMA integrity_check answered ok, and nothing anywhere said a word.

“A -wal file is not a log you can safely tidy up. While a process holds it, deleting it is free — the kernel keeps the unlinked file alive for that process. When nobody holds it, the commits in it are gone, and the .db that remains is not corrupt: it is a smaller, consistent, older database.”

Measured on SQLite 3.45.1 (Python 3.12 sqlite3), Linux ext4, 2026-11-16.

Both cases, measured

Setup: users with 500 rows, WAL mode, wal_autocheckpoint=0. The table was checkpointed into the .db first, so the .db is a complete 2-page database and every one of the 500 rows exists only in the -wal. The snapshot is taken with the writer still open — the state a crash leaves behind, and the only state in which a -wal survives a normal close.

What was doneFiles after itWhat the file then held
nothing — the snapshot as a crash leaves it .db 8,192 B · -wal 24,752 B (6 frames) · -shm 32,768 B 500 rows, integrity_check ok, page_count 6
rm main.db-wal, -shm left in place .db 8,192 B · no -wal · -shm 32,768 B 0 rows, integrity_check ok, page_count 2
rm main.db-wal main.db-shm .db 8,192 B · no -wal · no -shm 0 rows, integrity_check ok, page_count 2
rm main.db-wal while the writer is still running, one more commit, then close .db 8,192 B → 24,576 B on close · no -wal · -shm 32,768 B 501 rows, integrity_check ok, page_count 6

Why the two rms differ

On Linux, removing a file that another process has open only removes the name. The inode and its pages stay alive for that process until the last descriptor closes. SQLite's writer kept appending frames to the unlinked -wal, so the 500 rows were never out of reach; the only visible change was that main.db-wal no longer appeared in ls (a second process could not open it, and a cp of the trio would no longer find it). At close() the writer checkpointed normally and wrote the whole database back into the .db: 8,192 B → 24,576 B, 6 pages, 501 rows, integrity_check ok.

With no process holding the file there is nothing to keep it alive. The 24,752 bytes are freed, and what remains is the 8,192-byte file that existed before the rows were written — which is a perfectly valid database, just an older and emptier one. quick_check agreed with integrity_check. The users table is still there; it has no rows.

The state that kills: consistent, empty, and green

Nothing about the surviving file is damaged, which is why the usual checks are useless here. PRAGMA integrity_check → ok. sqlite_master → the table you expect. SELECT count(*) → 0. Header page_count at offset 28 → 2, and the file is exactly 2 × 4,096 bytes, so even a byte-level size check finds nothing wrong. The freelist is empty, so the rows are not recoverable as free-page residue either — they were never written into this file.

Two bytes still give the history away: the file-format write and read versions at offsets 18 and 19 are 2 and 2, meaning this database was in WAL mode. A WAL-mode database with no -wal beside it, holding 2 pages where its table should need more, is either freshly checkpointed or missing its commits; the page counts decide it. A 32 KB -shm left next to an 8 KB .db with no -wal is the other fingerprint: something copied the sidecar files and dropped the one that carried the data. The -shm itself holds no rows — it is shared memory of the index, and deleting it along with the -wal changes nothing.

How to tell after the fact

import os, struct, sys
p = sys.argv[1]
h = open(p, "rb").read(100)
ps = struct.unpack(">H", h[16:18])[0] or 65536
declared = struct.unpack(">I", h[28:32])[0]
wal = os.path.exists(p + "-wal")
print("%s: wal_mode=%s wal_file=%s pages=%d file_bytes=%d"
      % (p, h[18:20] == b"\x02\x02", wal, declared, os.path.getsize(p)))

If that prints wal_mode=True wal_file=False for a database whose tables you know had rows, stop looking for corruption. Run the file through the checker above as well: it reports WAL_MODE_BUT_NO_WAL and CRITICAL for exactly this state, and MAIN_DB_STALE when the pair is still together and the .db is behind the -wal — the state in the first row of the table, where copying only the .db is the mistake about to happen.

Order of operations

  1. Do not rm a -wal, ever. It is part of the database, not a temporary file, and it is only safe to remove when SQLite itself removes it — at the last connection's clean close.
  2. Moving a live database: VACUUM INTO 'copy.db' or the .backup command produce one consistent file; sqlite3_rsync for a target you can reach. Copying app.db, app.db-wal and app.db-shm together is the fallback, and only while every writer is stopped.
  3. If the -wal is already gone, the rows are not in the .db and no repair tool can invent them: restore from a backup that contains the pair, and expect the file you have to test clean. Check it anyway — knowing it is empty-but-valid rather than corrupt is what tells you a restore is needed instead of a repair.

What was not tested, stated plainly: one Linux host on ext4, SQLite 3.45.1, Python's sqlite3 module. The live-unlink row depends on POSIX semantics — on Windows an open file cannot be deleted, so the rm fails outright and no data is lost. No NFS/SMB share, no O_DIRECT, no FULL-sync power-cut run with real hardware was included, and nothing here is a claim about what a filesystem with broken locking does.

Check one .db file (max 20 MB; add the -wal if you have it)

Read-only byte inspection: page counts, WAL commit, freelist residue. The file is deleted before the answer is written, and nothing about its content is logged.

One-line check — no browser, same endpoint, JSON out

The request body is the file. Answer is a stable dbcheck/1 document, so it can go straight into a script or a monitoring job:

curl -sS --data-binary @backup.db   -H 'Content-Type: application/octet-stream'   https://huxin.online/dbcheck/check

If you still have the -wal, send both parts. Order matters, not the field names: the first file part is the .db, the second is its -wal.

curl -sS -F 'file=@backup.db' -F 'wal=@backup.db-wal'   https://huxin.online/dbcheck/check

Read verdict (ok / warn / critical) and the findings[].code strings; exit_code is 0 only when the verdict is ok. Limits: 20 MB for the .db, 4 MB for the -wal, 10 s of wall clock, 2 audits at a time — the third gets 429 BUSY. The upload is deleted before the answer is written and no byte of it is logged. Capabilities: api/info.