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

At the extremity of past year, our uptime was pretty shaky. You tin spot this inclination connected our position page, and that instability continued into the caller year. Many of these outages were caused by a azygous bug, heavy successful SQLite. It took months of aggravated forensics to way it down.

Now we’re successful summer, we’re assured that we’ve recovered the bug, that we understand it—and much importantly, that we’ve fixed it.

We cognize our customers expect Tailscale to beryllium a reliable service, and for respective months we didn’t unrecorded up to that promise. That’s disruptive, and we’re sorry. We’re publishing this blog station to explicate what went wrong, really we responded, and really we yet helped to uncover a long-standing bug successful the bosom of the SQLite database.

Tailscale’s database architecture

While our clients interact pinch our control plane arsenic a azygous nationalist endpoint (controlplane.tailscale.com), internally, our power level is divided into a bid of coordination servers (or “shards”). Each tailnet lives connected 1 soul shard astatine a time, but tin migrate seamlessly from 1 to another. These shards are an soul implementation detail: you don’t cognize what shard your tailnet is on, and you ne'er request to.

Each shard has an SQLite database that holds each the accusation astir the tailnets connected that shard. A azygous Go process exclusively accesses that database, and serves the power level for those tailnets. This single-writer creation is precisely really SQLite is meant to beryllium used.

We’ve utilized SQLite arsenic our superior database since 2022, and we chose it because it's well-known, reliable, and wide used. SQLite is “boring technology”—in a bully way. Many companies usage SQLite successful overmuch larger deployments without issue, and we expected the aforesaid stress-free usage.

In our existent backup pipeline, we return a complete snapshot of the database each fewer minutes, past upload the full SQLite record to an S3 bucket. We’d been moving this setup without incident since early 2023.

Fast guardant to August past year, erstwhile a information pipeline that sounds those S3 backups reported an correction successful 1 of our databases. We ran SQLite’s PRAGMA integrity_check bid against the backup, and recovered it was so corrupted. SQLite corruption is possible, but it’s highly different and not thing you should brushwood successful normal operation. We repaired the affected database, and investigated the cause, but to nary avail.

When operating astatine scale, moreover uncommon events tin hap pinch immoderate frequency, truthful we should person been unsurprised erstwhile it happened again—and again, and again, and again. In total, we faced 19 abstracted instances of database corruption complete six months earlier we yet resolved the underlying bug.

When you perceive the building “database corruption”, it’s earthy to interest astir information loss. Because our power level only handles configuration data, these databases incorporate metadata astir your tailnet and devices, but ne'er your backstage encryption keys aliases web traffic. In the earliest incidents, the betterment process meant a fistful of recently added devices aliases configuration changes didn’t persist, and a mini magnitude of metadata had to beryllium re-entered.

Whenever corruption occurred, we had to extremity the power level process connected the shard while we repaired aliases restored the database. This was achy for tailnets connected that shard, because their full power level vanished during that betterment window. In the early incidents, that downtime was complete an hour, but we gradually sped up the betterment process complete consequent incidents.

Each tailnet is simply a mesh network, wherever devices make peer-to-peer WireGuard® connections to each other. When a instrumentality joins the tailnet, it has to get a database of different devices from the power level earlier it tin found caller connections—so if a instrumentality came online during the SQLite downtime, it couldn’t connect. While the database was being repaired, devices already online remained connected to each other, but they couldn’t study astir changes to the network. Those tailnets besides temporarily mislaid entree to the web-based admin console and the Tailscale API.

There’s besides a broader effect connected trust. We station a world incident connected our position page moreover erstwhile only a mini number of tailnets are affected. Many group saw a position page arena for an incident that didn’t impact them. Indeed, the mostly of shards and tailnets were ne'er progressive successful a database corruption incident! Nonetheless, repeated downtime erodes trust, whether aliases not you’re straight affected.

