I found a bug in SQLite

https://www.philipotoole.com/how-i-found-a-bug-in-sqlite/

by otoolep • 4 years ago

584 157 4 years ago

habibur• 4 years ago

Even though SQLite bugs are rare, those can be found occasionally.

I remember a bug finder took the sqlite documentation off their website. Collected all their keywords, made up millions of jumbled up queries of random combination between keywords and then ran those overnight to find 10 bugs where the engine crashed. And yes those were also reported and fixed quickly.

markerz2• 4 years ago

Sounds a lot like fuzzing, which is a really good way to find really esoteric bugs against a spec! I did something like this in college when I was implementing a compiler and it caught a lot of really weird bugs in a lot of peoples codes. The beautiful part of some fuzzers though is they can automatically simplify their complicated test cases to something you can actually look at and reason about.

carlmr• 4 years ago

Sounds a lot like fuzzing, which is a really good way to find really esoteric bugs against a spec!

QuickCheck style testing is maybe also worth a mention here. Instead of using any possible inputs, like in fuzzing, you restrict yourself to legal inputs, like the keywords here, to get maybe less random crashes, but more likely to find useful corner cases because of the restriction on the search space.

return_to_monke• 4 years ago

so basically, fuzzing.

https://en.m.wikipedia.org/wiki/Fuzzing

rsaxvc• 4 years ago

Sounds more like smart Monkey testing, much older, less feedback.

https://en.m.wikipedia.org/wiki/Monkey_testing

Edit: looks like some consider these the same nowadays.

YetAnotherNick2• 4 years ago

SQLite is continuosly fuzz tested by at least 2-3 professional teams[0], I am really amazed you could find 10 bugs in one day. Would love to read your write up in this!

[0] https://www.sqlite.org/testing.html

techn00• 4 years ago

It's impressive to find a bug in something like sqlite, which is famous for its larger than the codebase test suite

capableweb• 4 years ago

larger than the codebase test suite

This tend to be true for most serious projects, that the amount of test code is greater than that of the code that is being exercised.

I think what they are famous for is the quality of the testing suite, rather than the amount.

markerz2• 4 years ago

I think a good way of looking at the quality of their tests is how they categorize their tests, which focuses on the different types of problems they want to solve: power loss, concurrency, API issues, logic, etc. https://www.sqlite.org/testing.html

loeg2• 4 years ago

This is a definition of “serious projects” that excludes almost all serious projects.

valray• 4 years ago

This tend to be true for most serious projects, that the amount of test code is greater than that of the code that is being exercised.

Reading this comment, I was thinking "Oh that must mean the test code is 2x or maybe even 3x the amount of source code"

Going to the SQLite web site, I was surprised to find that the test code is 600x larger than the source code. Impressive.

Is this 600:1 ratio typical for other projects? The ones that I have seen are more like 1x or 2x, but I have not worked with many open source systems.

civopsec• 4 years ago

Test to regular code ratio shouldn’t be impressive by itself anyway, considering how repetitive, duplicated, and setup-heavy test code can be.

zoomablemind3• 4 years ago

... larger than the codebase test suite

I wonder if they directly test the concurrent use?

It appears that the fix [1] of the OP bug did not lead to any addition/changes in resp. tests.

[1] https://www.sqlite.org/src/info/15f0be8a640e7bfa

P.S. looks like Fossil still has issues with content scrolling and wrapping to screen size (mobile).

otoolep6• 4 years ago

Actually, they added a unit test in a follow-on change:

https://www.sqlite.org/src/info/dc7dd2d3e50e7cc4

password43212• 4 years ago

Long long ago I lost my SMS database on my first Android phone to corruption.

The worst part was if the app encountered an error opening the database, it just deleted it and started over -- no chance of repair to rescue any of the data. I don't think this is done this way anymore.

After that I have installed SMS Backup+ first thing on every new phone.

password43212• 4 years ago

https://stackoverflow.com/questions/7764943/what-can-be-done...

mort96• 4 years ago

