> We funded the open-source SQLite VFS shim that helped isolate the race condition almost immediately, and will help track down similar bugs in the future.
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...
This isn't exactly new; the other model is to just hire them directly. Unfortunately this has not generally led to open source outside of the enterprise world getting similar funding, but it is what it is.
Tailscale is the best. It's infinitely better than Hamachi, ZeroTier, etc. My only gripe is that they have some really weird SSO requirements like GitHub, etc. and then that provider becomes a permanent part of your identity.
I've been a fan of Tailscale since encountering it for the first time at a previous job at a small startup. Someone asked if anyone had a Linux machine when we were all testing out something, and I mentioned I had a personal Linux desktop but wasn't sure how to connect it to the VPN for access, and it turned out that the solution was just literally running two commands in the terminal after installing tailscale from the repos. Compared to my first job where connecting to the VPN from Linux required hours of mucking around with openswan (or was it strongswan? so many swans...) and trial and error with various config files, it was unfathomably straightforward.
Yeah, I have my Tailscale tied to my Apple account, which just feels weird. I can add a Passkey account to my Tailnet and make it manager, etc., so that’s what I’ve done. The owner is my Apple account, but I actually do everything admin-wise with a Passkey account.
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.
In real life, an imaginary "leadership" quickly evaporates upon the first hurdle of any kind until proven otherwise by real actions. If I was in your shoes, I would be very careful with blanket statements like these.
Reading the article, it sounds more like they funded this by buying a SQLite support contract, and the SQLite developers created this in the process of helping them track down the issue.
Very nice article, and I appreciate SQLite's explanation of the bug too. And how extremely cool Tailscale appears to have been about it (paying for the VFS shim, etc.).
I'd have liked to have heard more about the decision to checkpoint so frequently that put them on this path though. Presumably that's to keep the WAL tiny for very fast recovery. Trying to mitigate some of the deleterious effects of inserting a DBMS into your network layer, I suppose? Tricky stuff. Wonder how that compares to typical etcd snapshot frequencies too.
> 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.
That constraint is the part I find most interesting here. The wal-index lives in the -shm file, which SQLite never really uses as a file: clients mmap it and treat it as shared memory, and access to it is coordinated through xShmLock rather than ordinary file locks. The race needs two connections because it needs that shared coordination layer to exist at all.
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.
I admit to curiosity as to whether static analysis could have caught this. E.g., Rust's type system (yeah yeah I know) catches all data races, unless they originate in unsafe code, which this one might or might not have; a hypothetical Rust SQLite would probably need a lot of unsafe (https://github.com/tursodatabase/turso has 556 unsafe blocks in the core), and I don't have a sense of whether the particular part that contained this bug would be included in that.
Such a good write up . Having explored a little bit of sqlite internals for a codecrafters challenge i was mildly happy i could follow along what was happening .
Great read. So glad they took the time to tell this story. (And glad they, as a for profit corporation, took out a support contract with SQLite. I hope they continue to do so even though this problem is resolved.)
Great writeup, and it was great to see them step in an pay the developers of SQLite to help them fix the bug. I get tired of corporations asking open source authors to fix problems that affect the corporation for free. And while I'm sure it was frustrating for folks to have these outages, I find such puzzles pretty fun to get to the bottom of.
Maybe it's just me, but the explanations of the cause don't align.
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 haven't looked into the actual code fix, but given the "reset" name I have to think it has to do with SQLite "thinking" it has copied more pages than it actually did.
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.
This was really, really interesting - what a triumphant adventure.
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.
>In our control plane, we take manual control of the checkpoint process so we can run fast and consistent backups.
> 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
The irony is that the SQLite developers get a support contract iff someone runs off the path in anger and finds an ancient bug. But perhaps that's part of what make it a quality team: devotion thriving without adverse incentives.
Block device upfuckery layers are powerful against databases. Years ago some colleagues wrote one that provides most of the hazards described by "Parity Lost and Parity Regained"[1] to test FoundationDB, which immediately uncovered several flaws in a project that described itself as well-tested. It's easy to do this with all the probing features that Linux (and others) provide today.
Richard Hipp's recent talk at Software Should Work explains that AI agents have been testing SQLite and they've gotten a deluge of new bug reports from the fuzz-like testing they can do. But they do not do this in house; hobbyists and other organizations do this in their own internal agent-driven fuzzing.
While technically true as written, it seems to downplay the significance:
> 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.
Odd not to highlight the sentence where they answer the obvious question "Why Tailscale in particular?":
> 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.
Quick note that data corruption bugs that are impossible to reproduce are not uncommon (perhaps they're the norm). So some amount of head scratching trying to figure out a plausible scenario by which the system could get into the state represented by the smoking remains is often required. Then you attempt to force it into the supposed bad state by modifying code paths accordingly. So the approach used in this case is clever, but it's not particularly unusual in the world of data stores.
> Whenever corruption occurred, we had to stop the control plane process on the shard while we repaired or restored the database. This was painful for tailnets on that shard, because their entire control plane disappeared during that recovery window.
You don’t need the control plane most of the time. I had a zero downtime headscale upgrade because once the nodes negotiate through the control plane they can talk to each other all the time. The data plane is peer to peer.
It’s problematic because you can’t run connections but it doesn’t stop the world.
What are some solutions to avoid database corruption being single points of failure? I can’t think of any off the top of my head. I don’t think people typically consider database corruption to be a kind of failure common enough to design for, unless you have unusual requirements.
The general answer to this is Byzantine consensus, which cryptocurrency blockchains are designed to solve. If your nodes are willing to fail a little more politely (e.g. no lying, immediately crashing, etc) you can use something cheaper like raft/paxos.
But yeah, it's a lot cheaper to build a reliable system than it is to be resilient.
> We funded the open-source SQLite VFS shim that helped isolate the race condition almost immediately, and will help track down similar bugs in the future.
Interesting example of a company funding open source - in this case paying for the development of a new and very specific debugging tool.
Yeah, tailscale seems to have leadership with their head on right, I agree with the way they handle a lot of things.
Yeah, this part also stuck out to me:
> 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...
This isn't exactly new; the other model is to just hire them directly. Unfortunately this has not generally led to open source outside of the enterprise world getting similar funding, but it is what it is.
Probably the most refreshing thing I've read in a while. Glad to support them moving forward if this is indeed their modus operandi.
Tailscale is the best. It's infinitely better than Hamachi, ZeroTier, etc. My only gripe is that they have some really weird SSO requirements like GitHub, etc. and then that provider becomes a permanent part of your identity.
I've been a fan of Tailscale since encountering it for the first time at a previous job at a small startup. Someone asked if anyone had a Linux machine when we were all testing out something, and I mentioned I had a personal Linux desktop but wasn't sure how to connect it to the VPN for access, and it turned out that the solution was just literally running two commands in the terminal after installing tailscale from the repos. Compared to my first job where connecting to the VPN from Linux required hours of mucking around with openswan (or was it strongswan? so many swans...) and trial and error with various config files, it was unfathomably straightforward.
Yeah, I have my Tailscale tied to my Apple account, which just feels weird. I can add a Passkey account to my Tailnet and make it manager, etc., so that’s what I’ve done. The owner is my Apple account, but I actually do everything admin-wise with a Passkey account.
Oh I had no idea they supported Apple. Maybe they didn't back when I signed up? I'm seemingly stuck with GitHub forever now though.
Just FYI - with Tailscale you can switch SSO providers by putting in a support ticket. I did it last year and it was a breeze.
I think their reasoning on not being an identify provider but acting solely downstream is very clever.
It's common for databases.
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.
In real life, an imaginary "leadership" quickly evaporates upon the first hurdle of any kind until proven otherwise by real actions. If I was in your shoes, I would be very careful with blanket statements like these.
Avery is one of the few people I have enough respect for to look up to.
Their CEO is a very nice and personable guy too. Has given me and others advice on random topics of his interest with no nonsense plenty of times.
Reading the article, it sounds more like they funded this by buying a SQLite support contract, and the SQLite developers created this in the process of helping them track down the issue.
That's actually SQLite revenue model.
https://sqlite.org/prosupport.html
Started using them like 2 weeks back, happy to see how they work.
Very nice article, and I appreciate SQLite's explanation of the bug too. And how extremely cool Tailscale appears to have been about it (paying for the VFS shim, etc.).
I'd have liked to have heard more about the decision to checkpoint so frequently that put them on this path though. Presumably that's to keep the WAL tiny for very fast recovery. Trying to mitigate some of the deleterious effects of inserting a DBMS into your network layer, I suppose? Tricky stuff. Wonder how that compares to typical etcd snapshot frequencies too.
Well written post, really enjoyed reading it.
> 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.
[0] https://sqlite.org/wal.html#the_wal_reset_bug
That constraint is the part I find most interesting here. The wal-index lives in the -shm file, which SQLite never really uses as a file: clients mmap it and treat it as shared memory, and access to it is coordinated through xShmLock rather than ordinary file locks. The race needs two connections because it needs that shared coordination layer to exist at all.
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.
See:
> Don't post generated text or AI-edited text. HN is for conversation between humans.
https://news.ycombinator.com/newsguidelines.html
SQLite: 92 million lines of tests
Dijkstra: Tests can only prove the presence of bugs, never their absence!
I admit to curiosity as to whether static analysis could have caught this. E.g., Rust's type system (yeah yeah I know) catches all data races, unless they originate in unsafe code, which this one might or might not have; a hypothetical Rust SQLite would probably need a lot of unsafe (https://github.com/tursodatabase/turso has 556 unsafe blocks in the core), and I don't have a sense of whether the particular part that contained this bug would be included in that.
Everyone knows that tests don't prevent all bugs. But they are very good at preventing known bugs from recurring in the future.
Donald Knuth: Beware of bugs in the above code; I have only proved it correct, not tried it.
It can prove absence of specific bugs though.
Such a good write up . Having explored a little bit of sqlite internals for a codecrafters challenge i was mildly happy i could follow along what was happening .
Great read. So glad they took the time to tell this story. (And glad they, as a for profit corporation, took out a support contract with SQLite. I hope they continue to do so even though this problem is resolved.)
Great writeup, and it was great to see them step in an pay the developers of SQLite to help them fix the bug. I get tired of corporations asking open source authors to fix problems that affect the corporation for free. And while I'm sure it was frustrating for folks to have these outages, I find such puzzles pretty fun to get to the bottom of.
Maybe it's just me, but the explanations of the cause don't align.
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 haven't looked into the actual code fix, but given the "reset" name I have to think it has to do with SQLite "thinking" it has copied more pages than it actually did.
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.
My interpretation is that they haven't been copied because they didn't exist?
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.
Or you could have 10 pages, it actually copies 9, and reports 20 anyways.
This was really, really interesting - what a triumphant adventure.
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.
[3] https://sqlite.org/releaselog/3_52_0.html
>In our control plane, we take manual control of the checkpoint process so we can run fast and consistent backups.
> 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
In other words there exists a concept of HOT and COLD backups for this reason only.
The irony is that the SQLite developers get a support contract iff someone runs off the path in anger and finds an ancient bug. But perhaps that's part of what make it a quality team: devotion thriving without adverse incentives.
Which SQLite driver for Go does Tailscale use?
https://github.com/tailscale/sqlite
What a brutal bug. I'd never entertain a that bug in SQLite could be causing problems in code I wrote.
Tracking down a 16-year-old edge case in database internals is peak engineering perseverance. Incredible deep dive.
Block device upfuckery layers are powerful against databases. Years ago some colleagues wrote one that provides most of the hazards described by "Parity Lost and Parity Regained"[1] to test FoundationDB, which immediately uncovered several flaws in a project that described itself as well-tested. It's easy to do this with all the probing features that Linux (and others) provide today.
1: https://www.usenix.org/legacy/event/fast08/tech/full_papers/...
I'm definitely going to use the word "upfuckery" instead of fault injection the next time I need it.
> Now we’re in summer, we’re confident that we’ve found the bug, that we understand it—and more importantly, that we’ve fixed it.
This is the feeling I chase as a software engineer. It's the greatest motivator.
Learned something new today, thanks
Similar bug to the one that plagued Codex until 3 months ago.
Do you happen to have a link?
you gotta admire the power of using json/b and simple KV stores.
so many people sleep on that.
I imagine the SQLite eschews AI generated code, but using it for testing (vulnerability, performance, etc) would seem like an easy win.
I know their proprietary testing framework is their secret sauce so we may never know...
Richard Hipp's recent talk at Software Should Work explains that AI agents have been testing SQLite and they've gotten a deluge of new bug reports from the fuzz-like testing they can do. But they do not do this in house; hobbyists and other organizations do this in their own internal agent-driven fuzzing.
While technically true as written, it seems to downplay the significance:
> 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.
Odd not to highlight the sentence where they answer the obvious question "Why Tailscale in particular?":
> 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.
Quick note that data corruption bugs that are impossible to reproduce are not uncommon (perhaps they're the norm). So some amount of head scratching trying to figure out a plausible scenario by which the system could get into the state represented by the smoking remains is often required. Then you attempt to force it into the supposed bad state by modifying code paths accordingly. So the approach used in this case is clever, but it's not particularly unusual in the world of data stores.
> Whenever corruption occurred, we had to stop the control plane process on the shard while we repaired or restored the database. This was painful for tailnets on that shard, because their entire control plane disappeared during that recovery window.
Gotta love single points of failure...
You don’t need the control plane most of the time. I had a zero downtime headscale upgrade because once the nodes negotiate through the control plane they can talk to each other all the time. The data plane is peer to peer.
It’s problematic because you can’t run connections but it doesn’t stop the world.
What are some solutions to avoid database corruption being single points of failure? I can’t think of any off the top of my head. I don’t think people typically consider database corruption to be a kind of failure common enough to design for, unless you have unusual requirements.
The general answer to this is Byzantine consensus, which cryptocurrency blockchains are designed to solve. If your nodes are willing to fail a little more politely (e.g. no lying, immediately crashing, etc) you can use something cheaper like raft/paxos.
But yeah, it's a lot cheaper to build a reliable system than it is to be resilient.
The shard was already a way to make it not a single point of failure.