Tailscale Traces Database Corruption to 16y/o SQLite WAL-Reset Bug

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

▲

binhex an hour ago | parent | next [-]

Yeah, tailscale seems to have leadership with their head on right, I agree with the way they handle a lot of things.

▲

saghm an hour ago | parent | next [-]

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

▲

jjordan 12 minutes ago | parent [-]

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

	▲	LoganDark 6 minutes ago \| parent [-]
		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.

▲

packetlost 34 minutes ago | parent | prev | next [-]

Avery is one of the few people I have enough respect for to look up to.

▲

devmor an hour ago | parent | prev [-]

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.

▲

EastSmith 35 minutes ago | parent | prev [-]

Started using them like 2 weeks back, happy to see how they work.

▲

calmingsolitude an hour ago | parent | prev | next [-]

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

	▲	maitrungduc 22 minutes ago \| parent [-]
		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.

▲

LgWoodenBadger 6 minutes ago | parent | prev | next [-]

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.

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.

▲

bobtheborg an hour ago | parent | prev | next [-]

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

▲

sandeepkd 9 minutes ago | parent | prev | next [-]

>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

	▲	sandeepkd 7 minutes ago \| parent [-]
		In other words there exists a concept of HOT and COLD backups for this reason only.

▲

tyho 4 minutes ago | parent | prev | next [-]

What a brutal bug. I'd never entertain a that bug in SQLite could be causing problems in code I wrote.

▲

myshapeprotocol 6 minutes ago | parent | prev | next [-]

Tracking down a 16-year-old edge case in database internals is peak engineering perseverance. Incredible deep dive.

▲

riknos314 an hour ago | parent | prev | next [-]

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

▲

spockz 3 minutes ago | parent | next [-]

The shard was already a way to make it not a single point of failure.

▲

kccqzy 43 minutes ago | parent | prev [-]

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.

	▲	AlotOfReading 15 minutes ago \| parent [-]
		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.

▲

dolmen an hour ago | parent | prev | next [-]

Which SQLite driver for Go does Tailscale use?

	▲	sethops1 27 minutes ago \| parent [-]
		https://github.com/tailscale/sqlite

▲

declan_roberts 19 minutes ago | parent | prev | next [-]

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

▲

Zenul_Abidin 15 minutes ago | parent | prev | next [-]

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

▲

jeffbee 16 minutes ago | parent | prev | next [-]

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

▲

ec109685 an hour ago | parent | prev | next [-]

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.

	▲	Ariarule 41 minutes ago \| parent [-]
		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.

▲

pstuart 31 minutes ago | parent | prev [-]

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

	▲	d-us-vb 7 minutes ago \| parent [-]
		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.