I'd be interested to read some discussion on why this wasn't caught earlier. When you have some thing with supports read + write and supports access from multiple threads/processes, I'd expect "read as fast as possible from one thread, write as fast as possible from another" to be one of the most obvious tests to write.

loeg2• 4 years ago

Seems like it was particularly the lock upgrade path in the memdb vfs — so a little weird case that must have not been covered adequately.

https://www.sqlite.org/src/info/15f0be8a640e7bfa

morelisp6• 4 years ago

I vaguely recall some variant of MC/DC coverage that also treats each bit in any bit flag checks as independent boolean conditions. It seems like there could be a similar variant that requires checking each value of such a "leveled" enum independently; if your enum says you can take on values 0-5 and the logic checks <= 2 and <= 5, nonetheless there should be a test case for 0, 1, 3, and 4.

YetAnotherNick2• 4 years ago

Also this is not even a silent bug, so I would have expected that this would have been caught by someone in production.

xk3• 4 years ago

I've experienced this a few times before but I just ignored it because I never had access issues after waiting for the other process to finish writing. I wonder what the patch will really do, is it just a better error message or will SQLITE show only committed data?

jeroen79• 4 years ago

still just submit a bugreport instead of making a fuss of it.

0cf8612b2e1e• 4 years ago

People are allowed to be proud of their accomplishments. SQLite is extremely high quality software. Identifying a mistake is practically equivalent to receiving a Knuth check.

Maybe this post will inspire others on how to locate other bugs, improving the world for the rest of us.

wistlo• 4 years ago

I found a bug in mySQL and after the mySQL team fixed it, I included a summary of it in my annual employee review. Bug fixing widely-used software is not part of my role, but the company recognized it as an accomplishment.

sigjuice• 4 years ago

This is all rather tasteful. Making a fuss would be registering a domain name and making a whole freaking website just for the bug.

bawolff• 4 years ago

It is impressive for very popular software to find bugs.

But even if it wasnt, its still a blog post. The entire point is to talk about what you have been doing. Personally, My blog is super inane.

jacquesm• 4 years ago

No such thing as software without bugs, but given the incredibly widespread use that SQLite sees the quality as evidenced by the fact that finding a bug is news by itself is extremely high. Something to strive for.

The_Colonel5• 4 years ago

It's news only because the author wrote a blogpost about it.

There are bugs fixed in every SQLite release.

MrWiffles• 4 years ago

Props to the author, this was well written. Clear and concise, it was easy to follow. Not like my ratings and ravings! ;-)

otoolep6• 4 years ago

Glad you enjoyed it -- thanks.

orf2• 4 years ago

Lots of people saying that SQLite is super high quality and finding a bug is so rare. It’s not. I found one on a simple query utilising WHERE EXISTS [1]. Reporting it to a weird forum was also a horrible experience.

It’s high quality software, don’t get me wrong, but the infamous 100% test coverage doesn’t make it somehow immune to issues, or imply that the issues you do find are of a certain level of complexity. Nothing is back and white like that.

1. https://sqlite.org/forum/forumpost/452888d3b1?t=c&unf

qbasic_forever• 4 years ago

What exactly was a 'horrible experience' with the report you filed? Richard himself promptly replied in less than 24 hours that the issue was resolved and previously seen by Firefox too. I am at a genuine loss, what was horrible here??

orf2• 4 years ago

It was a while ago, but if I recall I ended up having to sign up several times, the post failed to be created for some reason and something else. There was a captcha involved I think.

I don’t remember the specifics, but I do remember coming away from it with a feeling of “wow, that was an atrocious experience. I wonder what the drop off rate is”

bobleeswagger• 4 years ago

Lots of people saying that SQLite is super high quality

I think the point most of those folks are making, is that SQLite is good enough where most developers think "Psh, I will use [HEAVIER DB SYSTEM THAT SLOWS OVERALL DEVELOPMENT TIME]" even if it is a better long term solution.

It's about bikeshedding, SQLite really is good enough for most projects and its a shame it still has such negative connotations.