From the very first lawsuit of corruption, we knew this was a superior threat to our reliability, and we threw a batch of engineering clip astatine the problem—but the hole wasn’t easy.

Trying to find the fault

This bug resisted each our first attempts to find it.

We looked astatine caller changes, but location weren’t immoderate that seemed relevant. Nobody had been moving connected our low-level codification that interacts pinch SQLite, because it had each been written years agone and presented nary issues up until that point. We re-reviewed each of that codification pinch a fine-toothed comb to look for antecedently missed bugs, but we didn’t find thing that would origin the corruption we were seeing.

We looked for communal factors betwixt corruption incidents, but we couldn’t find any. It wasn’t tied to a azygous shard, aliases customer, aliases tailnet feature, aliases clip of day, aliases load level. We were astatine a nonaccomplishment for what mightiness beryllium triggering the behaviour.

This deficiency of reliable trigger conditions meant we couldn’t reproduce the bug synthetically. Instead, we had to trust connected deploying passive, forensic telemetry successful our unrecorded situation to drawback the corruption red-handed. Gathering unrecorded diagnostics for a database rumor is the past point we wanted to do, but we had nary choice.

As an further complication, the corruption didn’t hap connected a regular schedule. Sometimes incidents would beryllium hours apart, different times weeks. This made it difficult to foretell advancement aliases scheme further work, because we were ne'er judge erstwhile we’d get our adjacent diagnostic dump. We had a six-week play betwixt October and December erstwhile location were nary corruption incidents, earlier they returned arsenic an unwelcome Christmas present.

Because this wouldn’t beryllium a speedy aliases easy fix, we reached retired to the SQLite developers for a professional support contract. This was a awesome decision. It gave america nonstop entree to their heavy expertise and experience, and we had galore elaborate method conversations astir our architecture and our incidents.

Between Tailscale engineering and the SQLite halfway developers, we mapped retired respective theories for what mightiness beryllium causing the corruption—including broken POSIX locks connected close(), mismanaging representation owned by SQLite, aliases accidentally utilizing SQLite from aggregate threads while disabling thread safety. After each incident, we gathered much data, added much diagnostics, and systematically ruled retired these theories. We were gradually converging connected the existent bug.

The transactions that didn’t bark

While we were investigating the guidelines cause, we still had a unrecorded level to run. We took fierce steps to automate betterment and minimize downtime:

• Configuring our power level shards to hard-stop instantly upon encountering corruption
• Deploying an automated backup show that continuously ran PRAGMA integrity_check complete our backups
• Improving our runbooks and on-call training

These efforts trim our consequence clip to nether an hour—and past we discovered an unexpected clue.

We wanted a measurement to reconstruct work that didn’t impact rolling backmost to the past known-good backup (which would suffer a batch of data) aliases repairing the known-corrupted database (which was perchance risky).

To do this, we built a transaction logging pipeline. We streamed each SQL connection that modified the database to a abstracted log file. Because SQLite is a single-writer database pinch serialisable transactions, our transaction history was wholly linear and deterministic. (This wouldn’t beryllium existent successful a multi-writer database for illustration Postgres aliases MySQL.) Replaying those transactions against the latest known-good backup should reconstruct the database to its astir caller state, safely bypassing the corruption.

This pipeline worked, but past it did thing moreover better: it gave america a clue.

In 2 incidents, our transaction logs grounded to replay cleanly. Upon person inspection, we discovered that information written and committed by 1 transaction was inexplicably invisible to later transactions. A constitute had vanished into bladed aerial without raising an error. That should beryllium impossible!

The penning connected the WAL

As these incidents were ongoing, the SQLite developers had been processing a caller debugging tool. For a while, we’d suspected that the bug was location successful the checkpoint process. They were building a caller instrumentality to springiness amended visibility into what was happening during checkpoints.

To understand what this instrumentality found, we request to concisely explicate really SQLite checkpoints work.

A SQLite database is made of a bid of “pages”, mini blocks of information. When you update the database, immoderate of those pages request to beryllium replaced pinch caller pages pinch the updated information.

