Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Tracking down the 16-year-old WAL-reset SQLite bug

tailscale.com

Post reply on HN
Print view View original

171–180 of 263 posts

Re: Tracking down the 16-year-old WAL-reset SQLite bug

#171

Post by theMonsterpus »

inigyou wrote: post #161
catapart wrote: post #126

As others have said: great article! I did find myself wanting them to get to the point, but once they started describing the bug and the fix, it was very satisfying. I'm very happy there are companies out there on the frontiers of functionality not only funding fixes and debugging measures, but taking the time to write up the details so we can all benefit. Tailscale just moved up in my priorities list. Was going to h…

I don't think Tailscale can do that though? It can't open a port from the internet to your private network.

It can, but with some specifics about DNS names and supported ports: https://tailscale.com/docs/features/tailscale-funnel

Re: Tracking down the 16-year-old WAL-reset SQLite bug

#172

Post by shye »

inigyou wrote: post #161
catapart wrote: post #126

As others have said: great article! I did find myself wanting them to get to the point, but once they started describing the bug and the fix, it was very satisfying. I'm very happy there are companies out there on the frontiers of functionality not only funding fixes and debugging measures, but taking the time to write up the details so we can all benefit. Tailscale just moved up in my priorities list. Was going to h…

I don't think Tailscale can do that though? It can't open a port from the internet to your private network.

https://tailscale.com/docs/reference/tailscale-cli/funnel

Re: Tracking down the 16-year-old WAL-reset SQLite bug

#173

Post by nujabe »

procflora wrote: post #61

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 i…

Was wondering the same, doesn’t mention if they tried checkpointing less frequently

Re: Tracking down the 16-year-old WAL-reset SQLite bug

#174

Post by zbentley »

pseudohadamard wrote: post #170
calmingsolitude wrote: post #8

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 SQ…

I don't know enough about the scale of Tailscale's operations to comment strongly on this, but if they're fairly significant shouldn't that have read "is exactly how MariaDB is meant to be used" or "exactly how Postgres is meant to be used"? SQLite has a "lite" in the name for a reason, but it's often pushed into places where it's being asked to do things it was never really designed for.

This particular bug doesn’t seem to arise from SQLite’s “lite” nature. It’s a TOCTOU inside the DB when applying WAL segments in a checkpoint, which is a pattern used in extremely similar ways by Postgres and MySQL. They don’t seem to have similar bugs, but I don’t think there’s any reason to believe that this is due to their being client/server rather than coordinated-file databases.

Re: Tracking down the 16-year-old WAL-reset SQLite bug

#176

Post by zbentley »

inigyou wrote: post #129

This reaffirms my belief that SQLite is not well suited for systems with significant concurrency. It replaces fopen, not postgres. Although this corruption is a rare bug and sqlite is usually extremely stable, it's usually not worth it from a performance and features standpoint either. Here they were trying to do a backup by forcing a checkpoint and then copying the file. Systems like postgres let you do online conti…

Eeeh, this particular bug was a race when applying WAL to the data files in a checkpoint. Postgres’s checkpointing is theoretically just as vulnerable to this class of bugs as SQLite, though it doesn’t seem to have equivalent issues today.

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.

Re: Tracking down the 16-year-old WAL-reset SQLite bug

#177

Post by zbentley »

sgarland wrote: post #135
tyho wrote: post #30

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

That is the correct mentality, but sometimes, you’re wrong. I found (not entirely true; someone else [0] found it first, but my report from my company got traction [1] on it) a weird bug in ProxySQL several months ago involving mirroring and fast routing, where if you had a configuration that was illogical from the standpoint of documented behavior, it would duplicate queries, which led to super fun times for writes.…

I mean, ProxySQL and SQLite are at very different tiers of reliability. I encountered multiple unreported data-corrupting (and some resource exhausting/connection mis-pinning) bugs in ProxySQL within a few months of using it for the first time, and I wasn’t using it for anything particularly complex or advanced—just a basic connection pool, no failover or caching/rewriting/replica awareness, but a lot of frontends and QPS.

Re: Tracking down the 16-year-old WAL-reset SQLite bug

#179

Post by zbentley »

danpalmer wrote: post #76

Glad this got found and fixed, but I continue to be astounded at the amount of work people put into making SQLite do things that would be much simpler with other systems.

Maybe, but there’s nothing about this particular bug that’s due to SQLite not being a networked database. Postgres is just as likely to have TOCTOU races in its checkpointer, which works roughly the same way.

Re: Tracking down the 16-year-old WAL-reset SQLite bug

#180

Post by zbentley »

inigyou wrote: post #136
raggi wrote: post #122

Earlier quoted context omitted.

We have very good reasons for our checkpointing model, related to our backup + disaster recover strategy, along with resource cost. It might be worth writing about one day, so I'll not give away all the details, but in very short form, we organize a backup strategy that has minimal pause time, avoids doubling the page cache cost of the database, and enables extremely fast byte-copy restores in disaster recovery.

okay but explain why you are using sqlite and copying the file to S3 instead of using any client/server DB and its online backup feature?

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
Post reply on HN