maxbond4• 4 years ago

For what it's worth I think it's largely overcome the reputation of being a "toy" database.

eduction3• 4 years ago

“Heavier” and “lightweight” are such abstract terms in software.

If “heavier” just means more LoC — sure, there’s more complexity in more LoC but also more problems solved. There’s a reason people tend to use the latest Linux/macos/Windows as opposed to the very lightweight Apple II OS from 1978.

Defaulting to, say, Postgres doesn’t seem so bad to me. It solves more problems than SQLite and “lightweight” is not really a concrete benefit for SQLite. It’s at least one level removed from speaking to a real problem.

tiffanyh• 4 years ago

Reporting it to a weird forum was also a horrible experience.

What was so “horrible”?

After you posted the bug, the second comment (and only 6-hours later) had a new release and fix.

robertlagrant3• 4 years ago

Evidence of a wonderfully comfortable life. We should all hope to have horrible experiences that are that inconsequential!

setr• 4 years ago

He’s talking about the forum software, not the forum community.

eduction3• 4 years ago

It’s high quality software that is being pushed well past its intended use case. Maybe it will work out fine but rqlite is taking something designed as an on disk file format for one program and trying to use it as a network distributed concurrent database system. It would be surprising if they didn’t expose bugs in SQLite.

There are database systems that have been around for many years built from the ground up for this use case.

otoolep6• 4 years ago

rqlite[1] author here. To be clear rqlite is using SQLite in a completely conventional manner. Nothing about the distributed nature of rqlite impacts on SQLite, since each rqlite node runs its own complete copy of SQLite.

This bug can affect anybody using an in-memory version of a SQLite database. That was the point of writing the C unit test.

[1] https://github.com/rqlite/rqlite

infamia• 4 years ago

It’s high quality software that is being pushed well past its intended use case. Maybe it will work out fine but rqlite is taking something designed as an on disk file format for one program and trying to use it as a network distributed concurrent database system. It would be surprising if they didn’t expose bugs in SQLite.

Expensify is pushing millions of queries/sec by layering Bedrockdb over top of SQLite. You can go a long way and do amazing, unexpected things with a very solid foundation.

https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...

LVB• 4 years ago

but the infamous 100% test coverage doesn’t make it somehow immune to issues

Infamous in what way? While I totally get that 100% coverage may be impractical for many projects, I’m also not seeing how less coverage would have improved things. And I highly doubt the SQLite team ever claimed they were immune to bugs!

lmm• 4 years ago

While I totally get that 100% coverage may be impractical for many projects, I’m also not seeing how less coverage would have improved things.

The argument is generally that language-level correctness would achieve more than emphasising test coverage so heavily.

playingalong• 4 years ago

Not sure of SQLite's interpretation, but in general 100% test coverage "only" means all lines are executed in some piece of test. It doesn't necessarily indicate correctness of each line being checked.

Not to mention one can have multiple logic branches in a line. Or bugs relevant to only some subset of inputs (e.g. works fine for positive numbers but fails for negative is a classic example)

strictfp• 4 years ago

Yup. If you consider that a simple int can have billions of states, and that you can have 100% test coverage while testing just one of those, 100% really doesn't mean much. And with several variables, the total state space quickly becomes almost infinite.

CodesInChaos• 4 years ago

SQLite uses a variant of branch coverage. So "multiple logic branches in a line" isn't a problem. The rest of your comment still applies though.

https://www.sqlite.org/testing.html#test_coverage

pigbearpig• 4 years ago

The first couple of responses seemed a bit dismissive and impolite to me. Quickly remedied by a courteous and professional response by Richard Hipp.

I'm talking about things like, "You should not expect this to get a lot of attention on a Sunday. That's a slow day here.". I didn't see anything in the initial post that implied OP was expecting an immediate answer. And then the snarky, "This missing return makes me think you dislike or ignore warnings, which makes me want to eagle-eye your code more closely.".

I don't know, maybe I'm reading more into that than I should.

einpoklum• 4 years ago

