5 ms·
SQLite does not do checksums
- Quarrelsome 2y agoI am extremely happy to learn of Checksum VFS Shim. So this article has done its part to make the world a slightly better place. :)
- deleted 2y ago[deleted]
- ncruces 2y agoMy Go driver adds support for this by default, and I provide a simple API to enable it. I'm of the opinion that the VFS shim should ship by default, certainly on the CLI, because it still needs to be enabled for each DB, and all it takes is one instance that doesn't know about checksums to modify a checksummed DB for it to appear corrupt. https://github.com/ncruces/go-sqlite3/tree/main/vfs#checksums https://github.com/ncruces/go-sqlite3/tree/main/vfs#checksum... Weirdly, databases created with the macOS version of the sqlite3 CLI reserve 12 bytes, for some reason, so you can't ever enable checksums on those DBs.
- ncruces 2y agoAlso, I'm not 100% certain what the interaction is between the checksums of the VFS shim and the WAL checksums during checkpointing w.r.t. silent durability loss. The shim's checksums are explicitly ignored during checkpointing so I'd need to think it through. The VFS is focused on not (silently) accepting corrupted data, not necessarily on not (silently) ignoring it.
- ncruces 2y agoYeah, I've double checked. The checksum VFS shim does nothing to fix the (silent) data loss a random bit flip in the WAL causes. If you want long term durability, checkpoint your WAL. Then the checksum VFS will help.
- OskarS 2y agoHonestly seems pretty reasonable to me. SQLite operates above the filesystem, and it seems to me the filesystem is the appropriate place to do this kind of error correction/detection. If you need this kind of durability of your data, why are you storing anything on a filesystem that doesn't do this?
- kevincox 2y agoYeah. I agree. That being said we do line in a world where non-checksummed filesystems and non-ECC RAM are common especially on consumer devices. That being said disk or file-level checksums are an incomplete solution without ECC RAM anyways.
- xyzzy_plugh 2y agoI'm going to present a potentially controversial opinion: you should always consider adding checksums. Checksums are great, easy and almost assuredly cheap. CRC32C has hardware acceleration support on any modern CPU. Writing a masked CRC32C is incredibly cheap and you'd be surprised at all the places you can catch corruption. The sooner you write a checksum, the more value it adds. If you calculate a checksum later on, such as when you're rotating files on disk, or archiving to cold storage, it may be too late. What do those checksums represent? The checksums may be accurate, such as those used by your filesystem, but the data is already corrupted before they were calculated.
- bawolff 2y agoWhat part are we checksumming? No matter how fast CRC32 is, you still have to transfer the data from disk to cpu. I suspect reading the entire db would introduce unacceptable latency & i/o strain in many usecases for sqlite.
- ajsnigrutin 2y ago+1 for this. I use sqlite for some "logging-like" thing a lot, the file is in the ~1.5GB range and growing, and every minute some data is logged to it. Having to read 1.5GB of data from the disk every minute, to add a (few times) timestamp and one 64 bit number to that data seems pointless.
- macote 2y agoI think it's a question of trust and responsibility, and the client should decide where to draw those lines. Depending on the environment and context, it could be totally acceptable to trust the filesystem, but not in other cases.
- astrobe_ 2y agoYes. It is a "storm in a glass of water", as we say. Probably the client application doesn't assume cosmic bit-flipping events for its own files, which would have equally bad consequences, either. Probably the likeliness for this happening and not being caught and corrected by the storage medium is as low as a hash or UUID collision, which everyone conveniently round down as "impossible".
- deleted 2y ago[deleted]
- deleted 2y ago[deleted]
- anotherhue 2y agoZFS & BTRFS both have native FS checksumming, I don't think there's a strong reason not to use it, except maybe in a contained embedded context. Many years ago ZFS checksums helped me figured out I had a bad DIMM.
- lxgr 2y agoThere is: It’s already done at the layer below the file system. In the best case, repeating it at the FS layer wastes performance; in the worst case, it causes confusion as to which layer has to do it and then maybe nobody does. For the same reason, e.g. HTTP doesn’t have a checksum either, and IPv6 even got rid of its header checksum.
- anotherhue 2y agoYou mean the disk controller or something else? I had not heard of that.
- roncesvalles 2y agoMost modern hard drives (and all SSDs iirc) have block-level ECC.
- oarsinsync 2y agoHTTP depends on the TCP and Ethernet layer checksums, which is not great. At today’s bitrates, it is remarkable how frequent a bitflip can occur without invalidating the TCP or Ethernet checksums. HTTPS effectively gets integrity validation by default, as TLS takes care of that. TLS is much more effective at this than the checksums are.
- chasil 2y agoThe problem with btrfs is that is does not work well with databases without careful adjustment. https://archive.ph/dqFgh https://archive.ph/dqFgh
- anotherhue 2y ago
- joseangel_sc 2y agoat the end it says most dbs don’t do this, except some, can someone tell me one that actually does it?
- ndxf 2y agoPostgreSQL has data checksums, but the option is not enabled by default. More information at: https://www.postgresql.org/docs/current/checksums.html https://www.postgresql.org/docs/current/checksums.html
- taffer 2y agoDb2, Oracle, Postgres and SQL Server do checksums.
- zX41ZdbW 2y agoClickHouse uses and validates checksums for both compressed blocks of data and uncompressed data; it also checksums and validates data during network transfers. Additionally, every replica validates checksums with other replicas when doing deterministic computations. It is the only reasonable way to work with data, and if someone thinks otherwise, I will happily provide motivating examples.
- hodgesrm 2y agoFully agree. There are many bad things that can happen to data. Another good feature is that ClickHouse is fairly tolerant of torn blocks and handles many failures automatically. This fault tolerance actually makes some corner cases hard to illustrate in demos, because ClickHouse repairs them silently. Users never see them.
- bakugo 2y agoMySQL does it by default (or at least InnoDB does).
- rrdharan 2y agoAll the native storage systems at Google do it at every level (block, file, database); I would assume the same is true at AWS and Microsoft.
- bawolff 2y agoGiven how sqlite is used, running a hash over a giant (possibly multi-gb file) on startup sounds like a really bad idea.
- compressedgas 2y agoThe checksums would be on a per page basis. Only the pages of the file that are actually read would be hashed. No need to hash the entire file unless you want to scrub it though this would automatically happen when the database is vacuumed.
- deleted 2y ago[deleted]
- sethev 2y agoThe checksum in the WAL is to detect incomplete writes (due to crash or power failure). That data was never committed successfully. Throwing an error for this scenario would mean that sqlite is unable to recover from a crash.
- ncruces 2y agoIt was committed successfully, if there's a valid commit frame in the WAL. It simply wasn't checkpointed at this point. If the WAL has valid data frames and a valid commit frame after a corrupted frame, there's a strong likelihood this was caused by accidental corruption, so an argument could be made that silently deleting such a WAL is not necessarily the best idea. A lesson to be learned here, is that the durability of stuff in the WAL is reduced, even if you use PRAGMA synchronous=FULL.
- Andys 2y agoThis is likely to happen in the case that your hardware is telling lies about when it has committed data to disk. In this case, it makes sense to cut short the WAL application, but you are probably right in saying it should throw an error (or at least a warning).
- ncruces 2y agoYou're right that write reordering might cause this (an invalid frame followed by a few valid frames). That shouldn't happen if you use PRAGMA synchronous=FULL, but in WAL mode it's very common to use NORMAL. Not sure what's the better strategy here, but I'd definitely appreciate a mode that warns me before silently truncating a WAL that has valid frames in it.
- sethev 2y agoThat's true. The protocol aware recovery paper [1] talked about the challenges of disentangling corruption in the middle of the log vs uncommitted data at the end of the log. This is an issue in other log-based systems as well. The OP made it sound like an oversight rather than an implication of assuming that the filesystem won't return corrupt data that was successfully written and fsync'ed. Sqlite is pretty upfront about the tradeoffs: https://www.sqlite.org/howtocorrupt.html#_failure_to_sync https://www.sqlite.org/howtocorrupt.html#_failure_to_sync 1: https://blog.acolyer.org/2018/02/27/protocol-aware-recovery-for-consensus-based-storage/ https://blog.acolyer.org/2018/02/27/protocol-aware-recovery-...
- ndxf 2y agoI don't think any banking apps are using SQLite as their main database storage in any capacity, so the example in the article seems a tad extreme. The worst use case for SQLite that I've seen in production was a shop using it with some minimal UI on top as an order tracking system. Even in that case, the orders were accepted on paper, they just used the SQLite db as a way to track material usage so they knew what to order from the suppliers. I wonder if anyone has a more egregious or worse example of SQLite in production. The behaviour of SQLite in not raising an error when detecting a corrupt WAL frame also seems to be on purpose.
- lxgr 2y agoSQLite is a (maybe unexpectedly) powerful database. I would not be surprised at all if it’s used for financial data in at least some capacity. I’m also not sure every “proper” database has application layer checksums, as that seems like a lower layer concern costing performance and possibly providing a false sense of security (it can’t help with durability at all, for example).
- twisteriffic 2y ago> The worst use case for SQLite that I've seen in production was a shop using it with some minimal UI on top as an order tracking system. Even in that case, the orders were accepted on paper, they just used the SQLite db as a way to track material usage so they knew what to order from the suppliers. This doesn't seem particularly egregious.
- chasil 2y agoWell, sqlite is certified for avionics via do-178b. What interesting things could it do in an aircraft?
- mentalgear 2y agoPostgreSQL, one of the most popular databases for reliable data management, also does not enable this feature by default. "By default, data pages are not protected by checksums, but this can optionally be enabled for a cluster. " https://www.postgresql.org/docs/current/checksums.html https://www.postgresql.org/docs/current/checksums.html I feel it's quite unfair for the title to call out sqlite on checksums, when in reality, as the very closing line of the article states most databases don't do checksum verifications.
- llm_nerd 2y agoThe submission doesn't seem unfair. It's simply pointing out something for users of a specific database to be aware of, and I don't really think SQLite is maligned or impugned in any way. And FWIW, Oracle, SQL Server and DB2 enable page checksum storing/verification by default.
- deleted 2y ago[deleted]
- RicoElectrico 2y agoPeople are just bored of security sensationalism I guess. Too many people want to gain visibility just by reporting either something little-known (but still known) or that needs stars to align in a proper way to be exploitable at all.
- MattPalmer1086 2y agoChecksums aren't used to protect integrity for security, they're about data corruption.
- Brian_K_White 2y agoA long time ago there was a famous / infamous ad campaign for some food product like bread or milk, and the ad merely stated the true fact that their milk didn't contain any bleach. Of course no one's milk (or whatever it was) had any bleach (or whatever it was). Specially highlighting something true, but out of context and with no equally special justification, is not an innocent act and is misleading. And yes, absolutely, very clearly, causes harm, and does so unfairly. A not-unfair version of this same article would just talk about databases, and include sqlite with others, and not only sqlite and not be titled sqlite. And then there is this: "Hey there I am v. I work at Turso Database."
- coding123 2y agoYou want a database that was originally designed when hard drives failed a lot more.
- llm_nerd 2y agoApplication level checksums aren't for when the hard drive fails -- the OS will catch that, as every single operating system is already assuring hard drive blocks. Application level checksums are for memory, driver, subsystem faults. And many enterprise database systems still do it by default, regardless of how redundant it might seem. Even though your NAS, file system, OS, and even the individual drives are computing it too, systems like SQL Server, Oracle, DB2 and others default to calculating and verifying checksums on all pages.
- paulsutter 2y agoThey should have an option to write every block twice including the WAL. SQLite is already so robust, why not go the extra mile Has anyone seen a study of the pattern of bit errors experienced on disks? Like are they a single bit or multiple consecutive sectors? At Quantcast in our QFS distributed file system we used a very efficient reed-solomon coding to get redundancy as good as 3x blocks with only 1.5x storage and still got gigabytes/sec encoding speed. We spread blocks out over different cabinets (not just machines or drives) but that would be overkill here. Obviously we still had end to end checksums because errors can happen anywhere, especially in disk and network controllers whose firmware is usually a one off hack
- lxgr 2y agoWhy? SQLite has a very well defined and documented set of abstractions about what can and can’t go wrong on the layers of abstraction it relies on. “Out of two writes, at least one will be successful” is not part of that, and doesn’t make sense to me. Second-guessing the storage layer like that will never be able to catch all problems with it (e.g. drives or file systems lying about the durability of write commands).
- paulsutter 2y agoSorry but bit errors happen all the time at scale, and most commonly in controllers. SQLite is used in so many environments for its robustness rather than actually for SQL. Seems odd to argue agsinst it actually Drives, controllers, and file systems lying about write durability is another great reason to add more robustness, good point
- lxgr 2y ago> Drives, controllers, and file systems lying about write durability is another great reason to add more robustness, good point No, the solution for that is to not use these, not to greatly entangle layers via workarounds in the ones above them.
- chasil 2y ago
- chasil 2y ago"Most databases (except a few) assume that the OS, filesystem, and disk are sound." The Oracle "dbv" utility will verify all checksums by file that compose a table space. This includes the free XE edition.
- bityard 2y agoSqlite is the wrong layer at which to care about storage hardware failures. That would be the job of the disks themselves, the hardware or software volume manager, or filesystem.
- avinassh 2y agoSQLite or any other mainstream database like Postgres don't do checksums. Wouldn't you be concerned if you are using it for financial data or medicine or something crucial?
- School-Cotton 2y agoI’d be concerned if a system without ECC ram and disks were being used for something crucial.
- avinassh 2y agoYou would be surprised :) A couple of my friends who work in the financial domain showed surprise at this post - they had no idea this was possible. I guess most people just assume databases 'just work'?
- School-Cotton 2y agoFWIW I said I’d be concerned, not surprised. SQLite isn’t doing anything wrong here, IMO — people who use non-ECC ran for critical workloads are. But people do wrong things all the time, so it doesn’t surprise me.
- GauntletWizard 2y agoIf I could get ECC ram for my consumer hardware, I would - but I'm not paying the xeon tax to store my photos, in addition to the necessary 20% premium for the ram sticks themselves, for my personal workloads.
- zazaulola 2y agoECC algorithms can be used to find errors in read data: https://en.wikipedia.org/wiki/Error_correction_code https://en.wikipedia.org/wiki/Error_correction_code I was introduced to the first ECC algorithm in high school: the Reed-Solomon Code: https://en.wikipedia.org/wiki/Reed%E2%80%93Solomon_error_correction https://en.wikipedia.org/wiki/Reed%E2%80%93Solomon_error_cor... This algorithm remains mysterious to me to this day. It allows you not only to indicate the presence of an error, but also to recover any data with a small redundancy (e.g. 5%). Reed-Solomon Code principles are embedded in the workings of WinRAR. When creating RAR-archive, you can add redundant data to restore the archive in case of storage interference: https://thuthuattienich.com/wp-content/uploads/2015/05/cach-tao-tao-recovery-record-3.png https://thuthuattienich.com/wp-content/uploads/2015/05/cach-...
- jrockway 2y ago> This algorithm remains mysterious to me to this day. The basic idea of Reed-Solomon is to represent a piece of data as though it's the coefficients on a polynomial and then transmitting the value of various points on the polynomial. You have the opportunity to provide an infinite number of points, and you only need the <order of the polynomial> to be correct to derive the original polynomial. If the polynomial is overspecified, then you have the opportunity to derive the original polynomial for each set of points and see whether or not you get the same result each time. Various combinations of this outcome result in no errors, errors that are corrected, or errors that are detected but not corrected. The rest of Reed-Solomon is about which polynomial to pick, as the selection of polynomial and where to evaluate it makes the coefficients easier to derive, and how "finite field" math works. We're used to math over all real numbers from math class; finite fields define the same operations but over a finite number of numbers (i.e. "all uint8s"). This is convenient when implementing this algorithm on a computer. (The math, of course, still works when "all real numbers" is the domain of the coefficients and points; restricting to fixed-size integers just makes things easier for computers.)
- zazaulola 2y agoI'm not a mathematician. It works like magic for me. Simply put. Suppose you have 64 bits (8 bytes) of regular RAM. For ECC-enabled memory, a similar block of memory will occupy 72 bits (9 bytes). The first 64 bits will be identical. What would need to be stored in the remaining 8 bits so that any of the 72 bits could be detected and recovered, assuming only one bit was corrupted?
- mediumsmart 2y agothank you for sharing
- prirun 2y agoIMO, checksums at the filesystem and higher level are only useful for maybe detecting software problems, not cosmic ray RAM bitflips. ECC RAM is the only way to reliably detect random RAM bit flips. Without ECC, if you read a block of data from disk into a RAM page, do whatever checksums you want - filesystem page, database page, whatever - and THEN a bit flip occurs, you are now processing bad data but think it's okay because all of the checksums passed. Every disk block has an ECC code recorded with it to detect and correct errors. Hardware busses have checksums to detect transmission errors. But without ECC RAM, there is no protection for RAM bit flips, and higher-level checksums won't help with that. It's ridiculous that all computers don't come with ECC RAM, and it's mainly that way because Intel wants to charge a premium for data center servers.
- d_tr 2y agoDoesn't matter, you should explicitly specify such a basic configuration option yourself anyway unless you are clueless and don't know any better.
- pajop 2y agoStack Overflow: https://stackoverflow.com/questions/4637764/checksum-for-a-sqlite-database https://stackoverflow.com/questions/4637764/checksum-for-a-s...
- nephanth 2y agoDo modern filesystems not implement fancy error-correcting codes? If not why?