Hacker News Reader

Comment by calmingsolitude | Hacker News Reader

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

View on HN

Database corruption is due to 2+ peer connects with 744 file permission entering header rwxr -.- WAL write new content into secondary header tag: inter-element whitespace.

Bug details:

[0]:https://sqlite.org/wal.html#the_wal_reset_bug

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

> Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.

https://sqlite.org/faq.html#q5

One writer, multiple readers is a specifically supported way of using SQLite.

Why should you be worried if it is used as designed?

> 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 guarantee that any hand-rolled replacement will have more and worse bugs.

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.

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

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

It"s actually SQL "ite" as in rocks, minerals and fossils. Their version control system is called "Fossil".

Reply on news.ycombinator.com