I found a bug in SQLite

Well, good thing it wasn't a bug in the C compiler you were building sqlite with... even those can come up occasionally.

de6u99er• 4 years ago

Did you have to get baptized first to be allowed reporting a bug?

kanonieer• 4 years ago

Obviously. This is a consecrated codebase[1].

[1] https://www.sqlite.org/codeofethics.html

lifeisstillgood• 4 years ago

I understand the comment about "intellectual honesty" about those pesky transient bugs.

It's really hard to know where a hard to reproduce bug is on the cost benefit spectrum - and that is the crux - not knowing enough about the bug to determine it's negative weight means you are essentially guessing both sides of the equation.

It's probably not the best idea, it waiting till users find it at east gives a good idea of the prior

quickthrower22• 4 years ago

Yeah the old "boss, I have spent 2 days investigating the bug, and I think it is best overall if we don't fix it. maybe show a better message".

ChuckMcM• 4 years ago

The lesson here is never ignore your own "Huh, that's weird." experience when using software. :-)

jicea• 4 years ago

A French proverb is « Il n’y a pas de fumée sans feu », means there is no smoke without fire. I use it a lot in my day work when I try not to watch a suspicious behavior…

bewaretheirs• 4 years ago

The usual form of that proverb in English is "When there's smoke, there's fire".

protomolecule• 4 years ago

Exactly the same in Russian: "Нет дыма без огня".

nszceta• 4 years ago

Smoke is absolutely possible without a fire. Have you ever seared any meat on an electric stovetop? Overheated an empty frying pan?

nonrandomstring• 4 years ago

Tis good advice, but in most cases I'd question my own code first and then be confused by general complexity - is it the threading library, or the API, or the database? Rarely do we get a chance to pare back to an isolated behaviour. Also there's a voice that says "surely someone much smarter than me already knows about this".

quickthrower22• 4 years ago

Hire me a full time bug reporting assistant then please :-)

otoolep6• 4 years ago

https://quoteinvestigator.com/2015/03/02/eureka-funny/

eigenvalue• 4 years ago

Yes, I refer to this as cognitive dissonance. You know that something isn't right, and you can't readily think of an innocent explanation that really holds up, but it's possible to ignore in the short term because you can work around it or it's not a complete show stopper. Those things almost always seem to eventually come back to bite you!

DenisM• 4 years ago

Those things almost always seem to eventually come back to bite you!

Or maybe you only remember the ones that bit you, and forget the ones that didn’t.

lost_tourist• 4 years ago

I usually only ignore things when I'm just too busy to care, unless it stops me from doing what I'm currently doing.

yellow_lead• 4 years ago

I think you have to ignore some of these, especially at a large company, where there may be bugs everywhere. Otherwise you will never get your features done and just fix bugs the whole time.

nigamanth• 4 years ago

The code for databases such as MySQL and SQLite is so huge that there are probably 100s of bugs, some get fixed and some don't.

thaumasiotes• 4 years ago

Eh, depends.

I found a bug once in Clojure's implementation of format. It was perfectly reproducible, Clojure's behavior was out of spec, and it didn't match SBCL's output from an identical call to format.

I wanted to report it, but I had to give up on that; the process was too opaque.

fastaguy88• 4 years ago

I have seen similar error messages of the "database is corrupted ..." type with MariaDB when I simply typed in my SQL incorrectly.

nodesocket• 4 years ago

My first time coming across rqlite. Looks awesome. I just finished writing a poor man’s centralized store of SQLite writing the DB file to Minio (s3 compliant) storage but of course has race conditions all over the place. Gonna take a look at replacing with rqlite.

adius• 4 years ago

Just a few days ago I found a serious security issue in SQLite: https://sqlite.org/forum/forumpost/07beac8056151b2f

It was also promptly fixed, but it makes me feel like the millions of tests sound better than they are in reality …

cube00• 4 years ago

At least the tests help avoid regressions, you can bet these bugs won't come back again.

TAForObvReasons• 4 years ago

