[HN Gopher] I found a bug in SQLite
___________________________________________________________________
I found a bug in SQLite
Author : otoolep
Score : 564 points
Date : 2022-12-11 17:18 UTC (1 days ago)
(HTM) web link (www.philipotoole.com)
(TXT) w3m dump (www.philipotoole.com)
| nodesocket wrote:
| 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.
| [deleted]
| MrWiffles wrote:
| Props to the author, this was well written. Clear and concise, it
| was easy to follow. Not like my ratings and ravings! ;-)
| otoolep wrote:
| Glad you enjoyed it -- thanks.
| [deleted]
| password4321 wrote:
| 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.
| password4321 wrote:
| https://stackoverflow.com/questions/7764943/what-can-be-done...
| mort96 wrote:
| 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.
| loeg wrote:
| 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
| [deleted]
| morelisp wrote:
| 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.
| YetAnotherNick wrote:
| Also this is not even a silent bug, so I would have expected
| that this would have been caught by someone in production.
| jeroen79 wrote:
| still just submit a bugreport instead of making a fuss of it.
| sigjuice wrote:
| 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 wrote:
| 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.
| 0cf8612b2e1e wrote:
| 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 wrote:
| 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.
| einpoklum wrote:
| > 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.
| jacquesm wrote:
| 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_Colonel wrote:
| It's news only because the author wrote a blogpost about it.
|
| There are bugs fixed in every SQLite release.
| de6u99er wrote:
| Did you have to get baptized first to be allowed reporting a bug?
| kanonieer wrote:
| Obviously. This is a consecrated codebase[1].
|
| [1]: https://www.sqlite.org/codeofethics.html
| fastaguy88 wrote:
| I have seen similar error messages of the "database is corrupted
| ..." type with MariaDB when I simply typed in my SQL incorrectly.
| lifeisstillgood wrote:
| 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
| quickthrower2 wrote:
| 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".
| adius wrote:
| 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 ...
| ygra wrote:
| 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.
| [deleted]
| gregors wrote:
| 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 wrote:
| 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.
| cteiosanu wrote:
| 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.
| haspok wrote:
| 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...
| otoolep wrote:
| 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
| lightbendover wrote:
| 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.
| deepsun wrote:
| It really depends on the type of software. An ETL pipeline,
| for example, is obviously way easier to develop and
| maintain through tests (record real system inputs, compare
| with desired outputs). But that logic doesn't extend to all
| other types of software.
| gregmac wrote:
| 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.
| nicoburns wrote:
| > failures in this code are totally obvious during a smoke
| test - automated or not.
|
| Yes, but if your codebase is large enough then a non-
| automated smoke test can be a very slow process, especially
| if things are configurable. It would have taken 3-4 days to
| smoke test all functionality manually at my last workplace.
|
| Automated tests could make that 5-10 minutes.
| TAForObvReasons wrote:
| 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...
| cryptonector wrote:
| But the testsuite being proprietary is part of their business
| model.
| notRobot wrote:
| 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._
| ec109685 wrote:
| It also makes forking SQLite infeasible since any new
| changes will be woefully under tested.
| [deleted]
| morelisp wrote:
| SQLite's free test suite is far in excess of most other
| projects'. What do you think the test coverage is in the
| Linux kernel, GCC, or Emacs?
| The_Colonel wrote:
| (Embedded) database has stricter requirements than a text
| editor or a compiler.
| morelisp wrote:
| Obviously not, if nobody was willing to buy the test
| suite for their internal forks.
| The_Colonel wrote:
| That's a non-sequitur argument.
| morelisp wrote:
| It follows fine, but you want to debate instead of think.
|
| If the 100% MC/DC coverage was critical to forks, the
| companies that fork (there's lots of them!) would have
| bought it.
|
| Nobody bought it, so it's not _that_ important to
| maintaining a fork compared to the regular test suite
| even for such environments. A test suite which, to go
| back to my first comment, is still leagues ahead of the
| dozen other pieces of lynchpin software most companies
| have no problem depending on.
|
| Meanwhile, for the 99.9% of us out here not building
| aircraft and merely shipping a billion browsers or
| phones...
| The_Colonel wrote:
| > If the 100% MC/DC coverage was critical to forks, the
| companies that fork (there's lots of them!) would have
| bought it.
|
| Again, that's a non-sequitur (or perhaps strawman, you
| can choose), because I wasn't addressing the proprietary
| test set, merely the comparison between a text editor and
| a database, which is completely absurd since the
| tolerance for failures is drastically different.
| morelisp wrote:
| > I wasn't addressing the proprietary test set
|
| Then perhaps you're in the wrong thread to be saying
| anything at all.
| ec109685 wrote:
| Right, but it makes the fork strictly worse from a
| reliability perspective than SQLite given it will be less
| tested.
|
| If there wasn't a competitive advantage given it has no
| sales, wouldn't they have open sourced it by now?
| morelisp wrote:
| > wouldn't they have open sourced it by now?
|
| If there's minimal value in it, why put in the work to
| open source an extremely complex test environment?
| ec109685 wrote:
| Just for completeness sake, they do offer a SQLite
| Consortium Membership for $120k, which I guess includes
| all their test suites as a selling point:
| https://www.sqlite.org/prosupport.html
| bigDinosaur wrote:
| What was the incentive to buy these tests if they've
| already been used to improve the product?
| bluGill wrote:
| You can then audit the tests to ensure they test some
| condition that you are concerned about.
|
| In reality nobody audits source code like that (see
| heartbleed for an unrelated example of critical code that
| didn't get proper audits from people who should have
| cared)
| The_Colonel wrote:
| SQLite is probably very well tested, but this "millions of
| tests" argument is tiring. Most of these tests are
| algorithmically produced.
| weq wrote:
| 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....
| anitil wrote:
| 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.
| cortesoft wrote:
| 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.
| deepsun wrote:
| 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.
| 2rsf wrote:
| Definitely, some real world cases research show that this
| balance stands around 80% for unit tests- you don't earn
| more quality as you add more test above that.
| drikerf wrote:
| Any functionality that is tested :)
| rozenmd wrote:
| "if it's not tested, it's not functionality"
| jeffreygoesto wrote:
| Hyrum would want to have a word...
| ithkuil wrote:
| If only there was a way to run all the tests of all the
| software that depend on your change...
|
| Fwiw, that's what monorepos are good for.
|
| Sadly it's hard to make a "world monorepo"
| franga2000 wrote:
| If you pin your dependency versions and the dependency
| maintainers run tests before releasing, you're already
| almost there (excluding bugs that might arise only in
| your specific environment). If dependencies don't have
| tests or test automation, you can always contribute them.
|
| As for the platform-specific bugs, monorepos only help if
| the way to run tests in all the components is
| standardised. But this is something you can just as
| easily implement across repos. Could be as simple as
| having a testme.sh in the root.
| ithkuil wrote:
| > > "if it's not tested, it's not functionality"
|
| This suggests you may have not 100% test coverage in your
| tests. But 100% coverage if what? What is the
| specification you're defining your behaviour against?
|
| The comment above suggests that you could treat your
| tests as if they were the ones that actually define your
| contract.
|
| > Hyrum would like to have a word.
|
| This is a reference to "Hyrum's law" which says:
|
| "With a sufficient number of users of an API, it does not
| matter what you promise in the contract: all observable
| behaviors of your system will be depended on by
| somebody."
|
| This comment, in response to the previous about defining
| tests as the source of truth for your contract, remarks
| that sadly you can't do that because no matter what
| contract you wish you define, ultimately the behaviour of
| your existing software becomes its effective contract.
|
| > [My comment about monorepos]
|
| Here I suggest that if you extend the notion of what is
| the test corpus to include the test corpus of all of the
| software that depend on you (not your dependencies! The
| code whom your code is a dependency) then you could
| detect if a (yet unmerged) change you're making is
| actually going to affect any existing code.
| franga2000 wrote:
| Totally misread the "depend on you" as "you depend on"
| part, sorry.
|
| Interesting idea, but monorepos still don't help on their
| own. You need a way to detect which modules depend on
| yours and a way to run and interpret their tests. Whether
| they're in an adjacent folder or another repo changes
| very little.
|
| GitHub actually already has a dependency scanning thingy
| that builds cross-repo dependency graphs [0] and package
| managers have been doing that for years. The missing
| parts wouldn't be that much effort to build, but getting
| everyone to set up their repos to work with it would
| probably be prohibitively difficult.
|
| [0] https://docs.github.com/en/code-security/supply-
| chain-securi...
| ithkuil wrote:
| Monorepos are useless unless you have good tooling,
| including a build system that is aware of module
| dependencies inside the monorepo.
|
| An example build system that can do that is
| https://bazel.build
| YoshiRulz wrote:
| That's exactly what the Nixpkgs Hydra instance does--
| caveat being your library and its consumers all need to
| be in Nixpkgs. I'm hopeful that there will be a way to
| keep the tracking part of that going forward, as Flakes
| are adopted and the community decentralises.
| komali2 wrote:
| 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 wrote:
| Reminds me me
| https://www.joelonsoftware.com/2000/04/06/things-you-
| should-... search for "floppy"
| jolux wrote:
| > 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?
| user5678 wrote:
| xnorswap wrote:
| 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.
| [deleted]
| benatkin wrote:
| 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.
| cube00 wrote:
| At least the tests help avoid regressions, you can bet these
| bugs won't come back again.
| habibur wrote:
| 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.
| YetAnotherNick wrote:
| 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
| [deleted]
| markerz wrote:
| 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 wrote:
| >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 wrote:
| so basically, fuzzing.
|
| https://en.m.wikipedia.org/wiki/Fuzzing
| rsaxvc wrote:
| 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.
| robocat wrote:
| I wonder if ChatGPT would be good at generating various
| fuzz testing queries. Maybe too slow? Maybe too repetitive?
| Maybe too unvarying? Sorry for mentioning ChatGPT (to those
| who are sick of the infection).
| mattygee wrote:
| Actually, with one of the problems being faced in that
| space at present, especially for applications involving
| factual answers, being "hallucinations" ( ie. essentially
| as I understand it the level of "creativity" in
| responses) such "creativity" may well be quite suited to
| finding those various unusual edge cases.
| TeMPOraL wrote:
| You know this common trope in fiction and jokes, that
| advanced computers & AIs can be easily defeated by
| feeding them a logical inconsistency?
|
| Guess what, we now have a chatbot AI that not only
| doesn't mind working with nonsense input, it will happily
| _produce_ logically-inconsistent statements on its own,
| and can do it so sneakily and convincingly, that it 's
| _the human operator_ who could end up believing logically
| inconsistent statements, without even realizing it, and
| possibly end up in serious trouble some time later.
| orf wrote:
| 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 wrote:
| 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??
| orf wrote:
| 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"
| devwastaken wrote:
| Sounds about right, most forum software is buggy and fails,
| but those failures don't get sent to the maintainers. It
| just waits until someone whom knows the maintainers says
| "uhhh it's broke.".
| asah wrote:
| in situations like this, I typically report bugs directly
| to members of the core team individually, with gory
| details, and explina that I tried the forum approach and it
| failed for me.
| weird-eye-issue wrote:
| I'm sure they love that
| zoomablemind wrote:
| Perhaps a while ago... For quite some time SQLite official
| forum has been running its own version of Forum software
| (part of Fossil). It allows Anonymous login, with captcha
| (again, Fossil handles), yeah, but this is nothing
| unexpected. So in general, the path to Forum presence is
| fairly unimpeded, well, it is moderated. Reasonably stated
| issues seem to get attention.
|
| Though, some issues indeed need a push to be recognized as
| such, as it's a public forum, so other users may express
| their "other" opinions...
|
| All in all it's Freedom of Reasonable speech in action.
|
| I believe there's a different channel for reporting
| security-related issues. Again, it's through the Forum, but
| there's a private message feature for signed-in users.
| bobleeswagger wrote:
| > 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.
| maxbond wrote:
| For what it's worth I think it's largely overcome the
| reputation of being a "toy" database.
| robertlagrant wrote:
| It never was a toy, so that's good.
| maxbond wrote:
| I'll plead guilty to having this impression until fairly
| recently, but it's an incredible database. While we were
| all not taking it seriously, SQLite was quietly getting
| better and better, year after year. You do that for a few
| decades and you've got an incredible piece of software.
| eduction wrote:
| "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.
| acuozzo wrote:
| It's SQLite... like a mineral/rock. Graphite, Titanite,
| Erythrite.
|
| Not "SQ Lite".
| freeopinion wrote:
| Here's one concept of heavy vs light: I have often wished I
| could just email a small Pg db to a colleague. In the same
| way I can just attach a single file to an email and
| transmit an entire SQLite DB.
|
| I'm not dissing Pg. I really love Pg and I understand that
| it's built the way it is for good reasons. But it sure
| would be awesome to pg_dump and have a single tar that
| could be "run" with a single pg command without worrying
| about which version is required, what configuration is
| required, etc.
|
| When that convenience is the most important requirement,
| SQLite wins. But that is hardly ever the biggest
| consideration in which RDBMS I choose.
| KronisLV wrote:
| > Here's one concept of heavy vs light: I have often
| wished I could just email a small Pg db to a colleague.
| In the same way I can just attach a single file to an
| email and transmit an entire SQLite DB.
|
| The closest to this I've gotten is being able to run
| MySQL/MariaDB/PostgreSQL/other solutions in containers
| locally and sending archived data directories, with which
| they can be launched anywhere else locally, or on server.
|
| I actually had a blog post about how that looks on my
| servers for other applications:
| https://blog.kronis.dev/articles/how-i-migrate-apps-
| between-...
|
| For PostgreSQL, it could look like: 1.
| run PostgreSQL in a container, e.g.
| https://hub.docker.com/_/postgres , use a bind mount for
| the data directory, /var/lib/postgresql/data 2.
| once you want to share, use tar/something else to archive
| the local bind mount directory 3. send the archive
| and the command to run the container through e-mail or
| whatever else you prefer
|
| And on the receiving end: 1. receive the
| run command and attachment 2. unarchive the data
| directory into a folder 3. run the container with
| the provided command
|
| It's not perfect, but it's one of the more portable
| methods I've found (though there are file system issues
| between Linux and Windows sometimes, like when running
| PHP apps).
| robertlagrant wrote:
| It also causes problems. Now I have to worry about multiple
| processes instead of just one, and I need to upgrade it
| separately and test them both together.
| [deleted]
| Quarrelsome wrote:
| Lightweight means I don't need to install some shit on a
| server somewhere and hope that its reachable at the time I
| need to access it. If I'm a process running then I loaded
| from a disk somewhere which means I can in-process sqlite
| to write to that disk.
|
| Less stuff == less to go wrong == lightweight.
| throwawaymaths wrote:
| I remember switching from postgres to MySQL and there was
| definitely a learning curve, so even without deployment
| costs (admittedly the same for mysql -- which were a real
| thing back then before RDS or Aurora or whatever hosted
| postgres)... It probably would have been better to use
| sqlite
| maxbond wrote:
| I read them as "more/less complexity" not "more/less code".
| Postgres is heavier weight because it has more complexity;
| it has more features, it's networked, it has a more
| involved configuration process. They both have different
| advantages and can with in problem spaces the other can't.
|
| This is quite unlike the Apple II, which is outmoded and
| requires a dedicated hobbyist to get working.
|
| Postgres is an excellent default, but preferring lighter
| solutions does solve problems. It eliminates failure modes
| and cognitive load. As engineers we seek to eliminate the
| irrelevant to focus on the interesting. If you can use
| SQLite and avoid shipping a series of containers, and
| instead ship a single binary, you've eliminated things to
| think about.
|
| Neither of them is a silver bullet and you'll be a better
| engineer if you can do both.
| eduction wrote:
| This is an enlightening answer. Part of my issue with
| "lightweight" is that is is vague. I see it used all the
| time. In this case, if people mean "way less
| configuration than Postgres," and "does not need its own
| service and process(es) to manage," that makes total
| sense. And it also gives a hint as to the tradeoffs.
| maxbond wrote:
| This made me smile, thanks for keeping an open mind,
| stranger.
| gjulianm wrote:
| Not really. Heavier and lightweight usually refer to the
| amount of features and requirements of a piece of software.
|
| In that regard, it's easier to see which of PostgreSQL and
| SQLite is lighter. PostgreSQL requires a separate process
| running with its own config, plus the library to
| communicate with it, plus all the things Postgres does...
| On the other hand, SQLite is just a library that reads
| files in a certain format.
|
| > It solves more problems than SQLite and "lightweight" is
| not really a concrete benefit for SQLite.
|
| But it is a concrete benefit. Sometimes you'll have
| restricted environments because either by power or by
| permissions, you can't install Postgres or any other
| database server (e.g., mobile phones or embedded software).
| Or sometimes you just don't want the user to configure
| their postgres instance and your software for just a few
| tables (e.g., system utilities/small services that just
| need a simple database).
| LVB wrote:
| > 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 wrote:
| > 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.
| tiffanyh wrote:
| > 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.
| robertlagrant wrote:
| Evidence of a wonderfully comfortable life. We should all
| hope to have horrible experiences that are that
| inconsequential!
| setr wrote:
| He's talking about the forum software, not the forum
| community.
| eduction wrote:
| 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.
| otoolep wrote:
| 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 wrote:
| > 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...
| playingalong wrote:
| 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 wrote:
| 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 wrote:
| 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 wrote:
| 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.
| neya wrote:
| 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.
| techn00 wrote:
| It's impressive to find a bug in something like sqlite, which is
| famous for its larger than the codebase test suite
| capableweb wrote:
| > 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.
| loeg wrote:
| This is a definition of "serious projects" that excludes
| almost all serious projects.
| rat9988 wrote:
| I'm not sure, all of mines at big companies have followed
| this definition.
| valray wrote:
| > 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.
| baq wrote:
| They count post-codegen test LOC, the checked in ratio is
| much smaller - used to be something like 5:1. Still super
| impressive!
| civopsec wrote:
| Test to regular code ratio shouldn't be impressive by itself
| anyway, considering how repetitive, duplicated, and setup-
| heavy test code can be.
| markerz wrote:
| 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
| zoomablemind wrote:
| >... 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).
| otoolep wrote:
| Actually, they added a unit test in a follow-on change:
|
| https://www.sqlite.org/src/info/dc7dd2d3e50e7cc4
| zoomablemind wrote:
| Indeed, thanks for pointing this one out.
|
| The test seems to test a shared access in rather a serial
| order. I wonder if underneath this is actually running as
| concurrent processes?
| otoolep wrote:
| I may not be following your point, but I don't believe
| the root cause of this issue was a race condition, or
| anything that might be related to concurrency.
|
| The fundamental cause AFAIK was a SQLite connection was
| attempting to make a state transition (from one type of
| locking state to another) which shouldn't be allowed
| under certain circumstances, but the implementation
| didn't actually enforce this rule. So the added test
| really does test the root cause.
| ChuckMcM wrote:
| The lesson here is never ignore your own "Huh, that's weird."
| experience when using software. :-)
| nonrandomstring wrote:
| 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".
| thaumasiotes wrote:
| 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.
| otoolep wrote:
| https://quoteinvestigator.com/2015/03/02/eureka-funny/
| lost_tourist wrote:
| I usually only ignore things when I'm just too busy to care,
| unless it stops me from doing what I'm currently doing.
| jicea wrote:
| A French proverb is << Il n'y a pas de fumee 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...
| nszceta wrote:
| Smoke is absolutely possible without a fire. Have you ever
| seared any meat on an electric stovetop? Overheated an empty
| frying pan?
| nix23 wrote:
| The definition of fire is kind of not precise, Humans are
| on fire (burning calories with oxygen) well nearly
| everything in contact with oxygen is "on traditional fire",
| burning alcohol is invisible and no smoke, and the sun does
| not need oxygen (fusion), so you can say everything that
| "decays" is on "fire"....even your mixtape.
| komali2 wrote:
| By what means was electricity generated to be delivered to
| your stovetop? ;)
|
| ("solar panels!" sun is a ball of fire)
|
| ("nuclear power plant!" via what means was the steel in the
| brace for the control rods forged then? :D )
| Dylan16807 wrote:
| Congrats, you have now proved that everything is fire. So
| "no smoke" also means fire if we follow your logic. Was
| that your intent?
| komali2 wrote:
| The point of the saying is that if there's evidence
| (smoke) of something normally worth paying attention to
| (fire), said thing is indeed present.
|
| If something is smoking on your stove, it's worth paying
| attention to, whether it's because you're actively
| searing something on an electric or because your pan is
| empty.
|
| My OP was the one you should come at with this tone lol,
| they were the ones that sidetracked off whether or not
| "if there's smoke, there's fire" is a useful idiom. Maybe
| a better question would have been, "though there's no
| actual fire, would not those instances of smoke still be
| worth noting?"
|
| Shall we start thinking of times smoke is present, and
| not worth anyone's attention?
| Dylan16807 wrote:
| They sidetracked by taking too binary of an
| interpretation, sure, but your rebuttal was flat-out
| broken.
|
| I don't feel the need to reply to _every_ post I disagree
| with.
| me_again wrote:
| Since we seem to have started a competition about who can
| provide the least relevant and most pedantic correction,
| the sun is not a ball of fire. It is a plasma heated by
| nuclear fusion. There is no oxygen to support combustion.
| protomolecule wrote:
| Exactly the same in Russian: "Net dyma bez ognia".
| Koshkin wrote:
| I guess there was something they could share during the
| Moscow fire in 1812.
| [deleted]
| bewaretheirs wrote:
| The usual form of that proverb in English is "When there's
| smoke, there's fire".
| aidos wrote:
| Is it? Interestingly I've only heard it as "no smoke
| without fire"
| arcanemachiner wrote:
| I've heard both.
| thedrexster wrote:
| "Where there's smoke, there's fire" is how I've heard
| that proverb
| rgbrgb wrote:
| "Where there's smoke, there's fire" is what I've heard
| (from fire heavy Southern California).
| berny222 wrote:
| This is how we phrase it in Australia.
| brokenmachine wrote:
| I'm Australian and have only heard, "where there's smoke,
| there's fire".
| quickthrower2 wrote:
| Hire me a full time bug reporting assistant then please :-)
| yellow_lead wrote:
| 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 wrote:
| 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.
| eigenvalue wrote:
| 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 wrote:
| > 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.
___________________________________________________________________
(page generated 2022-12-12 23:02 UTC)