I see tailscale also use the pure Go SQLite conversion so I hope this fix will land there soon, it's rapidly become one of my favorite packages for self-contained tools.
Tracking down the 16-year-old WAL-reset SQLite bug
121–130 of 263 posts
Re: Tracking down the 16-year-old WAL-reset SQLite bug
#122> This investigation is a useful reminder: running boring technology in a non-standard way is a risk. The common paths and standard configurations are incredibly well-tested and reliable. Most people use SQLite in a standard configuration and never face this sort of issue. Everything we were doing was a public, documented, supported configuration—but by taking manual control of the checkpointing process and running a…
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.
Re: Tracking down the 16-year-old WAL-reset SQLite bug
#123Earlier quoted context omitted.
I've been a fan of Tailscale since encountering it for the first time at a previous job at a small startup. Someone asked if anyone had a Linux machine when we were all testing out something, and I mentioned I had a personal Linux desktop but wasn't sure how to connect it to the VPN for access, and it turned out that the solution was just literally running two commands in the terminal after installing tailscale from…
With a desktop its usually possible from the network setting GUI? Worked like that last time I needed to use a VPN for access to a corporate network.
Based on the experience that I (and other coworkers, including on other distros) had, whatever configurations our VPN needed did not seem to work out of the box on network manager.
Re: Tracking down the 16-year-old WAL-reset SQLite bug
#124Earlier quoted context omitted.
I think their reasoning on not being an identify provider but acting solely downstream is very clever.
Y? What's wrong with providing username/password authentication
One maybe non-obvious benefit I see in that from the individual Tailscale user perspective is that only having SSO rather than username/password keep the security of my login in the same bin as the larger customers where Tailscale makes their actual money.
A username/password would be perfectly fine for my use-case as an individual user using Tailscale for my homelab and personal devices. But I suspect the majority of real paying customers don't want a separate authentication flow just for Tailscale and would rather use the SSO they already have. I have confidence Tailscale wouldn't half-ass usernames and passwords is they offered it even if it was mostly just for non-paying hobbyist users, but I'm also sort of glad I get to bucket my account security risk with the people paying their bills.
Re: Tracking down the 16-year-old WAL-reset SQLite bug
#125Earlier quoted context omitted.
I admit to curiosity as to whether static analysis could have caught this. E.g., Rust's type system (yeah yeah I know) catches all data races, unless they originate in unsafe code, which this one might or might not have; a hypothetical Rust SQLite would probably need a lot of unsafe ( https://github.com/tursodatabase/turso has 556 unsafe blocks in the core), and I don't have a sense of whether the particular part tha…
>Rust's type system (yeah yeah I know) What do you know?
Presumably he is referring to rust fanboys' tiresome criticisms of everything C.
Re: Tracking down the 16-year-old WAL-reset SQLite bug
#126As 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 host my next website with hostinger, but now I'm going to at least try to run a personal server with tailscale to make it public. I might not be able to figure it all out, and may end up going with the VPS route, but this gave me some appreciation for the company that makes me willing to try the less familiar method.
Re: Tracking down the 16-year-old WAL-reset SQLite bug
#127Earlier quoted context omitted.
Yeah, tailscale seems to have leadership with their head on right, I agree with the way they handle a lot of things.
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 aga…
They could have just spent $300,000 in tokens to AI slop rewrite SQLite in Rust.
Re: Tracking down the 16-year-old WAL-reset SQLite bug
#128Normal code has 50% to 90% ratio of code coverage by unit-tests. Dynamic-typed languages (Python, Ruby) usually require more, like 100% - 120%.
SQLite has 59,000% ratio [1]
Yet it didn't help for a bug to left unnoticed for 16 years :( I don't know what we can do for the industry. I doubt one can formally verify a project like SQLite, and keep it maintainable.
Re: Tracking down the 16-year-old WAL-reset SQLite bug
#129This 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 continuous backups.
Re: Tracking down the 16-year-old WAL-reset SQLite bug
#130Normal code has 50% to 90% ratio of code coverage by unit-tests. Dynamic-typed languages (Python, Ruby) usually require more, like 100% - 120%. SQLite has 59,000% ratio [1] Yet it didn't help for a bug to left unnoticed for 16 years :( I don't know what we can do for the industry. I doubt one can formally verify a project like SQLite, and keep it maintainable. https://sqlite.org/testing.html
One might be able to formally verify the sqlite pager layer, where this problem occurred. It's just writing pages to disk and reading pages from disk, and providing ACID guarantees.