There was an interesting comment a few months ago [1]

Not mentioned is that the full test sqlite test suite is proprietary and you need a super expensive sqlite foundation membership to get access to it.

According to Dr Hipp [2], no one bought the test suite. So there are definitely deficiencies in the test suite which may have been better addressed if the full test suite was open.

[1] https://news.ycombinator.com/item?id=33346661

[2] https://corecursive.com/066-sqlite-with-richard-hipp/#billio...

notRobot• 4 years ago

From the second link:

We still maintain the first one, the TCL tests. They’re still maintained. They’re still out there in the public. They’re part of the source tree. Anybody can download the source code and run my test and run all those. They don’t provide 100% test coverage but they do test all the features very thoroughly. The 100% MCD tests, that’s called TH3. That’s proprietary. I had the idea that we would sell those tests to avionics manufacturers and make money that way. We’ve sold exactly zero copies of that so that didn’t really work out. It did work out really well for us in that it keeps our product really solid and it enables us to turn around new features and new bug fixes very fast.

cryptonector• 4 years ago

But the testsuite being proprietary is part of their business model.

komali23• 4 years ago

I'm watching engineers re-implement error handling code I wrote line by line over the weeks after they "optimized" my error handling into a oneliner. And as the old bugs crop up when API error responses are weirdly formed, they find themselves adding the edge case code I had added previously.

If I had been granted time to add unit tests, those would just function as a source of truth: "sometimes the API returns this kinda weird error, so we handle it. Sometimes this one, so we handle it." Unit tests are nice for that, all things this given program (the UI in this case) needs to worry about from the various things it talks to (it could talk to a couple different APIs who all have different quirks).

I wasn't granted time because the API quirks are considered bugs that are being fixed... one day... hence why the oneliner "refactor" was allowed, but regardless, it has been my go to object lesson in why I finally find unit tests useful.

ketralnis• 4 years ago

Reminds me me https://www.joelonsoftware.com/2000/04/06/things-you-should-... search for "floppy"

weq• 4 years ago

Thats exactly the point of a regression test suite and shows that millions of tests work at giving confidence in a major SQL provider to release a fix to prod so quickly....

cortesoft• 4 years ago

The tests don't prevent bugs, but they do make fixes a lot faster to get out, since you can be confident your fix doesn't break any functionality.

drikerf• 4 years ago

Any functionality that is tested :)

deepsun3• 4 years ago

Not if a simple test requires rewriting hundreds of tests (aka "fragile tests").

Yes, one shouldn't be writing fragile tests, but usually from what I seen at projects with great test coverage, is that it often slows bugfix releases, and especially any bigger changes, as it's very wearisome to also change hundreds of tests.

So I believe there should be some balance between tests/code ratio, as well as attention paid to tests brittleness.

jolux• 4 years ago

but it makes me feel like the millions of tests sound better than they are in reality

What it should make you wonder is if software as well-tested as SQLite still has bugs like this, how much worse is the situation in software with fewer tests?

anitil• 4 years ago

Does that fall within the scope of the core library? It looks like a bug in the CLI program (shell.c) rather than the sqlite lib.

haspok• 4 years ago

I found this interesting (in another blog post of the same author):

Keep your integration testing for smoke tests — to make sure your database actually starts and that you haven’t missed anything basic. Only when there is no way to exercise the code except when an actual full instance of the software is running should an end-to-end test be used.

This is the complete opposite of my experience. But I guess this is because he is developing a library-like software, and I'm mostly working on application code. I found unit tests mostly useless and a waste of time. But I'm sure that for a database library they are absolutely key...

lightbendover• 4 years ago

Any time I make claims like this, people look at me like I'm insane. But here I am, year after year, meeting release targets with robust software while the teams that chase test coverage and other optics don't. I would assume I'm missing something if this industry hasn't give me a million reasons not to believe in the best practices typically put forward.

gregmac• 4 years ago

When I've heard people making similar claims, what I've usually found is they're testing "glue" code: controllers, routers, etc. Personally I find this a near total waste of time: it's hard to write the tests, almost never actually catches a bug, and failures in this code are totally obvious during a smoke test - automated or not.

