12 ms·
How to Corrupt an SQLite Database File
- thrower123 4y agoAccidentally check it into git on a multi-developer team...
- zvrba 4y agoI read this as a damnation of POSIX filesystem semantics. File descriptors getting reused, insane locking semantics on close, allowing you to delete an open file and providing no means to prevent it, fork causing problems, unspecified behavior of sync() (is it barrier or "full" sync). Though: Multiple copies of SQLite linked into the same application. Weird and rare scenario, but why not keep the global list of open sqlite files in a global shared memory segment?
- kevingadd 4y agoMy guess is the shm to detect multiple copies linked in would probably be a lot of complexity for the sole purpose of trying to compensate for a really bad practice.
- psyclobe 4y agoYeah global shm state is a bitch you have kinda a ownerless resource that must be managed somehow cooperatively. This adds global mutexes to the equation and quirky concepts on initialization and abandonment of the shared state. Usually none of these issues are dealt with in the shared memory sdk you’ll be using, so you gotta model it with mental experiments just like you would a lock free implementation in a threaded process. Simple no, doable yea, error prone you betcha.
- zvrba 4y agoYeah, I agree.
- CGamesPlay 4y agoHow would the multiple copies find the global memory segment? In theory each copy could create such a segment and check that all handles received by APIs live in it, but I can’t see how you could convince multiple versions to get a handle to the same shared segment (well, without a file handle).
- zvrba 4y ago> How would the multiple copies find the global memory segment? They would all use the same reserved "name". It's possible to atomically create or open a SHM segment with a given "name". Then have a header at the start of the segment with critical metadata like signature, version, etc. Barf unconditionally if the metadata is found to be invalid (i.e., some other process deliberately used the name to sabotage sqlite [1]), or, in addition, have an option to not use SHM. [1] The situation is not different from some process sabotaging another by deleting or corrupting well-known files.
- CGamesPlay 4y agoI might be misunderstanding something, but doesn’t that mean that only one SQLite library could be loaded per system? That hardly seems like an acceptable compromise for an embedded software.
- SQLite 4y agoYou misunderstand. It means that a single application should not use two or more copies of the SQLite library to open the same database file. As a practical matter, you really have to work hard to get an application to use two different copies of SQLite at the same time - all the while avoiding symbol collisions on link. You can do it, but it takes some work. And then on top of that your application has to decide to open two or more connections to the same database file, using different copies of SQLite in each case.
- mst 4y agoYeah, managing to do that is ... almost impressive. The more common failure mode I've seen is a library bundling and inlining an old version of a database API in a way that means it overrides the shared object I was expecting my code to link to, and alarums and excursions resulting thereby. (I don't imagine SQLite would -break- in that situation, but I doubt my application code would be any less unhappy than in the cases of that problem I've encountered in the wild)
- gigatexal 4y agoEhh. I kinda like being able to update the browser binary while it’s in use and and in memory and then only get the new version when I restart the browser.
- ectopod 4y agoWhat I get is a crash when I open a new tab because the process launched to manage it is using the newly installed binary, which isn't compatible with the old binary that's running. Similar problems occur if you do in-place upgrades of software using dynamic libraries.
- afiori 4y agoit is fine to allow for such a feature, it is also nice to allow protecting against this possibility
- bayindirh 4y agoWhy everything which doesn't work as someone expected is called "damned"? These features have been there for decades and we know what to do, what not to do, and how to exploit these features. I personally like how POSIX works, and how well documented its operation and failure modes. I love systems which don't prevent foot guns, and go bang spectacularly when I foot gun myself. It's also ironic how we don't get to like systems which do limits us in severe ways, and then get angry to a system which doesn't limit us the same way. Fun times.
- ynik 4y agoWe know what to do, but no single developer is in a position to do it -- POSIX file locking requires global coordination between all developers of code running inside a single process. This is fundamentally incompatible with the use of libraries. Observe that there is no way SQLite could fix this bug, the SQLite developers instead have to shove off the responsibility to everyone one using the SQLite library. Add a few more layers of libraries where this requirement isn't documented as clearly, and this is basically guaranteed to go wrong somewhere.
- zvrba 4y ago> Why everything which doesn't work as someone expected is called "damned"? Because POSIX doesn't provide features that are necessary for reliability. Workarounds, if they exist, are highly platform-specific. That I'd say is damnation of the standard. For example, how do you, within POSIX, ensure that a write to the storage media is "durable"? People argue about what combination of various "sync" calls and open flags you need to achieve this. (Disregard the case of hardware cheating -- nothing can be done about that.) How do you make an "owning" lock on a file: a kind of that you and only you (the owner) can remove? Why is a lock applied per for file descriptor and not the file object? What is the workaround here? In general, POSIX + threads = damnation because there's too much per-process global state, which was kind of OK "before MT". It's clear that the standard was written before multithreading was even foreseen to be the norm, and it contains many SNAFUs wrt threads, not just files. (Signals and fork are the first to come to mind.) Oh, yeah, don't mention Linux. It has so many extensions and additions to POSIX to alleviate POSIX messups that it's not even funny.
- hnarn 4y ago> allowing you to delete an open file and providing no means to prevent it What exactly do you mean by “providing no means to prevent it”?
- zvrba 4y agoLike Windows file share mode. Mandatory, "hard" lock on a file. The OS doesn't let you delete it if another process holds it open. (You can forbid any of 3: reading, writing, deleting by another process. It is completely orthogonal to the ACL permissions system.)
- tinus_hn 4y agoAnd it’s the reason you need to reboot all the time.
- zvrba 4y ago??? All the time? I reboot ca once a month, for windows update.
- BenjiWiebe 4y agoYou can update Linux without rebooting, except for the kernel (and even then there's options). Rebooting once a month is way more often than some of my Linux systems.
- zvrba 4y agoEh. What about updated libc.so or libssl.so? If daemons using them do not get restarted, they keep using the old version. If they do get restarted, well, then, it's just as if you rebooted the machine. Somewhat quicker, but in effect it's a reboot.
- oauea 4y ago> but in effect it's a reboot no. restarting some daemons one by one when it's convenient is wildly different from taking down the entire system
- Pxtl 4y agoWindows has the reverse problem, where you are blocked to delete an in-use file, but it's buggy as hell and very opaque as to what is holding that file.
- zvrba 4y agoThe windows feature is not buggy, the process that created the file potentially is as the creating process chooses the sharing mode. Except for executable images (EXE/DLL): they're always locked on the filesystem as long as any running program is referencing the image. That's by design: designers didn't want "invisibly" used space on disk. For what happens when you do that on Linux: the file entry gets deleted, but disk space doesn't get deallocated until the last program using the file has exited. If you replace the file, the program will still be referencing the old file (now unreferencable by any name/path, except possibly through /proc/../fd).
- Pxtl 4y agoI've dealt with far too many cases of crashed programs retaining their locks on files to call it anything but buggy. And Windows offers no UI for finding out which process is holding the file handle.
- zvrba 4y agoI think your diagnosis is wrong. When the program exits, in whatever way, all resources/locks/etc are cleaned up. (Hey, the OS is also nice enough to tell waiters about abandonend mutexes.) There's no built-in GUI, but there's Process Explorer and other sysinternals utilities that you can use to find out what is keeping the file open.
- layer8 4y agoWindows comes with Resource Monitor which can be used for that purpose: https://superuser.com/a/643312/62 https://superuser.com/a/643312/62
- asveikau 4y ago> File descriptors getting reused I don't know how you could avoid this, and it has me wondering if you understand the issue. This is basically the file descriptor version of a use-after-free bug. I'm not one of those "rust fixes everything" people, so I don't answer this about just anything, but I think this issue would completely disappear in a memory safe language and especially one where the wrapper for file descriptors had good prevention of race conditions closing a file, so it hardly seems fair to blame the filesystem API. I mean, in some cases you could blame the API for high frequency of application bugs, but using a closed file seems like a bug category that is unreasonable to do so for. The issue of use-after-free is larger than the filesystem API.
- rfoo 4y agoWriting to arbitrary file descriptor (because, well, a fd is just an integer, right?) is safe Rust. So, no.
- asveikau 4y ago> and especially one where the wrapper for file descriptors had good prevention of race conditions closing a file This is an ownership issue is my point. It's not exactly memory safety but the same concept applied to an integer rather than a pointer.
- chrismorgan 4y agoIf you want to write to an arbitrary file descriptor in Rust, you need to use unsafe code (std::os::unix::io::FromRawFd, unsafe fn from_raw_fd). … though if you’ve got a procfs around, /proc/self/fd/* is ripe for the writing.
- mst 4y agoI have previously used memfd_create plus /proc/self/fd/* to emulate fdlopen on systems that only provide dlopen. I strongly recommend finding a different approach ... but it totally worked.
- moleperson 4y ago
- account42 4y ago> allowing you to delete an open file and providing no means to prevent it I honestly think this one of the greates advantages of POSIX systems over Windows. If I as a (super)user want to delete a file I should always be able to do that and applications will just have to deal with it.
- zvrba 4y agoYou can delete the name/directory entry, but the file remains allocated and in use as long as any program holds it open. So how useful the POSIX behavior is, is debatable.
- TekMol 4y ago"Backup or restore while a transaction is active" Is it possible to turn off transactions alltogether in SQLite? So they function like a MyIsam table in MySql or a Aria table in MariaDB? I have crunched many billions of queries over the last years in MySql and then MariaDB. I like MariaDB because it offers tables that are way faster due to no transaction overhead. I know this goes against the popular opinion to use transactional tables for everything. But depending on the task at hand, the performance gain can be very well worth turning transactions off. I consider trying SQLite. But if you cannot switch off transactions, performance will probably not be up to par.
- dataflow 4y agoAFAIK there's PRAGMA journal_mode = OFF, which may help with performance, but if you want to go beyond that, you may [1] have to modify the code or write your own VFS driver. [1] https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg33057.html https://www.mail-archive.com/sqlite-users@mailinglists.sqlit...
- capableweb 4y agoTo expand on this, journal_mode controls if SQLite should document the transaction or not, not if there should be transactions at all, so you're effectively disabling logging, which indeed should also contribute a bit to better performance.
- hnlmorg 4y agoUnless you are referring to another meaning for the term “transactions” that I’m unaware of, I’ve found running your SQLite queries inside transactions grately increases throughout.
- TekMol 4y agoBatching multiple queries into a single transactions of course greatly increases performance compared to have one transaction per query. But it does not give you the performance you get if there are no transactions in the first place.
- andix 4y agoThat one is easier as you think: 2.2.1. Multiple copies of SQLite linked into the same application I used two different C# libraries to access the same database. And under the hood, they both used a different SQLite library, with different native SQLite builds (of the same version though). Took me some time to figure out…
- exikyut 4y agoIf they were both the same version, things should technically work, at least on disk. ....ah, but if they were tripping over each other in memory I can see that going sideways. What exactly happened?
- andix 4y agoI don’t know it, but as far as I understand it has to be the exact same shared library, otherwise some in-memory caches don’t get updated on writes. This doesn’t happen, if it’s two separate processes, that are using different libraries. In my case it was just one process. But it’s also possible, that SQLite was built by different compilers/toolchains and that did the trick. What happened: my database files were always corrupted and needed repair. The application did only a few writes, and sometimes the changes just disappeared. Edit: the SQLite website says: „But, if multiple copies of SQLite are linked into the same application, then there will be multiple instances of this global list.“
- mst 4y agoYeah, the problem is inherent to two copies of libsqlite, their being both the same version just makes things marginally less horrific.
- zabzonk 4y agoReally, the only way to prevent corruption of a SQL (or any other kind of) database is to have a server process manage all accesses to the database storage. And even then.... SQLite is great, but I would not use it in a multi-machine, multi-processing environment.
- samatman 4y agoSQLite is among the most reliable software you can choose, and there's nothing about a multi-machine, multi-processing (?) environment which changes that. Developers do need to understand that it's SQLite, not a poor man's Postgres. This require correct engineering, and the SQLite docs are in a class of their own in patiently explaining in simple terms how to do this. If you embrace what SQLite is, you can do a remarkable number of things with it, safely and reliably.
- notRobot 4y agoI've used SQLite quite a bit, and going by my experience, you're absolutely correct. > SQLite docs are in a class of their own in patiently explaining in simple terms how to do this. Yes! I love reading the SQLite (and Fossil!) docs. They're so beautifully written!
- psnehanshu 4y ago> SQLite is great, but I would not use it in a multi-machine, multi-processing environment. Yeah great, you shouldn't. SQLite is meant to be replacement of custom files to store data by apps (mobile apps, desktop apps, server apps). This isn't meant to scale to millions of users, but just store data in a structured manner.
- andreareina 4y ago> SQLite does not compete with client/server databases. SQLite competes with fopen(). https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
- 4y ago
- div3rs3 4y agoAn Es Kew El or A Seakewl Lite
- cm2187 4y agoI actually googled to check the rules, I didn’t think of the first alternative.
- dinkleberg 4y agoThe title actually surprised me because of that. I would’ve assumed the SQLite team would pronounce it sequelite since es kew el ite is pretty awkward to say. But the grammar would suggest they do say it by pronouncing each letter of the acronym separately.
- Zababa 4y agoI thought it was pronounced "es kew lite", S Q Lite instead of S Q L ite.
- boopmaster 4y agoI definitely prefer the pronunciation that sounds like a sugar-free beverage additive. "This Java tastes like Rust, would you parse me sum see'kwel-ite?"
- srcreigh 4y agoEither way, the name SQLite is not SQL-lite but SQL-ite (think graphite). SQLite is an element of SQLey stuff. Source: https://youtu.be/Jib2AmRb_rk https://youtu.be/Jib2AmRb_rk I'd link the exact timestamp, but then you might watch less than the entire talk, which would be unfortunate.
- teddyh 4y agoFor reference: The person in the video pronounces it Ess-Kew-Ell-Ait.
- teddyh 4y agoAn Ess-Kew-Lite.
- deleted 4y ago[deleted]
- newskfm 4y ago[flagged]
- nrjames 4y agoDuring some low-impact exploratory work on my local machine recently, I launched a Jupyter Lab notebook that connected to a SQLite db that was in a directory MS One Drive had claimed. The sync processes had nuked the db within five minutes. I was able to recreate the db easily, from csv files. Important lesson learned!
- psnehanshu 4y agoWhat do you mean by "nuked", did OneDrive delete it? It shouldn't delete unless it was deleted in the cloud by someone (or you).
- nrjames 4y agoNo, I think what happened is that OneDrive overwrote the local db with a version from the cloud that was several seconds older, corrupting everything. Or maybe it just overwrote the lock file? I'm not completely sure. Whatever it was, it appears that OneDrive replace a newer version of the db with an older or partial/corrupted one from a prior sync. I should mention that I was able to repeat the corrupting problem reliably. The problem disappeared when I moved to a working directory that was not monitored by OneDrive. I was setting up a very simple db from a few CSV files, for use in a data science exercise for a course. There was nothing interesting happening in my code at all-- maybe a simple table join sql query, at most.
- davepeck 4y agoI recently saw SQLite used (and written to) in a Docker container, with the database file on a mounted volume. That made me nervous! I don't know enough about how Docker implements volumes under the hood to know how likely (or not) it is to break filesystem behavior that SQLite depends on for its transactional guarantees. Perhaps someone here does?
- ktaylora 4y agoI've used mounted volumes in docker for high-traffic postgresql databases and they've held up nicely. Should test this with SQLite, but I imagine it would perform just fine.
- rfoo 4y agoVolumes are implemented with bind mount and it breaks almost nothing. No need to be nervous.
- andix 4y agoAs long as you’re not using docker for windows you should be fine. Docker desktop for windows uses a network share, to connect the Windows filesystems to the Linux docker system. Volumes are made exactly for holding databases. And also bind mounts should work without any issue in most configurations. But if there is some kind of network/virtualization between the physical disk and the container, you should investigate it in more detail.
- Too 4y agoHalf of the universe nowadays run on containers with volume mounts. No need to be worried about it being a container per se. What you should worry about though is the underlying volume driver and its storage type. Volumes can be simple bind mounts to your local disk, being more or less identical to no container. But volumes can also be attached straight to a network disk, where same dangers apply as if you would have mounted that network disk on host.
- martincmartin 4y ago"Unfortunately, most consumer-grade mass storage devices lie about syncing. Disk drives will report that content is safely on persistent media as soon as it reaches the track buffer and before actually being written to oxide. This makes the disk drives seem to operate faster (which is vitally important to the manufacturer so that they can show good benchmark numbers in trade magazines)." Is this also true of SSDs?
- vlovich123 4y agoProbably. Although it’s possible they solve the problems with a builtin battery backup (capacitor?) that can flush buffered data to flash (at least that’s how I’d do it)
- Tempest1981 4y agoFrom February: - I tested four NVMe SSDs from four vendors – half lose FLUSH’d data on power loss (twitter.com/xenadu02) - https://news.ycombinator.com/item?id=30419618 https://news.ycombinator.com/item?id=30419618
- martincmartin 4y agoThanks! If you follow the thread, they tried 8 more, all of which passed. So that's 10 passing / 2 failing. Still, it answers my question.
- Kenji 4y ago
- pixl97 4y agoIf you want to be sure, look for SSDs that have a power capacitor. These mostly exist in the enterprise devices.
- RedShift1 4y agoFor non-enterprise drives you still need to pay attention, sometimes power loss protection means that data at rest is not corrupted as opposed to an assurance that things in the write cache will make it to stable storage.
- vlovich123 4y agoDoes Linux yet have a config option to randomize the file descriptor number it hands out? So many times have I been bitten by silent use after close of these FDs.
- andai 4y agoDoesn't point 1.2 (backing up the file in the middle of a transaction) contradict the part above about how SQLite is resistant to power failures and simply rolls back the incomplete transaction? You'd just end up with a db outdated by 1 transaction, which is fine for a backup anyway.
- electroly 4y agoI'm not sure, but from the last sentence, it seems as though they're talking about what would happen if you backed it up but didn't also back up the journal or WAL. I think the bit about SQLite being resistant to power failures assumes that the journal or WAL are present. This is supported by the next section on "Deleting a hot journal" which says "SQLite must see the journal files in order to recover from a crash or power failure."
- account42 4y agoBesides the journal, live backups of the database file might not be atomic in which case the backed up copy could have content written after a fsync but missing content written before it which should not happen on power loss.
- layer8 4y agoDo I understand correctly that “2.2.1. Multiple copies of SQLite linked into the same application” is only a problem if more than one copy accesses the same database file?
- test123afds34 4y agointeresting!