you gotta admire the power of using json/b and simple KV stores.

so many people sleep on that.

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.

This was a great technical writeup and very interesting to read, but it's not clear to me why once the suspected source of the bug was identified, they seemingly didn't build a automated way to trigger the condition? It seems like that could have cut down on the uncertainty of whether the fix worked over a painfully long period of time.

How is "Fix the WAL-reset database corruption bug" downplaying this? https://www.sqlite.org/changes.html

> 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.

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...

huh. ran into almost-certainly this, but blamed it on litestream and rearchitected a bit as a result. will have to see if I can reproduce the issue as we were using with the patched sqlite

I see tailscale also use the pure Go SQLite conversion so I hope this fix will land there soon, it's rapidly become one of my favorite packages for self-contained tools.

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 .

Everybody here knows that SQLite is not production-ready and does not scale.

This is just proof that it can't be used in real-world applications.

SCNR

I wonder if this also affected litestream disproportionately, because litestream also inserts itself into the checkpoint process.

Awesome write up. Finding these bugs in such a well used piece of software is like donating to humanity

> 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 feel like they missed a key takeaway from their own argument here, they should not be running a non-standard configuration :)

Wild to have worked in the industry long enough that 16 years doesn’t feel that long ago.

> 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...

Similar bug to the one that plagued Codex until 3 months ago.

