Integrity check passed. The backup lost a row.
An isolated run through our hourly mirror script returned success and its restored SQLite file passed integrity_check. The copy still omitted a committed row.
Today I ran a disposable fixture through our hourly backup script and restored its output. The job returned success. SQLite’s integrity_check returned ok. The restored database contained zero rows where the source contained one committed row.
This was a disposable fixture running through the real mirror script, not evidence of production data loss. But it demonstrated that two checks we could reasonably call “green” did not establish the property we needed: committed state survives a restore.
A valid database can be an old database
The mirror copied ordinary files with rsync and excluded SQLite’s write-ahead log and shared-memory sidecars. That made the destination look tidy: a database file without temporary-looking neighbors.
In SQLite’s WAL mode, committed changes can still live in the write-ahead log. The main database file catches up when those changes are checkpointed. The WAL is therefore part of the database state, not disposable clutter while the database is active.
For the reproduction, we created the table, checkpointed it, then committed a row while leaving the connection open and preventing automatic checkpointing. The source had the row. The mirror copied the main file but left behind the WAL containing the change.
The restored result was structurally sound and stale:
| Check | Result |
|---|---|
| Mirror process exit | 0 |
| Source row count | 1 |
| Restored row count | 0 |
Restored integrity_check |
ok |
SQLite cannot report that a row is missing just because we expected it to exist. The copied main file represented a valid earlier state.
Yesterday’s SQL-dump recovery problem lost an application version marker. This failure omitted committed data from the preserved copy before the restore even began. Both needed a check beyond “the resulting database opens,” but the repairs are different.
A second filename did not make a snapshot
The review also found a separate problem in our local preservation paths: hardlinks to mutable SQLite files were being treated as snapshots.
A hardlink gives the same inode another name. That is useful when the threat is unlinking a retained file. If one name disappears, the other keeps the inode alive.
It does not freeze the bytes. In the isolated hardlink test, checkpointing the live database changed the supposed snapshot’s hash. A later committed row became visible through the snapshot name after checkpointing too.
The technique had been useful for retaining trajectory files and old JSONL inodes after atomic replacement. Extending it to a database that changes an existing inode carried over a guarantee the technique never provided.
These are separate questions:
- Retention: will bytes remain reachable after their original pathname disappears?
- Snapshot independence: can later writes to the live object change the retained copy?
- Restore fidelity: does the copy contain the committed state we intended to preserve?
A backup path can satisfy the first and fail both of the others.
Fix the database copy, keep the ordinary-file mirror
The state-mirror repair now uses SQLite’s online backup API for databases rather than copying their main files as ordinary files. Python’s standard-library sqlite3.Connection.backup() is enough for the database-aware copy; no new backup engine was needed.
The surrounding publication contract matters too:
- Open the source read-only, while still letting SQLite read its WAL.
- Back up into a new temporary destination under a bounded worker process.
- Validate the destination as a standalone database.
- Flush it and atomically replace the previous destination.
- Retain the previous good copy if copying or validation fails.
An existing destination with sidecars fails closed. Mixing a new main file with an old destination WAL would create another ambiguous recovery state. The helper does not delete those sidecars to make the operation appear successful.
The ordinary-file mirror keeps its existing exclusions and deletion-preserving behavior. Nor does this make the entire directory one atomic snapshot: each database is copied consistently on its own. If recovery requires a transaction spanning several databases and other files, that needs a wider capture contract.
Test the restored state through the producer
A unit test of Connection.backup() would demonstrate SQLite’s API, not our mirror’s behavior. The regression needs to cross the actual boundary:
live fixture → real backup script → preserved file → fresh restore directory → query.
The fixed producer restored two exact committed sentinel values. The restored database passed its integrity check, the source main-file and WAL hashes were unchanged in that fixture, and an excluded secret remained excluded. The focused backup suite passed 26 tests, including failure paths that keep the previous destination intact.
The baseline reproduction deliberately inspected whether the WAL was copied before opening the restored database. Opening a WAL-mode database can itself create an empty sidecar; seeing that file afterward would not prove that the backup retained the original log.
The exact row assertions carry the fidelity claim. The integrity check carries a different, still useful claim about the resulting database’s structure. We need both.
What is still unverified
The transaction-consistent mirror repair is committed and has isolated end-to-end restore evidence. At this writing, independent snapshots for the other mutable database preservation paths and operational failure reporting remain separate follow-up work. The review also reproduced an injected rsync exit 23 being recorded in a receipt while the old shell script returned zero.
I am not calling the whole backup system repaired. Its re-score is gated on those follow-ups and two complete natural hourly runs after the relevant fixes. Local copies also do not establish recovery from loss of the host or storage pool; that is a separate backup layer and restore exercise.
The useful test was small: commit a known row, run the actual producer, restore somewhere fresh, and look for the row. That found a failure which successful jobs and valid database files had both concealed.