Tracking down the 16-year-old WAL-reset SQLite bug
tailscale.com
tailscale.com
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...
Don't be the identity provider, have the email host be the identity provider (which it is anyway if you have a forgot password prompt).
Agreed 100% that nobody should still be using passwords in 2026 though.
The only time I like magic links is for services where I am "not really a user". For example, an appointment reminder for my doctor where I need to validate my insurance. Great, send me an email 24-hours before with the reminder and a magic link, as I don't want to think about an account there.
This is an extreme case, yes, but I have similar issues with several services that only support magic links and GitHub login, and it's hard to configure services to use a new catch-all address when they harvest my address from GitHub login and won't let me change it. Magic links are a great tool, but they should _never_ be the only option. They should supplement existing login methods. Password managers exist for a reason. Several services that I have since stopped using for this reason have in the past decided that they should start sending emails to the addresses they harvested from login with other service. Two services have gotten my magic link address stuck in their system because it's not associated with an account (the account has been deleted) and thus I can't manage subscriptions.
A username/password would be perfectly fine for my use-case as an individual user using Tailscale for my homelab and personal devices. But I suspect the majority of real paying customers don't want a separate authentication flow just for Tailscale and would rather use the SSO they already have. I have confidence Tailscale wouldn't half-ass usernames and passwords is they offered it even if it was mostly just for non-paying hobbyist users, but I'm also sort of glad I get to bucket my account security risk with the people paying their bills.
On Windows and Mac, VPN config is usually just installing a client, signing in, and then it all basically works. Sometimes you don't even need the client and can set it up in settings.
Meanwhile tailscale or wireguard, by being actually opinionated, avoids needing much configuration at both ends.
I have now mostly switched to Wireguard for this, which is much more sane [2].
[1] https://github.com/jawj/IKEv2-setup [2] https://github.com/jawj/wireguard-setup
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.
It seems very similar to Tailscale: immense utility from a free-tier product for the general public, which leads to trust and a large enterprise market.
A shame really, because Keybase was kind of unique in the way the facilitated social proof of identity tied to cryptographic keys. A shame though that they never found a really sustainable business model. When they turned to crypto currency towards the end of their independent run I knew it probably wouldn't last.
I just deleted it as I noticed it was responsible of about 30% of active cpu (and battery drain) usage on my mac, while being logged out due to inactivity. Honestly: I never particularly liked it, and I could not feel the concept especially tempting, just went with the team.
For macOS, you need to build the open source tailscale client. Or create file in esoteric file location to opt out using App Store.
For iOS I don’t even think it’s possible to opt out.
Unfortunately, HeadScale is not a drop in replacement for TailScale. Most people don't realize this until they start self hosting.
Due to this, I had to migrate from Tailscale to NetBird, which is completely open source.
https://avilpage.com/2026/06/moving-from-tailscale-to-netbir...
Looks to me like this is the tool in question, added in January: https://github.com/sqlite/sqlite/commits/master/ext/misc/tms...
> 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.
I would not think of SQLite as "lite" anything. It's SQL In The Executable.
It has a better security and data-durability track record than both Postgres and MySQL, and often beats them in the sorts of things applications do with databases:
> it's often pushed into places where it's being asked to do things it was never really designed for
https://sqlite.org/whentouse.html
https://sqlite.org/hirely.html
Seems like it absolutely is "designed" for this use case.
It"s actually SQL "ite" as in rocks, minerals and fossils. Their version control system is called "Fossil".
To be honest, I'm surprised that someone using SQLite would try to access it directly from multiple threads or processes without fear of data racing.
https://sqlite.org/faq.html#q5
One writer, multiple readers is a specifically supported way of using SQLite.
Why should you be worried if it is used as designed?
Well this whole article is about a company discovering a catastrophic corruption bug even though they were using it as designed.
I think the lesson is that if you're ever actually worried about concurrency then just don't use sqlite. We can see here that concurrency is hard and the bugs are old and deep.
Bug details:
https://sqlite.org/testing.html
It's amazing that a bug could exist for 16 years but it is sobering.
Dijkstra: Tests can only prove the presence of bugs, never their absence!
But I had a funny experience recently where an agent implemented an entire feature completely wrong (exactly backwards, actually, in a way that defeated the purpose, introduced security issues etc.).
It happily supplied tests for the new functionality, and all the tests passed.
What I realized was, even formal verification wouldn't have helped here -- it would have just written a mathematical proof that the incorrect functionality was correctly implemented!
So there's a gap here, where first, the human's intention needs to be formally specified (by the human, or at least the human needs to be able and willing to verify it), and then the slopswarm can hack away at it...
This never changed with AI; in fact, I think it made this need more visible than it ever had been before. You can't get away with not being able to describe in detail what you want. As with working with humans, any ambiguities will be interpreted, and not always in the way you hoped.
Yes, that has always been the case with formal proofs. Even a mathematical proof with Lean is useless if the proven formal statement doesn’t match what the mathematician meant. And that’s why humans working with formal languages won’t be going away. Natural language is imprecise by nature, even when we have precise ideas in mind. In that case, it’s often better to specify what we mean in a formal language than in natural language.
I think a model checker or something similar that can exhaustively cover a search space would suffice, though at that point I think the boundary between "test" and "formal verification" becomes somewhat fuzzy.
"Simple"/"complex" doesn't necessarily imply "small"/"large", respectively. Testing whether the Collatz conjecture holds is "simple", but its search space is (potentially) infinitely large. Testing correctness of concurrent data structures is (probably) a more "complex" problem, and yet it's still possible to perform exhaustive tests of their functionality [0].
In the end, the practicality of exhausting the 64-bit integer search space is irrelevant since it says nothing about the size of the write + checkpoint search space. I could just as easily say that it's easy to exhaust the 16-bit integer search space, but that doesn't mean exhaustively testing for this particular bug is easy.
[0]: http://demsky.eecs.uci.edu/publications/c11modelcheck.pdf
This is the gotcha: in stateful environments you'll have state-space explosion so big you'll have to come up with some compromising measure of "equivalent". You're right that that would constitute a proof by exhaustion of correctness. Unfortunately in practice it's entirely impractical.
[0]: demsky.eecs.uci.edu/publications/c11modelcheck.pdf
Not by hand in the olden days but with AI to help with it, why not?
This is importantly different from formal code verification, in which the specification that needs to match the programmers' intentions is small enough to review, while the bulk of the LLM's output is proof tactics and such, which don't need human review because the verifier guarantees* that they're right.
I suppose you could just tell Fable "hey try using TLA+ to look for bugs in this code" and see whether it finds any.
* Uh, usually. https://leodemoura.github.io/blog/2026-8-1-postmortem-for-ke...
What do you know?
The union of a lot of necessary conditions is not a sufficient condition. But it might be good enough for software.
It's an art to come up with a great test suite that covers just enough and minimizes overlap, not only survives but also helps with refactoring.
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 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.
> 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
Backing up sqlite by copying the file (e.g. rsync) while it's open is a surefire way to eventually get corruption caused by a race condition, but it seems like tailscale wasn't doing that. They were probably using the proper sqlite backup API.
But you don't need checkpoints for consistency and I think the backup API will not copy both the old and new versions of pages just because they're in the WAL, in other words I think checkpointing then backing up should give you the same pages as backing up without checkpointing. So the whole thing seems unnecessary.
On second question of why the tailscale developers did it, its possibly for the same reason why they invested this much into debugging this issue. Some one believed the current behavior did not fit into their architecture, they want to be more performant and take control over things. A big part of me considers this is a required exercise to try, grow and learn. The only thing they could have for improvement would be to have these old hands on architect kind of folks on their team who might have hinted/pointed them to the problem a long before. Challenge/chances are that these older folks would have even stopped them from going in this direction in the design phase itself.
1: https://www.usenix.org/legacy/event/fast08/tech/full_papers/...
> Abstract: SQLite is a C-language library that implements a self-contained, in-process relational database engine supporting full-featured SQL, an advanced query planner, and ACID transactions. By many estimates, SQLite is the most widely used software library in the world today.
> Over its 26-year history, SQLite has gained a reputation as software that "just works". This talk goes over the design choices and development practices that have, at least in the opinion of the lead developer, resulted in that reputation.
Tailscale just moved up in my priorities list. Was going to host my next website with hostinger, but now I'm going to at least try to run a personal server with tailscale to make it public. I might not be able to figure it all out, and may end up going with the VPS route, but this gave me some appreciation for the company that makes me willing to try the less familiar method.
> Consortium members have the guaranteed, undivided attention of the SQLite developers for 23 staff-days per year and for as much additional time above and beyond that amount that the core developers have available. There are no arbitrary limits on contact time. The consortium will never be over-subscribed. New SQLite developers will be recruited and trained as necessary to cover the 23 day/year support commitment.
The SQLite home page lists five companies that have paid for consortium access. I can easily imagine that there are more who don't want to pay $150K/year but would pay $1.5k/year, proactively, to get "private, expert email advice from the developers of SQLite" when they need it.
I spent hours empirically trying various scenarios before concluding that no, it was a ProxySQL bug.
SQLite has 59,000% ratio [1]
Yet it didn't help for a bug to left unnoticed for 16 years :( I don't know what we can do for the industry. I doubt one can formally verify a project like SQLite, and keep it maintainable.
Here they were trying to do a backup by forcing a checkpoint and then copying the file. Systems like postgres let you do online continuous backups.
It's so sometimes just performative. I remember when Tailscale had a similar performative approach with 'just use a JSON file on disk'. Then etcd. Then SQLite. Like sure, you can keep picking the absolite mininum technology for your needs and then change it every couple of years... Or you could just immediately go with a solid Postgres (or Postgres-like setup, eg. yugabyte) and save yourself a bunch of faffing about with weird solutions and migrating between them. But I guess that doesn't drive engagement on your blog.
But of course, neither does Tailscale's weird DIY contraption.
Online backups/replicas are nice until you’re charged for network traffic or have to recreate the replica from scratch, at which point the initial-restore-then-hook-up-the-WAL-stream dance is prone to all sorts of racy issues. If you’re lucky enough to have only a single process talking to the DB, SQLite seems like a nice way to sidestep that complexity while keeping a simple backup story.
Heck, this is basically the Redis model: a single process/thread coordinates all access to the data, and occasionally forks off a background job to snapshot the state somewhere. From that perspective, the Tailscale controller binary is a database; SQLite is just the data file format.
If there's a hardware failure, for example a flaky SD card, it's not out of the question. A mobile app with a lot of usage will see it.
(Yes, I know this appears to be a server use case.)
I believe your flaky SD card is category 4, Disk Drive and Flash Memory Failures.
And this statement is wrong in the article: “ Because SQLite is a single-writer database with serialisable transactions, our transaction history was completely linear and deterministic. (This wouldn’t be true in a multi-writer database like Postgres or MySQL.)”
Actually possible in mpedb to replay multi-writer, and actually better than SQLite3. Try to reply now() in a statement, that is not deterministic in SQLite but is in MPEdb.
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.
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.
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.
This is a little like “we shot ourselves in the foot and then performed surgery on our foot, and everything is resolved now.”
Perhaps you or I would make a different decision based on the aims that lead them there. But there isn't enough information to say whether or not their decision was a mistake, even if it has lead to a peculiar bug. It may be perfectly legitimate and we just don't know some of the constraints they had.
They also touch on this when they say that it worked for them for a long time.
This is a pretty classic symptom of a system that was designed a certain way early on and then runs into issues as the system grows.
One can argue that this was due to a bug, but it's a bug that they shouldn't really have had to deal with - a consequence that the design opened them up to.
This is the feeling I chase as a software engineer. It's the greatest motivator.
I know their proprietary testing framework is their secret sauce so we may never know...
This is just proof that it can't be used in real-world applications.
SCNR
I feel like they missed a key takeaway from their own argument here, they should not be running a non-standard configuration :)
Like this is a great practical takeaway, sure, but eh.
The team chose SQLite early on (there are some blog posts about this) and then we vertically scaled against the SQLite architecture. There are subtle ways you come to depend on the proximity/latency when you scale with local storage that mean switching requires a lot of non-obvious work - it’s probably the largest hazard for embracing SQLite in a growing saas - but at the same time you can push the vertical scale pretty far, which has great margins.
Had we scaled a different architecture of database there’s little reason to believe it would have been plain sailing as seems to be implied here.
Gotta love single points of failure...
But yeah, it's a lot cheaper to build a reliable system than it is to be resilient.
It’s problematic because you can’t run connections but it doesn’t stop the world.
Generally, you do complex distributed systems without on-paper single-points-of-failure anywhere when you absolutely have to, because those systems don't have transient failures. That's not mesh networks like Tailscale at all.
As always: https://how.complexsystems.fail/
Would have been a superior headline
*sarcasm
Now my joke works even worse :(
Most companies wouldn't. Instead, they'd fire the weirdo that came up with the idea of using some weird db, and switch to Postgres like God intended.
I'm not saying either is right, this is not a criticism of Tailscale and their approach, paying the Sqlite maintainers to fix a real bug is commendable, but it certainly doesn't inspire confidence in the "Sqlite in production" hype train.
While Sqlite is indeed boring technology for single-user SQL DBs, for traditional CRUD and network services, Postgres seems to be a much better trodden and much safer path.
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). To do this, we built a
transaction logging pipeline.
We streamed every SQL statement that modified the database to a separate log file. Because SQLite is a single-writer database with serializable transactions, our transaction history was completely linear and
deterministic.
(This wouldn’t be true in a multi-writer database like Postgres or MySQL.) Replaying those transactions against the latest known-good backup should restore the database to its most recent state, safely bypassing the corruption.
[...] This pipeline worked, but then it did something even better: it gave us a clue.
[...] To understand what was happening during these faulty checkpoints, the SQLite developers created a new debugging tool for the virtual filesystem layer.
[...] To help diagnose our problem, the SQLite developers created a wrapper around the virtual filesystem that
writes additional tracing information and logs
about changes to the database.
[...] After our next corruption incident, the additional logs from the new tmstmpvfs shim allowed the SQLite developers to find and fix the bug:
a rare data race
in the SQLite source code between a checkpoint and a write transaction."
Great article!
Software Engineering lessons (that repeat in this article!): So called "Heisenbugs" (bugs that make it past developer test harnesses and a company's Quality Assurance (QA) team) that show up post-deployment intermittently and can't be reproduced locally, occur because one or more of the following factors:
1) The lack of Determinism in a software process or processes.
2) The lack of appropriate logging.
3) The lack of the ability to replay a software process, step by exact step, state by exact state, as it has occurred in the field (occurs as an effect of #1 and/or #2).
4) Multi-threaded code; i.e., multiple threads giving rise to race conditions or other very specific intermittent combinatorial/permutational conditions caused by multiple threads and specific sections of code, which due to very large numbers of permutational timing possibilities, were not or could not be exactly tested for in development...
Anyway, great article! A must-read for any Sr. Software Engineer, or any developer that wrestles with hard-to-find-and-fix bugs in the field...
so many people sleep on that.
> 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.
I read that and it feels like some theoretical thing versus causing impactful outages for workloads shaped like tailscale’s.
> It could exist that long because it was rare—so rare, the SQLite developers had to add code to deliberately trigger it in their testing environments.