I lost my Android SMS DB way back in the day (20+ years? dang I'm getting old) because the SMS app's fix when opening the DB detected any issue was to delete it and start fresh.

love the simplicity of this article. Reminds me so much of foundational software engineering. i love databases def not cosmosdb

Show us the $$ numbers! Or an estimate at least.

I wish Microsoft wrote an apology like that.

Excellently-written article. Bravo!

Learned something new today, thanks

I wonder what happens when you give Claude the old version and the logs and ask it to find the bug.

"Tailscale Traces Database Corruption to 16y/o ..."

Would have been a superior headline

*sarcasm

> Nobody wanted us to spend six months looking for bugs in SQLite

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.

>"...and then we discovered an unexpected clue.

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...

Of course. On the other hand, is it fair to call it a single point of failure if it being down only affects a shard? Maybe within the shard indeed. Or for a single customer indeed. Not sure if we have terminology to indicate one or the other.

SQLite has an online backup API as well, but it is slower and requires a significant additional page cache cost.

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.

I don’t think that’s an architectural solution to this class of bug. I replied to a similar comment here: https://news.ycombinator.com/item?id=49281533

Same bug, two angles. Nothing really copies 20 pages, the "20" is just a broken counter. SQLite's internal tally of how many WAL pages it already saved to the main db gets corrupted and reads too high. Since it trusts that tally, it assumes those pages are already saved and skips writing them for real, so when the WAL resets they're gone. The "copied more than existed" number is the bug showing in your metrics; "pages never written" is the actual damage.

Oh? I thought we added that option on iOS recently. I'll go check.

Tailscale is Canadian, headquartered in Toronto.

This would be the "proven otherwise by real actions" part. They've been around since 2017 and have had plenty of time to hit various hurdles. Past performance is no guarantee of future performance and it is within the realm of possibility for them to go of change course like Elon did. But who are you? Are your Internet comments some proenoucement of truth that can never ever be taken back? So you said something that, years later, turns out to be wrong. So what?

Nope, it's a generic PBT harness. The human who built it knew about the bug, but the workload was specifically designed not to specifically target this particular bug, and may have found several others as well (we are in the process of reporting these upstream).

Avery is amazing, reading his writings was one of the highlights of my time at Google. There were many talented and articulate people there, the level of discourse used to be amazing.

For sure! I haven't had the opportunity to burn many tokens on this but given how quickly it was able to fuzz it into crashing i'm sure it can manage.

There's also the more fundamental issue that Postgres - unlike something like Yugabyte - does not use a distributed consensus algorithm for writes and can lose committed writes during network partitions.

But of course, neither does Tailscale's weird DIY contraption.

Right, the point is it's a kind of elephant in the room - they even allude to it not being a common use case - but they say nothing about the rationale. That leads me to suspect it was a kind of "seemed like a good idea at the time" situation.

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.

Is this because HS hasn't implemented it yet (which is on the community), or because Tailscale does something to prevent them from working specifically?

That's dynamic, not static; it doesn't help much with a race that's hard to trigger.

That's also possible

Presumably he is referring to rust fanboys' tiresome criticisms of everything C.

> 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.

I was very excited about formal proofs, which are now very cheap to produce, in service of validating AI generated code.

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...

That's already how it works at the data-plane layer. I'm talking about the control-plane layer where tailscale needs to maintain a server that gets its state from somewhere (they need to know where to route your data, what your permissions ACLs are, device names, etc.). If everyone shared the same db cluster accessed over the network there is a real risk of an accident leaking the encryption keys (of the database itself, not your tailnet) to the db they would have to maintain, and leaking semi-sensitive info like these device names and IP addresses. Using sqlite means each tailnet's metadata is isolated to the container running it.

Interesting. I don't think this kind of technique could realistically have caught the bug in advance, though; you're not going to model the entire codebase in TLA+, so the investigation in that post modeled just the parts that turned out in hindsight to be relevant.

> what if this bug is still there but the timing window is now one CPU instruction?

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.

Then it’s a different bug: they fixed a bug with a wider timing window and introduced a new bug with a narrower timing window.

Wow, that’s a great idea! I loved keybase back in the day and really thought it solved a real world problem in a unique and interesting way. Then Zoom bought it (for some reason) and left it to rot.

Or instead of buying Keybase, they could fund https://keyoxide.org/?

Why would enterprises need Keybase's product?

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.

Probably the most refreshing thing I've read in a while. Glad to support them moving forward if this is indeed their modus operandi.

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.

They could have just spent $300,000 in tokens to AI slop rewrite SQLite in Rust.

Oh, thank you! I might do that.

You can't even exhaustively test every 64 bit integer value.

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.

> Not being able to exhaustively test simple problem absolutely means you can't exhaustively test massively more complex problem.

"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

Y? What's wrong with providing username/password authentication

As a user, I really don't care for magic links. The whole, start the log-in process, switch context, wait for email (sometimes up to a minute), click on it, have it open a new tab in a different window than where I started is just a pain. I feel like I spend half my day logging in to services these days.

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.

I detest magic links. I have probably 9-10 accounts with a certain service that uses magic links (I mostly use them to get an API key or download a file every few months and never visit directly otherwise) because 1. the only login method they support is magic link and login with Google/Facebook. There's no password field so my password manager can't fill out my login details for me. 2. I don't have a Facebook account, and I don't keep my Google account signed in. Even if I did, I don't want them using these as my primary email address. (I use a catch-all and don't give out the primary address; I give each service it's own address) 3. A bunch of newsletters and forums (hackaday, etc) mention them by name every few weeks, often in the subject field, making it hard to search for. They don't send me any newsletters themselves, and I don't participate in their forums so there's nothing from them directly. They can't do anything about this, but it makes things harder. 4. If you enter a new email address that they've never seen before, the process exactly the same until after you click the magic link you received. They don't tell you they've never seen it before until you verify the address. 5. As such, if I can't recall the exact address I used to sign in, I automatically create a new account and don't find out until after I click the magic link and I'm asked to enter a new username or use the existing username they've already created. Proceed to settings and create a new API key for a new account instead of just adding one to my existing account.

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.

That strongswan thing is the kind of design HN praises about open protocols by IRC (just the other day...) but in practice is so flexible it can't keep itself upright and it's unusable in practice.

Meanwhile tailscale or wireguard, by being actually opinionated, avoids needing much configuration at both ends.

With a desktop its usually possible from the network setting GUI? Worked like that last time I needed to use a VPN for access to a corporate network.

Those are pretty much all first-party clients though, right? I'm not sure that "some companies don't provide a Linux client for their software" is something I'd characterize as "Linux desktop weirdness". My point here is that Tailscale seems to have actually put time and effort into making a Linux client that's a breeze to use, whereas most of the other VPNs I've used for work have not.