SQLite WAL-Reset Bug
Many of these outages were caused by a single bug, deep in SQLite. It took months of intense forensics to track it down.
[…]
This lack of reliable trigger conditions meant we couldn’t reproduce the bug synthetically. Instead, we had to rely on deploying passive, forensic telemetry in our live environment to catch the corruption red-handed. Gathering live diagnostics for a database issue is the last thing we wanted to do, but we had no choice.
[…]
As an additional complication, the corruption didn’t occur on a regular schedule. Sometimes incidents would be hours apart, other times weeks. This made it difficult to predict progress or plan further work, because we were never sure when we’d get our next diagnostic dump. We had a six-week period between October and December when there were no corruption incidents, before they returned as an unwelcome Christmas present.
[…]
In two incidents, our transaction logs failed to replay cleanly. Upon closer inspection, we discovered that data written and committed by one transaction was inexplicably invisible to later transactions. A write had vanished into thin air without raising an error.
The bug is likely present in all version of SQLite from 3.7.0 (2010-07-21) through 3.51.2 (2026-01-09). It is fixed in version 3.51.3 (2026-03-13) and later. Backports of the fix are available for some earlier releases: 3.44.6 and 3.50.7.
The bug only affects databases in WAL mode when there are two or more database connections open on the same file, in separate threads or processes, and when those two connections attempt to write or checkpoint at the same instant.
This investigation is a useful reminder: running boring technology in a non-standard way is a risk. The common paths and standard configurations are incredibly well-tested and reliable. Most people use SQLite in a standard configuration and never face this sort of issue. Everything we were doing was a public, documented, supported configuration—but by taking manual control of the checkpointing process and running at our own aggressive pace, we stepped off the well-trodden operational path.
I spent the last two years of my time working on SQLite trying to chase this bug down. It was so rare that I never got an internal report; my assessment of the number of customers affected was “about a county’s worth”, some of whom were generous enough to allow their local Genius Bar to send me backups of the affected databases. I spent so many hours in a hex editor staring at the wreckage.
Top three data corruption bug of my career for sure, and the other two weren’t bugs in SQLite.
A former Tailscale employee got in touch and shared that they were checkpointing these databases every 250ms. Multiplied across a fleet of servers that is a fantastic way to surface a super rare bug—they basically built our test rack as a product, but two orders of magnitude more effective (partially due to scale, but mostly because our rack also panics devices at random in order to test the filesystem, NAND controller, etc. and rebooting takes a while)
[…]
13/10 fantastic bug. proof that 100% test coverage is a good start, and that formal methods are needed for critical applications.
I whipped out my phone, and asked Claude to get to work. I had it get SQL 3.51.2 – still buggy – set up in Antithesis, and then instrument the code with a bunch of Antithesis assertions. You can see the instrumented version here.
Then I asked it to write a simple workload which exercised the WAL insert and checkpoint code. Notably, this is a completely generic workload. It just runs writes and checkpoints concurrently – things you’d expect to actually happen in production, all the time. The assertions are also generic to the bug, they’re all standard assertions you’d add to any database, things like “no lost committed writes” and “database is not corrupt” (called integrity check in sqlite).
On my first run, Antithesis caught the bug in 15 mins. Here’s the report.
Of course, it helps that he knew to look in the checkpoint code, but you could imagine using tools like this to quickly investigate hypotheses when you don’t already know the answer.
