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

191–200 of 263 posts

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

#191

Post by ec109685 »

Ariarule wrote: post #13
ec109685 wrote: post #9

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

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.

Tailscale isn't mentioned in the SQLite changelog.

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

#193

Post by aw1621107 »

ashton314 wrote: post #181
aw1621107 wrote: post #149

Earlier quoted context omitted.

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

> exhaustively cover a search space 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.

I was thinking that CDSChecker [0]-like tools like Rust's Loom [1] might be able to prune the write+checkpoint search space enough to make exhaustive checking feasible, though I'm far from knowledgeable enough about the implementation to say with any certainty how plausible my hunch is.

[0]: demsky.eecs.uci.edu/publications/c11modelcheck.pdf

[1]: https://docs.rs/loom/latest/loom/

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

#194

Post by punnerud »

Checked and this 100% compatible SQLite3 database (also C-API) did not contain the bug: https://github.com/punnerud/mpedb (Disclaimer: My own project)

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.

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

#195

Post by Naru41 »

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…

> The bug only affects databases in WAL mode when there are two or more database connections open on the same file, in separate threads or processes

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.

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

#196

Post by geocar »

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.

> SQLite has a "lite" in the name for a reason

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:

https://sqlite.org/speed.html

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

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

#197

Post by looperhacks »

punnerud wrote: post #194

Checked and this 100% compatible SQLite3 database (also C-API) did not contain the bug: https://github.com/punnerud/mpedb (Disclaimer: My own project) 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.)” Actuall…

At least mention that it's your own project ...

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

#198

Post by punnerud »

looperhacks wrote: post #197
punnerud wrote: post #194

Checked and this 100% compatible SQLite3 database (also C-API) did not contain the bug: https://github.com/punnerud/mpedb (Disclaimer: My own project) 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.)” Actuall…

At least mention that it's your own project ...

Added, thanks
Post reply on HN