I write a lot of "application" code (cli, service and back-end) and a lot of tests. Parsing, calculations, file generation, regex .. that catches lots of bugs.

The value comes from keeping the complex code separate from the glue, and of course testing it. And you can easily test dozens of cases, which is usually not true of integration tests due to complexity and run time.

otoolep6• 4 years ago

rqlite creator here.

I'm not developing libraries, I'm developing an entire RDBMS. In my experience -- and this is broader than rqlite -- integration and end-to-end tests seem like they are great - at the start. But as you rely more and more on them they become costly to maintain and really hard to debug. A single test failure (often due to a flaky, timing-sensitive issue) means wading through layers and layers of code to identify the issue.

Overly relying on integration and end-to-end testing (note I said over-reliance, there is absolutely a need for them) becomes exponentially more costly over time (measured in development velocity and time to debug) as the test suite grows. If you find you're having difficulty identifying a place for them it may that you're not decomposing your software properly in the first place. All this is probably manageable if you're a solo developer, but when a team is building the software it can become really painful.

For more details see the talk I gave to CMU[1] on my testing strategy.

[1]https://youtu.be/JLlIAWjvHxM?t=2067

The_Colonel5• 4 years ago

SQLite is probably very well tested, but this "millions of tests" argument is tiring. Most of these tests are algorithmically produced.

benatkin• 4 years ago

That reminds me of caddy templates. Caddy templates cannot have untested code. I think go text templates can. When in doubt, treat templates as code that has access to private stuff. With caddy that includes files and environment variables.

I didn't get bitten by this because I read the docs, but I noticed how easy it would be to misconfigure.

ygra• 4 years ago

Tests usually cannot prove absence of defects. But they can show presence of defects. So yes, a test suite that runs without errors tells you “nothing” in a way. But it's a very good safeguard to ensure that code changes don't inadvertently introduce unexpected changes.

For me it's also the case that I think much more thorough about what inputs could be possible and potentially problematic, so there's often an extra set of test cases around boundaries of input values that would have never been tried when just quickly throwing together a demo application to showcase and experiment with a new feature.

But the fundamental problem that lots of bugs don't appear in testing since those code paths are never tested also means that testing alone isn't sufficient. But I guess we all know that by now, and combining different kinds of tests with other approaches like code reviews (actual proofs are probably beyond the scope for the vast majority of software projects) is being done all the time to not bet everything on a misguided 100 % code coverage unit test approach that's both expensive and fairly useless.

schwartzworld• 4 years ago

Additionally, bug fixes should pretty much always come with tests. If the software has a lot of tests adding a few additional cases to cover the bug is trivial. But if tests are sparse, it may require a lot more time to figure out how to test that code altogether.

Testing also influences code design. 100% coverage requires planning and forethought and it will inevitably be reflected in code quality. Bugs are inevitable, but not all bugs.

cteiosanu• 4 years ago

If that's how it makes you feel then why not go on security bugs hunting spree. If you found one, surely you can find at least 10 more others.

xnorswap• 4 years ago

I feel frustration that the first response was to tell you it why it wasn't a bug and why it didn't need fixing.

gregors• 4 years ago

Tests demonstrate the current behavior of the software. If you don't have tests then all you'd need to do is manually run through millions of test cases manually, hardly better. What you've done is be the first person to go through a test case. Once there's a test that behavior will be documented, which I would much prefer to having to wait yet again for someone to find this test case.

yencabulator• 4 years ago

What really bugs me is the denialism as a recurring pattern. If you hadn't persisted, maintainer would just pretend there are no security issues.

neya• 4 years ago

The top comment is...sad yet funny:

    Euphorbium
    December 11, 2022 at 2:31 pm

    I think I hit the same bug in django, and it took them 5 years to fix it. Django tagline is “webframework for perfectionists with deadlines”. I was fired because of this bug.

I found a bug in SQLite

Related Stories