For amended capacity and greater concurrency, we tally SQLite pinch Write-Ahead Logging, which intends caller pages aren't written straight to the database file. Instead, they’re written to the "write-ahead log" aliases "WAL file".

New pages can't beryllium written to the WAL record indefinitely; astatine immoderate constituent they person to beryllium copied backmost to the main database file. This process is called “checkpointing”.

In astir deployments, SQLite itself decides erstwhile to do a checkpoint, and the process is invisible to the extremity personification and developer. In our power plane, we return manual power of the checkpoint process truthful we tin tally accelerated and accordant backups. This non-standard attack seemed suspicious arsenic we steadily eliminated imaginable causes.

One hint was that during corruption incidents, our metrics showed that SQLite would study copying much pages from the WAL record than were really available. If location are 10 pages successful the WAL record and 20 pages get copied to the database, thing is intelligibly wrong.

To understand what was happening during these faulty checkpoints, the SQLite developers created a caller debugging instrumentality for the virtual filesystem layer.

SQLite is divided into respective layers. The apical furniture is the parser and codification generator, which converts SQL statements into SQLite’s soul information structures. These information structures get passed to the pager, which splits them into the individual pages to beryllium written to disk. Actually penning them to disk is handled by the OS interface, aliases “virtual filesystem”. Currently SQLite has 2 mainstream virtual filesystem implementations—Unix and Windows.

If you're willing successful a deeper dive connected these internals, I urge this speech by Richard Hipp, the superior writer of SQLite.

This attack allows you to switch different layers pinch different implementations, aliases wrap an existing furniture to get much information. To thief diagnose our problem, the SQLite developers created a wrapper astir the virtual filesystem that writes further tracing accusation and logs astir changes to the database. This wrapper is called the tmstmpvfs shim, and the root codification is disposable successful the SQLite nationalist repository.

We deployed the shim into our unrecorded environment, and waited for the adjacent corruption to occur. Fortunately, we didn't person to hold long.

The WAL-Reset bug

After our adjacent corruption incident, the further logs from the caller tmstmpvfs shim allowed the SQLite developers to find and hole the bug: a uncommon information title successful the SQLite root codification betwixt a checkpoint and a constitute transaction.

In particular, if a constitute occurs astatine a circumstantial clip during a checkpoint, the checkpointing process gets confused—it thinks immoderate of the pages person been copied from the WAL into the main database file, but they haven’t. Those pages ne'er get written to the database file, and that information is permanently lost. The database record becomes corrupt, because different pages which reference those pages—such arsenic an index—are written to the database.

The SQLite developers named this the “WAL-Reset bug”, and they estimate it was coming successful SQLite for astatine slightest 16 years. It could beryllium that agelong because it was rare—so rare, the SQLite developers had to adhd codification to deliberately trigger it successful their testing environments. Their hole adds an further cheque to the checkpointing function which detects erstwhile the WAL has been reset by different thread.

They confirmed that this bug caused each of the baffling behaviour we’d seen. It explained the corruption, the transaction logs that wouldn’t use cleanly, and the inconsistent checkpoint statistics. They besides explained why we were much apt to deed the bug than different SQLite users: we return manual power of the checkpointing process, and we checkpoint very aggressively. Even a bug triggered by a uncommon information was bound to deed america eventually.

This was an breathtaking moment. After months of disorder and uncertainty, we yet had a plausible mentation for why the corruption was occurring, and a hole we could deploy to forestall it.

The SQLite developers released the hole arsenic SQLite 3.52.0, and we prepared to deploy it arsenic soon arsenic it was available.

Fixed, pinch a mendacious alarm

We rolled retired SQLite 3.52.0 carefully—first to a fewer canary shards, then, erstwhile we saw it moving smoothly, we deployed it to the remainder of the power plane.

