Interesting example of a company funding open source - in this case paying for the development of a new and very specific debugging tool.
> Because this wouldn’t be a quick or easy fix, we reached out to the SQLite developers for a professional support contract. This was a great decision. It gave us direct access to their deep expertise and experience, and we had many detailed technical conversations about our architecture and our incidents.
They were willing to pay to get help solving the problem, and then pay again to make sure that the problem is easier to avoid in the future! That kind of long-term thinking seems pretty rare nowadays...
> A single Go process exclusively accesses that database, and serves the control plane for those tailnets. This single-writer design is exactly how SQLite is meant to be used.
This line led me to believe that the writer and checkpointing logic lived on the same database connection, so I was curious to find out how the data race occurred. However, the bug details on the SQLite page[0] outline that it can only ever occur if there are multiple connections open, so the writer and the checkpointer must have been on different threads.
It also hints at why it could hide for sixteen years. Almost everything below the pager can be swapped out through the VFS interface, and there are plenty of unusual VFSes exercising those paths. The shared memory methods are the exception. WAL normally requires xShmMap, xShmLock, xShmBarrier and xShmUnmap, and unix and windows are effectively the only two implementations of them that see real traffic.
Everyone else opts out rather than implementing them, because SQLite documents an escape hatch: set locking_mode=exclusive before the first access and the wal-index is kept in heap memory with no shm file at all. That is the road the browser builds take. The WASM build has no shared memory APIs, so WAL on an OPFS database is only possible in exclusive mode, and the docs are blunt that this removes all concurrency in exchange.
So the alternative VFS world contributes close to nothing to the coverage of the exact code path this bug lived in. Everyone who might have been a third implementation stepped around it instead, which leaves finding it to someone on unix doing something unusual with checkpoints.
Dijkstra: Tests can only prove the presence of bugs, never their absence!
A few (very, very, very pedantic) things that stood out:
> We wanted a way to restore service that didn’t involve rolling back to the last known-good backup (which would lose a lot of data) or repairing the known-corrupted database (which was potentially risky).
(Emphasis mine) - it would be "risky", not "potentially risky" - then the "calculated risk period" starts and it's "potentially problematic".
In the SQLite report[0] (11.2) I wish they downplayed this less - a mention of the rarity, then technical details - I'm friendly with a few of the devs/previous-devs, have the utmost respect for their skill and accomplishments (and by extension, faith that the developers I do not personally interact with are also excellent), appreciation and fondness for the huge accomplishment that is SQLite, and on and on... this is world-class work. Maybe section 11.2 wasn't really aimed at me, or I'm too critical. To be fair to all involved, what a minor quibble for such an interesting problem/fix. I hope my comment isn't a fly in the ointment.
Last bugfix point[1] - ugh. What a sinking feeling that must've been to deploy a fix then be flooded with not-green - and a lesson[2] against smuggling other changes in a changeset "just because we're already here"? Happy it turned out non-catastrophic, but did result in a rare (not remembering other instances of top of head) recall[3] from SQLite. That it was throwing errors at the same time SQLite and Tailscale were testing the other WAL-issue bug must've upset some stomachs for a moment.
[0] https://sqlite.org/wal.html#the_wal_reset_bug
[1] https://tailscale.com/blog/sqlite-wal-reset-bug#fixed-with-a...
[2] Nobody conceptually learned anything here - we're all just reminded of what we know: that sometimes "perfect storms" do actually occur.
One clue was that during corruption incidents, our metrics showed that SQLite would report copying more pages from the WAL file than were actually available. If there are 10 pages in the WAL file and 20 pages get copied to the database, something is clearly wrong.
vs
it thinks some of the pages have been copied from the WAL into the main database file, but they haven’t. Those pages never get written to the database file, and that data is permanently lost.
The first says "more were copied than existed" but the second says "fewer were copied than should have been."
Like I said, it's probably just me interpreting something incorrectly.
i.e. The checkpoint starts, and a write hits after the modifications to data structures have been done but before the data has actually been put in the database. The process starts over again, but doesn't undo the changes it made to indexes etc. Hence the db thinks it holds pages that don't exist.
That's my interpretation, anyways.
If you have 10 pages and it tries to copy 20, either those 10 pages wouldn't really be copied, or bogus data would be written.
That's how I read at least. Those things are not mutually exclusive.
> running boring technology in a non-standard way is a risk.
It was a good read and reminder that the industry is loosing experts gradually. I am not a DBA and yet I have heard about this behavior at least couple times in the past as something to avoid. Its just one of those things which didnt get a chance to be documented cause experts avoided it and regulars didn't get into
1: https://www.usenix.org/legacy/event/fast08/tech/full_papers/...
This is the feeling I chase as a software engineer. It's the greatest motivator.
so many people sleep on that.
I know their proprietary testing framework is their secret sauce so we may never know...
> The bug is a data race with tight timing constraints. It is unlikely to occur in common use.
A large customer did experience this corruption, so it's important for people with tailscale's setup update immediately.
> The developers have never been able to reproduce the bug organically and had to add special testing logic to SQLite that deliberately triggers the circumstances of the the bug in order to verify that the issue has been fixed.
> They also explained why we were more likely to hit the bug than other SQLite users: we take manual control of the checkpointing process, and we checkpoint very aggressively. Even a bug triggered by a rare condition was bound to hit us eventually.
Gotta love single points of failure...
It’s problematic because you can’t run connections but it doesn’t stop the world.
But yeah, it's a lot cheaper to build a reliable system than it is to be resilient.
This is Percona's business model. They employ core pg/mysql developers and you can buy a support package from them. Same for enterprisedb. Pretty reasonably priced packages (like maybe $10k-ish/core IIRC) get you 24x7 support. I've only had to escalate issues once but inside 10 hours we got a pg core committer to debug some very strange vacuum behavior.