Our backup show promptly turned red, and reported corruption successful 13 different databases. This was highly alarming, but we followed our betterment procedures to hole each the expected corruption, and everything was happy. It turned retired these databases had not suffered existent corruption, but were taxable to a 2nd problem successful the type of SQLite.

We shared our errors pinch the SQLite developers, which uncovered a bug successful SQLite related to stale look indexes. If you create an scale connected a computed value, and past the computation changes, the scale will incorporate mismatched values, which gets reported arsenic corruption by PRAGMA integrity_check.

In our case, we were storing immoderate high-precision timestamps arsenic text, converting them to a floating-point number successful a VIRTUAL generated column, and the SQLite 3.52.0 merchandise that fixed our information title besides made an optimisation that subtly changed the rounding behaviour for text-to-floating-point conversions. Our canary shards didn’t person immoderate timestamps that triggered the changed rounding behaviour, truthful we missed this successful our phased rollout.

Because this alteration caused mendacious corruption warnings, the SQLite developers withdrew the 3.52.0 merchandise and alternatively published 3.51.3, which only contained a hole for the WAL-Reset bug.

We fixed the rumor connected our broadside by reducing the precision of our timestamps to integer seconds; text-to-integer conversions are unambiguous. Meanwhile, the SQLite developers created an automated, self-healing scale feature successful 3.53.0, which prevents the old look scale problem.

Party time!

With the hole rolled retired to our full power plane, we were fresh to state victory, but we were still cautious. An absence of corruption incidents doesn’t mean things are fixed—we’d already had 1 six-week play of deceptive calm.

We wanted affirmative impervious that this information title was actively occurring successful our accumulation environment. Now that we understood the origin of the bug—a collision betwixt a constitute transaction and a WAL-reset—we patched our SQLite driver to log a warning erstwhile these 2 operations overlap. If the informing fired but the database remained uncorrupted, we’d cognize the hole had saved america from a imaginable corruption incident.

We deployed the warning, and we waited. And we waited. And waited. And waited. As weeks slipped by, we began to wonderment why we didn’t spot it. Was the informing broken? Was our mentation wrong? Was the existent bug still lurking successful the darkness?

Then, 2 months later, the alert we were waiting for yet fired:

This alert proved that the precise conditions for the WAL-Reset bug do hap successful our accumulation environment, which intends it was the apt culprit for our six months of shaky uptime.

Since that weirdly joyous alert fired, we’ve tally for different 4 months without immoderate database incidents, arsenic of this writing. Finally, we could respire a sigh of relief.

Off the well-trodden path

Nobody wanted america to walk six months looking for bugs successful SQLite. This was an immensely frustrating acquisition for some our customers and staff, and we’re each gladsome to put this instability down us.

This investigation is simply a useful reminder: running boring exertion successful a non-standard measurement is simply a risk. The communal paths and modular configurations are incredibly well-tested and reliable. Most group usage SQLite successful a modular configuration and ne'er look this benignant of issue. Everything we were doing was a public, documented, supported configuration—but by taking manual power of the checkpointing process and moving astatine our ain fierce pace, we stepped disconnected the well-trodden operational path.

Resolving these incidents was a massive, cross-functional effort involving dozens of people—including Tailscale's engineering and support teams, and the halfway maintainers of SQLite. It is to each of their in installments that the effect of these incidents was not overmuch worse.

We cognize that repeated downtime erodes trust, nary matter really galore group are affected, and we’re grateful to our customers for their patience and support while we chased this down.

Frustrating arsenic this play was, we’re near successful a stronger position than we were before. The long-standing bug successful SQLite has been patched, and we fixed dozens of different incidental issues that we spotted while looking for it. We funded the open-source SQLite VFS shim that helped isolate the title information almost immediately, and will thief way down akin bugs successful the future. Finally, we’ve refined our database backup and betterment processes, and live-tested them complete a twelve times.

Hopefully location won’t beryllium different database incident for illustration this—but if location is, we’ll beryllium ready.