13 ms·

How to Corrupt a SQLite Database File

372 points by pcr910303 7y ago

Lex-2008 7y ago
Very interesting read! Although some points might sound too strange to include in this list (like "memory corruption" - in case of faulty memory, everything is in danger, not only database) - some worth keeping in mind (multiple hardlinks to the file, moving a single DB file around without accompanying *-wal file, etc)
- matsemann 7y ago
  > strange to include in this list (like "memory corruption" - in case of faulty memory, everything is in danger, not only database) But theoretically one could build something that handles up to X random bit flips, so it's maybe worth mentioning that sqlite doesn't handle this (even though probably no one else does as well)
  - Franciscouzo 7y ago
    Could you? You would also have to take into account that the instruction that make the program could also be randomly flipped
    - blattimwind 7y ago
      In most cases the size of the code is much smaller than the data handled, so for random errors (perfectly working DRAM has a bit error rate somewhere in the general vicinity of 10^-14/bit*hr) the distribution of errors is in accordance to that proportion.
- mytailorisrich 7y ago
  They are not describing cases of faulty memory but explicitly, and very aptly, warn you that since SQLite is a C library that runs in the same address space as the application code, if your application code corrupts memory (by way of buffer overrun, heap corruption, etc) it can impact SQLite's internals and in turn corrupts the SQLite database file.
andreareina 7y ago
Is open() on something other than /dev/stderr returning 2 a thing? Or is there something screwy that was causing it to get associated with the open database file?
- emilfihlman 7y ago
  The kernel gives you the lowest numbered fd available. If you close stderr, 2 becomes available and can be handed out. Also it's not exactly guaranteed that 0, 1 and 2 are the fd numbers for stdin, out and err on program start, but they are hard coded and standard. E: It's implementation defined, ie not guaranteed. Your platform may make guarantees about it, but it is not an actual standard.
  - matheusmoreira 7y ago
    http://man7.org/linux/man-pages/man3/stdin.3.html http://man7.org/linux/man-pages/man3/stdin.3.html > On program startup, the integer file descriptors associated with the streams stdin, stdout, and stderr are 0, 1, and 2, respectively.
    - emilfihlman 7y ago
      You must differentiate between a guarantee by an actual standard and platform specific documentation.
      - matheusmoreira 7y ago
        https://pubs.opengroup.org/onlinepubs/9699919799/functions/stdin.html https://pubs.opengroup.org/onlinepubs/9699919799/functions/s... > The following symbolic values in <unistd.h> define the file descriptors that shall be associated with the C-language stdin, stdout, and stderr when the application is started: > STDIN_FILENO Standard input value, stdin. Its value is 0. > STDOUT_FILENO Standard output value, stdout. Its value is 1. > STDERR_FILENO Standard error value, stderr. Its value is 2.
      - deleted 7y ago
bob1029 7y ago
I have found that corruption of SQLite files in practice is a very difficult thing to achieve. On Windows, the worst I've ever seen is a wall of "Database File is Locked" which was resolved with a rewrite of the DB access logic. After much experimentation, I have found that the happy path for SQLite is as follows: 1. Use a SQLite distribution that is compiled or otherwise configured for serialized threading mode (this is the default in most cases). 2. Use a single connection from a single process to access the SQLite database file. This connection can be safely shared between multiple threads with optional locking as required (due to the guarantees offered by #1 above). 3. Transactions are handled with locking in the application code (i.e. on the SQLiteConnection instance), not in the database. This is predominantly required for inserts with PK generation, but also applicable in cases where you don't want 2 different threads to interleave updates during a certain multi-step operation (e.g. debiting one account and crediting another). So far, we have found this to be the most performant and simple way to get business data in and out of a SQLite database file. The "one connection per operation" approach is NOT ideal for SQLite because there is an actual database file that needs a handle created/closed each time. One connection per makes sense when you are using a hosted database designed for this access model.
- catalogia 7y ago
  I've found WAL mode with one writer thread and as many reader threads as you want works pretty well.
  - bob1029 7y ago
    Yes, absolutely use WAL mode whenever feasible. Does wonders for performance.
- blattimwind 7y ago
  In WAL mode you can write from multiple threads without any problem, but you need to keep serializability in mind. Specifically, no concurrent write transactions are allowed. In other words, you can't have a transaction start at X, have another transaction start at X+1, issue DML and commit, and expect your original transaction from X being able to commit - it will never be able to. Some SQLite wrappers (notably, Python's) mess with the start and rollbacks of transactions and make this appear buggy.
kbumsik 7y ago
Can they be applied to other DBMSs? I mean, I don't think other DBMSs treat database files too differently from SQLite, don't they?
- pjc50 7y ago
  Most DBMS don't let you at the files directly - you speak to the DB over a socket. Because SQLite is in-process, bugs in the process that access random file descriptors can write over the SQLite files. (This is more ammunition for the idea that the real software isolation boundary on a desktop computer should not be "user", but "software author"!)
- Xophmeister 7y ago
  SQLite is serverless; server-based DBMSs are free to use other locking mechanisms, other than relying on filesystem primitives, to achieve concurrency.
- simias 7y ago
  Most of them would be possible in some variation, although typically with different results. For instance typically databases are accessed through a socket interface which uses the same pool of file descriptors as open(). So it's probably possible for a database connection to end up with fd 2 and then the write(2, ...) would also send garbage to the database. Although in this case it probably won't do much unless you're very unlucky because the message will almost certainly not end up being valid for the DB so it'll either ignore it, return an error or maybe drop the connection without corruption. So it would still be a nasty thing to debug but not quite as critical. More generally there's typically a much better isolation between database and process on traditional processes which makes it difficult for a misbehaving program to mess directly with the DB. And having a server program processing the transactions means that you don't have to rely so heavily on things like filesystem locking to achieve atomicity for instance. That being said buggy or limited filesystems can be a problem for any database, for instance to guarantee that a transaction has been really commited to disk in case of a power outage etc... See this for instance: https://wiki.postgresql.org/wiki/Fsync_Errors https://wiki.postgresql.org/wiki/Fsync_Errors https://www.postgresql.org/message-id/flat/CAMsr%2BYHh%2B5Oq4xziwwoEfhoTZgr07vdGG%2Bhu%3D1adXx59aTeaoQ%40mail.gmail.com https://www.postgresql.org/message-id/flat/CAMsr%2BYHh%2B5Oq...
justinator 7y ago
I must be getting old - an article with such a title would used to also be appended with, "For Fun, and Profit!"
- OJFord 7y ago
  These days that's not enough; most come with 6 Reasons, and often one of them is particularly Unbelievable.
- anon73044 7y ago
  I would have expected that if this were posted on milw0rm or a personal blog, but not on the actual SQLite info page.
  - justinator 7y ago
    I honestly feel it's more a sea change type of thing, https://mitpress.mit.edu/books/fun-and-profit https://mitpress.mit.edu/books/fun-and-profit
endorphone 7y ago
Related question - does SQLite checksum blocks (pages) of data? Of course the filesystem usually does, and the physical device itself does, but in the scenario where the OS somehow delivered corrupted data would SQLite at a minimum detect it?
- Liquid_Fire 7y ago
  > Of course the filesystem usually does Does it really? Certainly a few filesystems like ZFS have features like that, but AFAIK none of the "mainstream" ones do (the things actually running on billions of devices, like ext4, NTFS, etc)
- blattimwind 7y ago
  > does SQLite checksum blocks (pages) of data? No. Postgres has an off-by-default option for this. The journal does, though. > Of course the filesystem usually does Nope. Data checksumming is usually only employed by CoW file systems, because it requires data journaling for a regular file system, which generally isn't used or not supported at all.
wolco 7y ago
The most common by far. I/O error while obtaining a lock leads to corruption. Probably speaks to my drive/os choices.
cpuguy83 7y ago
In another life (2007) I worked for a company that does digital signage. We had about 8000 systems deployed across the US. The software on them relied heavily on SQLite. At some point we started seeing random corrupted SQLite databases. Trying to figure out what was causing this we realized our pc manufacturer was using the same sku but hardware inside could vary... and in particular the hard drive. Every system with a corrupt DB had a particular brand hard drive (WD), the ones that didn't fail was another (Seagate). We never really spent much more time on this other than to lock down the hardware config and replace all the hard drives in the failed systems with the "good" brand. Super interesting to see this. I'm assuming it was: Disk drives that do not honor sync requests We were of course using consumer grade equipment, because the company was pretty cheap, but also we had so many to deploy. I wonder how much this cost the company to fully resolve. Between systems not playing, having to send technicians, shipping systems overnight, time troubleshooting, etc.
- FpUser 7y ago
  In my other life (2000-2008 ish I think) I've also dealt with the large (tens of thousands) deployments all consumer grade as in your case. No SQLite though. Obviously we've had some hard drive failures. What is very interesting is that the results were exactly opposite. WD drives lived and Seagate drives failed. I am wondering if such discrepancy was caused by the usage patter.
  - winrid 7y ago
    The drives weren't actually failing, the setup failed due to how the WD drives work. If you reformatted them they'd be fine.
    - cpuguy83 7y ago
      Yep, exactly this. The drives were fine, only the database was getting corrupted.
    - FpUser 7y ago
      Oops, sorry was not careful reading your message
arminiusreturns 7y ago
I wonder if dqlite (https://github.com/canonical/dqlite https://github.com/canonical/dqlite) or rqlite (https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite) are enough to help prevent this.
- samatman 7y ago
  No, but they should be able to recover from it, which is almost as good.
adrianmonk 7y ago
> Systems that run automatic backups in the background might try to make a backup copy of an SQLite database file while it is in the middle of a transaction. The backup copy then might contain some old and some new content, and thus be corrupt. I am surprised by this! Is it really not entirely safe to take a backup while some software is running, has a SQLite database open, and might be writing to it? That limitation is fine for a big database that has someone knowledgeable administering it who will take care to follow any required backup procedures. But SQLite is often included in software that nontechnical end users will use without even knowing that there's a SQLite file. For example, Firefox and Google Chrome use it. This seems to mean that if a regular end user is browsing the web while backups run on their computer, it's possible they could be unlucky and get a bad backup that would corrupt the database if they had to restore it. Am I interpreting that right? If so, it suggests that many/most software projects that include SQLite are doing it in a slightly unsafe way. (It seems possible that the -journal and -wal files could protect against that, but this SQLite documentation only mentions that they protect you when transactions fail, which is a different case.)
- wongarsu 7y ago
  That depends on your backup solution. Naively copying or rsyncing files can lead to corruption for a large number of applications, including anything using SQLite. With OS or file system support you can make a snapshot of a point in time and copy that. Windows backup for example uses VSS (Volume Shadow Copies [1])) which should not suffer from this problem. On linux you can make ZFS clones or BTRFS snapshots, backup those and then delete them locally. 1: https://en.wikipedia.org/wiki/Shadow_Copy https://en.wikipedia.org/wiki/Shadow_Copy
- GlitchMr 7y ago
  Yes. However, keep in mind it's not just SQLite, but pretty much every program that has any kind of state. For instance, a program may save its state into a file every time it changes by truncating the file and then writing new contents. If an user were to copy the file after truncation, but before write, the user would end up with an useless backup. That said, this is rather unlikely to happen in practice. Programs usually do stuff (including saving files) when they are interacted with, so a backup copied with File Explorer has a very high probability of working, even when the application is running. Programs can deal with the issue by performing atomic full writes by performing renames, but most programs don't bother to. SQLite cannot do that, because it cannot afford doing full writes as databases tend to store a lot of data.
logicallee 7y ago
This section was particularly interesting (I add my own thoughts below it): >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). And in fairness, the lie normally causes no harm, as long as there is no power loss or hard reset prior to the track buffer actually being written to oxide. But if a power loss or hard reset does occur, and if that results in content that was written after a sync reaching oxide while content written before the sync is still in a track buffer, then database corruption can occur. >USB flash memory sticks seem to be especially pernicious liars regarding sync requests. One can easily see this by committing a large transaction to an SQLite database on a USB memory stick. The COMMIT command will return relatively quickly, indicating that the memory stick has told the operating system and the operating system has told SQLite that all content is safely in persistent storage, and yet the LED on the end of the memory stick will continue flashing for several more seconds. Pulling out the memory stick while the LED is still flashing will frequently result in database corruption. ----- If you were a human rather than sqlite you would stop believing that a sync has occurred immediately, trust it a little bit after a few seconds, and if it's been a minute since you yourself last touched the file, you would think "surely it has been written by now". So it seems by tracking wall time, the library could do better than have to rely on the drive, and could be more on guard against lying drives.
- fencepost 7y ago
  I'd also wonder about how old this part is and whether it's partially based on Windows. USB behavior on Windows used to be optimized for speed and would do caching (hence the need to eject flash drives before removal) but in one of the Win10 updates it was changed to safe removal which basically disables write caching on removable devices. So, at least on Windows, there was absolutely a time where the OS would report completion before things were actually written.
- kardos 7y ago
  > So it seems by tracking wall time, the library could do better than have to rely on the drive, and could be more on guard against lying drives. Can you elaborate about tracking wall time --- how would you determine the real sync timing? Picking an arbitrary large delay (a minute) would trash the write performance
tgsovlerkhgsel 7y ago
> Backup or restore while a transaction is active Is there any information whether taking a consistent snapshot (that contains both the DB file and journals) is safe?
- egdod 7y ago
  Seems like that would have the same problems if you do it while transactions are ongoing.
  - tgsovlerkhgsel 7y ago
    Given that the DB is designed to survive power outages (even during transactions), I don't really understand how that would be the case.
  - dunham 7y ago
    I believe they are subtly different. If you copy while transactions are ongoing, you're reading different parts of a file at different points in time. For example, the beginning of the sqlite3 has already been copied, but by the time you get to the log file, blocks have been committed back into the beginning of the sqlite file. If you take a consistent snapshot of the filesystem first, I would think that would be the same as a crash in the middle of a transaction. Things should be in a consistent state that the recovery code at startup can handle.
- bob1029 7y ago
  If you are using WAL, I feel volume-level snapshots are an acceptable approach.
- deleted 7y ago
  [deleted]
- pstuart 7y ago
  It's recommended to use the backup api vs a filesystem copy: https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html
tracker1 7y ago
Using a mounted volume in docker for windows/mac desktop and inside container to edit the same database. Don't do this btw, the FS sync in docker for windows/mac is really slow and prone to causing corruption and errors if you do.
umvi 7y ago
Somewhat unrelated, I feel like the stars aligned to prevent my company from using SQLite. We use a lot of CSV files in our embedded devices. I personally hate CSV and advocated for SQLite. However, our CSVs are 1 writer/many readers, and the only way to do that in SQLite is to change the mode to WAL. However, apparently WAL doesn't work on JFFS2 filesystems :(... I'm going to give it another shot on our next-gen product which uses ubifs
silvestrov 7y ago
> Continuing to use a file descriptor after it has been closed It would be nice if file descriptors on 64-bit platforms used tagging so the full value could never be reused. E.g. lower 32 bits is the normal fd and the 32-bit is a serial number (or random value). When using the fd the kernel can use the lower 32-bit as index into the table and check the upper 32 bits are correct.
- fanf2 7y ago
  Sadly that isn't allowed by POSIX https://pubs.opengroup.org/onlinepubs/9699919799.2018edition/functions/V2_chap02.html#tag_15_14 https://pubs.opengroup.org/onlinepubs/9699919799.2018edition... > 2.14. File Descriptor Allocation > All functions that open one or more file descriptors shall, unless specified otherwise, atomically allocate the lowest numbered available file descriptor
  - codetrotter 7y ago
    Would badness ensue if we allowed ourselves to define all previously used fds as “unavailable”, and thus conforming to the rules as stated when we increment our serial number before handing out the next fd? If yes, what sorts of badness and why?
    - samatman 7y ago
      It strikes me that you'd use up STDIN, STDOUT, and STDERR pretty fast that way. One could make an unprincipled exception for them, of course. And SQLITE would still need the special-casing to prevent using low-numbered fds. But this would prevent other problems and is worth considering.
      - mras0 7y ago
        Didn't see your reply before commenting, but it gave me a thought: We really only need a "local" solution. If there were a way of requesting fd's that weren't required to be the lowest available ones (perhaps even 64-bit ones with a process unique uppper part), that'd probably be good enough here. Of course we're still sharing address space with buggy code, so maybe it's not worth it.
dicytea 7y ago
Any good introduction to sqlite3 for a Python user?
- kccqzy 7y ago
  Read the sqlite SQL documentation. Then read the built-in Python library documentation. Or read the SQLAlchemy documentation, for a different way to interact with it. Build something. https://docs.python.org/3/library/sqlite3.html https://docs.python.org/3/library/sqlite3.html https://docs.sqlalchemy.org/en/13/core/tutorial.html https://docs.sqlalchemy.org/en/13/core/tutorial.html https://www.sqlite.org/lang.html https://www.sqlite.org/lang.html
drderidder 7y ago
It doesn't get more than a passing mention, but section 2.1 is worth noting. Don't try to use sqlite files on an NFS mounted drive.
laurent123456 7y ago
2009 (at least) + previous discussions: https://hn.algolia.com/?q=How+to+Corrupt+an+SQLite+Database+File https://hn.algolia.com/?q=How+to+Corrupt+an+SQLite+Database+...
Const-me 7y ago
> Backup or restore while a transaction is active I wonder does that apply to Windows? Well-designed Windows backup software uses shadow copy NTFS feature. Shadow copy creates a snapshot of the complete volume when backup starts. All writes happening after that won’t be included in the shadow copy, so the backup software should never read some old and some new content. Does it mean when using VSS on Windows, SQLite databases will be backed up just fine? Or am I missing something?
- DenisM 7y ago
  You're probably right. Taking a VSS snapshot is identical to pulling the power plug and imaging the drive. If SQLite hand handle the latter it should be able to handle the former. Another thing that should work well is an AWS EC2 EBS snapshot as Amazon promises a consistent point in time copy.
jhatemyjob 7y ago
> an SQLite Does this settle it, then? This implies it's pronounced ess-queue-lite. I personally prefer the sequel-lite pronunciation, but if the developers use the former, then I guess I'll do that.
- threatripper 7y ago
  Are you from the 70s and still use SEQUEL? Then you pronounce it sequel. If instead you are a modern hu-man who uses SQL, you pronounce it ess-qu-ell.
- tingletech 7y ago
  I thought it was ess-que-ell-ite yea, seems so https://english.stackexchange.com/questions/431329/what-is-the-correct-pronunciation-of-sqlite https://english.stackexchange.com/questions/431329/what-is-t...
shapeshifter334 7y ago
Leave it to the HN comment section for talking about databases instead of relationships and life decisions.
abinaya_rl 7y ago
Thanks for the post, We have been using SQLite with Django for the past one year and I'm so happy that it exists. I even store it in the git repo and and it makes backups so easy.
abinaya_b 7y ago
Thanks for the post, We have been using SQLite with Django for the past one year and I'm so happy that it exists. I even store it in the git repo and and it makes backups so easy.
poooood 7y ago
Had a team mate use “dos2unix” on everything in a directory and that defo corrupted the database.

372 points by pcr910303 7y ago

Lex-2008 7y ago

Very interesting read! Although some points might sound too strange to include in this list (like "memory corruption" - in case of faulty memory, everything is in danger, not only database) - some worth keeping in mind (multiple hardlinks to the file, moving a single DB file around without accompanying *-wal file, etc)

matsemann 7y ago
> strange to include in this list (like "memory corruption" - in case of faulty memory, everything is in danger, not only database) But theoretically one could build something that handles up to X random bit flips, so it's maybe worth mentioning that sqlite doesn't handle this (even though probably no one else does as well)
- Franciscouzo 7y ago
  Could you? You would also have to take into account that the instruction that make the program could also be randomly flipped
  - blattimwind 7y ago
    In most cases the size of the code is much smaller than the data handled, so for random errors (perfectly working DRAM has a bit error rate somewhere in the general vicinity of 10^-14/bit*hr) the distribution of errors is in accordance to that proportion.
mytailorisrich 7y ago
They are not describing cases of faulty memory but explicitly, and very aptly, warn you that since SQLite is a C library that runs in the same address space as the application code, if your application code corrupts memory (by way of buffer overrun, heap corruption, etc) it can impact SQLite's internals and in turn corrupts the SQLite database file.

andreareina 7y ago

Is open() on something other than /dev/stderr returning 2 a thing? Or is there something screwy that was causing it to get associated with the open database file?

emilfihlman 7y ago
The kernel gives you the lowest numbered fd available. If you close stderr, 2 becomes available and can be handed out. Also it's not exactly guaranteed that 0, 1 and 2 are the fd numbers for stdin, out and err on program start, but they are hard coded and standard. E: It's implementation defined, ie not guaranteed. Your platform may make guarantees about it, but it is not an actual standard.
- matheusmoreira 7y ago
  http://man7.org/linux/man-pages/man3/stdin.3.html http://man7.org/linux/man-pages/man3/stdin.3.html > On program startup, the integer file descriptors associated with the streams stdin, stdout, and stderr are 0, 1, and 2, respectively.
  - emilfihlman 7y ago
    You must differentiate between a guarantee by an actual standard and platform specific documentation.
    - matheusmoreira 7y ago
      https://pubs.opengroup.org/onlinepubs/9699919799/functions/stdin.html https://pubs.opengroup.org/onlinepubs/9699919799/functions/s... > The following symbolic values in <unistd.h> define the file descriptors that shall be associated with the C-language stdin, stdout, and stderr when the application is started: > STDIN_FILENO Standard input value, stdin. Its value is 0. > STDOUT_FILENO Standard output value, stdout. Its value is 1. > STDERR_FILENO Standard error value, stderr. Its value is 2.
    - deleted 7y ago

bob1029 7y ago

I have found that corruption of SQLite files in practice is a very difficult thing to achieve. On Windows, the worst I've ever seen is a wall of "Database File is Locked" which was resolved with a rewrite of the DB access logic. After much experimentation, I have found that the happy path for SQLite is as follows: 1. Use a SQLite distribution that is compiled or otherwise configured for serialized threading mode (this is the default in most cases). 2. Use a single connection from a single process to access the SQLite database file. This connection can be safely shared between multiple threads with optional locking as required (due to the guarantees offered by #1 above). 3. Transactions are handled with locking in the application code (i.e. on the SQLiteConnection instance), not in the database. This is predominantly required for inserts with PK generation, but also applicable in cases where you don't want 2 different threads to interleave updates during a certain multi-step operation (e.g. debiting one account and crediting another). So far, we have found this to be the most performant and simple way to get business data in and out of a SQLite database file. The "one connection per operation" approach is NOT ideal for SQLite because there is an actual database file that needs a handle created/closed each time. One connection per makes sense when you are using a hosted database designed for this access model.

catalogia 7y ago
I've found WAL mode with one writer thread and as many reader threads as you want works pretty well.
- bob1029 7y ago
  Yes, absolutely use WAL mode whenever feasible. Does wonders for performance.
blattimwind 7y ago
In WAL mode you can write from multiple threads without any problem, but you need to keep serializability in mind. Specifically, no concurrent write transactions are allowed. In other words, you can't have a transaction start at X, have another transaction start at X+1, issue DML and commit, and expect your original transaction from X being able to commit - it will never be able to. Some SQLite wrappers (notably, Python's) mess with the start and rollbacks of transactions and make this appear buggy.

kbumsik 7y ago

Can they be applied to other DBMSs? I mean, I don't think other DBMSs treat database files too differently from SQLite, don't they?

pjc50 7y ago
Most DBMS don't let you at the files directly - you speak to the DB over a socket. Because SQLite is in-process, bugs in the process that access random file descriptors can write over the SQLite files. (This is more ammunition for the idea that the real software isolation boundary on a desktop computer should not be "user", but "software author"!)
Xophmeister 7y ago
SQLite is serverless; server-based DBMSs are free to use other locking mechanisms, other than relying on filesystem primitives, to achieve concurrency.
simias 7y ago
Most of them would be possible in some variation, although typically with different results. For instance typically databases are accessed through a socket interface which uses the same pool of file descriptors as open(). So it's probably possible for a database connection to end up with fd 2 and then the write(2, ...) would also send garbage to the database. Although in this case it probably won't do much unless you're very unlucky because the message will almost certainly not end up being valid for the DB so it'll either ignore it, return an error or maybe drop the connection without corruption. So it would still be a nasty thing to debug but not quite as critical. More generally there's typically a much better isolation between database and process on traditional processes which makes it difficult for a misbehaving program to mess directly with the DB. And having a server program processing the transactions means that you don't have to rely so heavily on things like filesystem locking to achieve atomicity for instance. That being said buggy or limited filesystems can be a problem for any database, for instance to guarantee that a transaction has been really commited to disk in case of a power outage etc... See this for instance: https://wiki.postgresql.org/wiki/Fsync_Errors https://wiki.postgresql.org/wiki/Fsync_Errors https://www.postgresql.org/message-id/flat/CAMsr%2BYHh%2B5Oq4xziwwoEfhoTZgr07vdGG%2Bhu%3D1adXx59aTeaoQ%40mail.gmail.com https://www.postgresql.org/message-id/flat/CAMsr%2BYHh%2B5Oq...

justinator 7y ago

I must be getting old - an article with such a title would used to also be appended with, "For Fun, and Profit!"

OJFord 7y ago
These days that's not enough; most come with 6 Reasons, and often one of them is particularly Unbelievable.
anon73044 7y ago
I would have expected that if this were posted on milw0rm or a personal blog, but not on the actual SQLite info page.
- justinator 7y ago
  I honestly feel it's more a sea change type of thing, https://mitpress.mit.edu/books/fun-and-profit https://mitpress.mit.edu/books/fun-and-profit

endorphone 7y ago

Related question - does SQLite checksum blocks (pages) of data? Of course the filesystem usually does, and the physical device itself does, but in the scenario where the OS somehow delivered corrupted data would SQLite at a minimum detect it?

Liquid_Fire 7y ago
> Of course the filesystem usually does Does it really? Certainly a few filesystems like ZFS have features like that, but AFAIK none of the "mainstream" ones do (the things actually running on billions of devices, like ext4, NTFS, etc)
blattimwind 7y ago
> does SQLite checksum blocks (pages) of data? No. Postgres has an off-by-default option for this. The journal does, though. > Of course the filesystem usually does Nope. Data checksumming is usually only employed by CoW file systems, because it requires data journaling for a regular file system, which generally isn't used or not supported at all.

wolco 7y ago

The most common by far. I/O error while obtaining a lock leads to corruption. Probably speaks to my drive/os choices.

cpuguy83 7y ago

In another life (2007) I worked for a company that does digital signage. We had about 8000 systems deployed across the US. The software on them relied heavily on SQLite. At some point we started seeing random corrupted SQLite databases. Trying to figure out what was causing this we realized our pc manufacturer was using the same sku but hardware inside could vary... and in particular the hard drive. Every system with a corrupt DB had a particular brand hard drive (WD), the ones that didn't fail was another (Seagate). We never really spent much more time on this other than to lock down the hardware config and replace all the hard drives in the failed systems with the "good" brand. Super interesting to see this. I'm assuming it was: Disk drives that do not honor sync requests We were of course using consumer grade equipment, because the company was pretty cheap, but also we had so many to deploy. I wonder how much this cost the company to fully resolve. Between systems not playing, having to send technicians, shipping systems overnight, time troubleshooting, etc.

FpUser 7y ago
In my other life (2000-2008 ish I think) I've also dealt with the large (tens of thousands) deployments all consumer grade as in your case. No SQLite though. Obviously we've had some hard drive failures. What is very interesting is that the results were exactly opposite. WD drives lived and Seagate drives failed. I am wondering if such discrepancy was caused by the usage patter.
- winrid 7y ago
  The drives weren't actually failing, the setup failed due to how the WD drives work. If you reformatted them they'd be fine.
  - cpuguy83 7y ago
    Yep, exactly this. The drives were fine, only the database was getting corrupted.
  - FpUser 7y ago
    Oops, sorry was not careful reading your message

arminiusreturns 7y ago

I wonder if dqlite (https://github.com/canonical/dqlite https://github.com/canonical/dqlite) or rqlite (https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite) are enough to help prevent this.

samatman 7y ago
No, but they should be able to recover from it, which is almost as good.

adrianmonk 7y ago

> Systems that run automatic backups in the background might try to make a backup copy of an SQLite database file while it is in the middle of a transaction. The backup copy then might contain some old and some new content, and thus be corrupt. I am surprised by this! Is it really not entirely safe to take a backup while some software is running, has a SQLite database open, and might be writing to it? That limitation is fine for a big database that has someone knowledgeable administering it who will take care to follow any required backup procedures. But SQLite is often included in software that nontechnical end users will use without even knowing that there's a SQLite file. For example, Firefox and Google Chrome use it. This seems to mean that if a regular end user is browsing the web while backups run on their computer, it's possible they could be unlucky and get a bad backup that would corrupt the database if they had to restore it. Am I interpreting that right? If so, it suggests that many/most software projects that include SQLite are doing it in a slightly unsafe way. (It seems possible that the -journal and -wal files could protect against that, but this SQLite documentation only mentions that they protect you when transactions fail, which is a different case.)

wongarsu 7y ago
That depends on your backup solution. Naively copying or rsyncing files can lead to corruption for a large number of applications, including anything using SQLite. With OS or file system support you can make a snapshot of a point in time and copy that. Windows backup for example uses VSS (Volume Shadow Copies [1])) which should not suffer from this problem. On linux you can make ZFS clones or BTRFS snapshots, backup those and then delete them locally. 1: https://en.wikipedia.org/wiki/Shadow_Copy https://en.wikipedia.org/wiki/Shadow_Copy
GlitchMr 7y ago
Yes. However, keep in mind it's not just SQLite, but pretty much every program that has any kind of state. For instance, a program may save its state into a file every time it changes by truncating the file and then writing new contents. If an user were to copy the file after truncation, but before write, the user would end up with an useless backup. That said, this is rather unlikely to happen in practice. Programs usually do stuff (including saving files) when they are interacted with, so a backup copied with File Explorer has a very high probability of working, even when the application is running. Programs can deal with the issue by performing atomic full writes by performing renames, but most programs don't bother to. SQLite cannot do that, because it cannot afford doing full writes as databases tend to store a lot of data.

logicallee 7y ago

This section was particularly interesting (I add my own thoughts below it): >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). And in fairness, the lie normally causes no harm, as long as there is no power loss or hard reset prior to the track buffer actually being written to oxide. But if a power loss or hard reset does occur, and if that results in content that was written after a sync reaching oxide while content written before the sync is still in a track buffer, then database corruption can occur. >USB flash memory sticks seem to be especially pernicious liars regarding sync requests. One can easily see this by committing a large transaction to an SQLite database on a USB memory stick. The COMMIT command will return relatively quickly, indicating that the memory stick has told the operating system and the operating system has told SQLite that all content is safely in persistent storage, and yet the LED on the end of the memory stick will continue flashing for several more seconds. Pulling out the memory stick while the LED is still flashing will frequently result in database corruption. ----- If you were a human rather than sqlite you would stop believing that a sync has occurred immediately, trust it a little bit after a few seconds, and if it's been a minute since you yourself last touched the file, you would think "surely it has been written by now". So it seems by tracking wall time, the library could do better than have to rely on the drive, and could be more on guard against lying drives.

fencepost 7y ago
I'd also wonder about how old this part is and whether it's partially based on Windows. USB behavior on Windows used to be optimized for speed and would do caching (hence the need to eject flash drives before removal) but in one of the Win10 updates it was changed to safe removal which basically disables write caching on removable devices. So, at least on Windows, there was absolutely a time where the OS would report completion before things were actually written.
kardos 7y ago
> So it seems by tracking wall time, the library could do better than have to rely on the drive, and could be more on guard against lying drives. Can you elaborate about tracking wall time --- how would you determine the real sync timing? Picking an arbitrary large delay (a minute) would trash the write performance

tgsovlerkhgsel 7y ago

> Backup or restore while a transaction is active Is there any information whether taking a consistent snapshot (that contains both the DB file and journals) is safe?

egdod 7y ago
Seems like that would have the same problems if you do it while transactions are ongoing.
- tgsovlerkhgsel 7y ago
  Given that the DB is designed to survive power outages (even during transactions), I don't really understand how that would be the case.
- dunham 7y ago
  I believe they are subtly different. If you copy while transactions are ongoing, you're reading different parts of a file at different points in time. For example, the beginning of the sqlite3 has already been copied, but by the time you get to the log file, blocks have been committed back into the beginning of the sqlite file. If you take a consistent snapshot of the filesystem first, I would think that would be the same as a crash in the middle of a transaction. Things should be in a consistent state that the recovery code at startup can handle.
bob1029 7y ago
If you are using WAL, I feel volume-level snapshots are an acceptable approach.
deleted 7y ago
[deleted]
pstuart 7y ago
It's recommended to use the backup api vs a filesystem copy: https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html

tracker1 7y ago

Using a mounted volume in docker for windows/mac desktop and inside container to edit the same database. Don't do this btw, the FS sync in docker for windows/mac is really slow and prone to causing corruption and errors if you do.

umvi 7y ago

Somewhat unrelated, I feel like the stars aligned to prevent my company from using SQLite. We use a lot of CSV files in our embedded devices. I personally hate CSV and advocated for SQLite. However, our CSVs are 1 writer/many readers, and the only way to do that in SQLite is to change the mode to WAL. However, apparently WAL doesn't work on JFFS2 filesystems :(... I'm going to give it another shot on our next-gen product which uses ubifs

silvestrov 7y ago

> Continuing to use a file descriptor after it has been closed It would be nice if file descriptors on 64-bit platforms used tagging so the full value could never be reused. E.g. lower 32 bits is the normal fd and the 32-bit is a serial number (or random value). When using the fd the kernel can use the lower 32-bit as index into the table and check the upper 32 bits are correct.

fanf2 7y ago
Sadly that isn't allowed by POSIX https://pubs.opengroup.org/onlinepubs/9699919799.2018edition/functions/V2_chap02.html#tag_15_14 https://pubs.opengroup.org/onlinepubs/9699919799.2018edition... > 2.14. File Descriptor Allocation > All functions that open one or more file descriptors shall, unless specified otherwise, atomically allocate the lowest numbered available file descriptor
- codetrotter 7y ago
  Would badness ensue if we allowed ourselves to define all previously used fds as “unavailable”, and thus conforming to the rules as stated when we increment our serial number before handing out the next fd? If yes, what sorts of badness and why?
  - samatman 7y ago
    It strikes me that you'd use up STDIN, STDOUT, and STDERR pretty fast that way. One could make an unprincipled exception for them, of course. And SQLITE would still need the special-casing to prevent using low-numbered fds. But this would prevent other problems and is worth considering.
    - mras0 7y ago
      Didn't see your reply before commenting, but it gave me a thought: We really only need a "local" solution. If there were a way of requesting fd's that weren't required to be the lowest available ones (perhaps even 64-bit ones with a process unique uppper part), that'd probably be good enough here. Of course we're still sharing address space with buggy code, so maybe it's not worth it.

dicytea 7y ago

Any good introduction to sqlite3 for a Python user?

kccqzy 7y ago
Read the sqlite SQL documentation. Then read the built-in Python library documentation. Or read the SQLAlchemy documentation, for a different way to interact with it. Build something. https://docs.python.org/3/library/sqlite3.html https://docs.python.org/3/library/sqlite3.html https://docs.sqlalchemy.org/en/13/core/tutorial.html https://docs.sqlalchemy.org/en/13/core/tutorial.html https://www.sqlite.org/lang.html https://www.sqlite.org/lang.html

drderidder 7y ago

It doesn't get more than a passing mention, but section 2.1 is worth noting. Don't try to use sqlite files on an NFS mounted drive.

laurent123456 7y ago

2009 (at least) + previous discussions: https://hn.algolia.com/?q=How+to+Corrupt+an+SQLite+Database+File https://hn.algolia.com/?q=How+to+Corrupt+an+SQLite+Database+...

Const-me 7y ago

> Backup or restore while a transaction is active I wonder does that apply to Windows? Well-designed Windows backup software uses shadow copy NTFS feature. Shadow copy creates a snapshot of the complete volume when backup starts. All writes happening after that won’t be included in the shadow copy, so the backup software should never read some old and some new content. Does it mean when using VSS on Windows, SQLite databases will be backed up just fine? Or am I missing something?

DenisM 7y ago
You're probably right. Taking a VSS snapshot is identical to pulling the power plug and imaging the drive. If SQLite hand handle the latter it should be able to handle the former. Another thing that should work well is an AWS EC2 EBS snapshot as Amazon promises a consistent point in time copy.

jhatemyjob 7y ago

> an SQLite Does this settle it, then? This implies it's pronounced ess-queue-lite. I personally prefer the sequel-lite pronunciation, but if the developers use the former, then I guess I'll do that.

threatripper 7y ago
Are you from the 70s and still use SEQUEL? Then you pronounce it sequel. If instead you are a modern hu-man who uses SQL, you pronounce it ess-qu-ell.
tingletech 7y ago
I thought it was ess-que-ell-ite yea, seems so https://english.stackexchange.com/questions/431329/what-is-the-correct-pronunciation-of-sqlite https://english.stackexchange.com/questions/431329/what-is-t...

shapeshifter334 7y ago

Leave it to the HN comment section for talking about databases instead of relationships and life decisions.

abinaya_rl 7y ago

Thanks for the post, We have been using SQLite with Django for the past one year and I'm so happy that it exists. I even store it in the git repo and and it makes backups so easy.

abinaya_b 7y ago

Thanks for the post, We have been using SQLite with Django for the past one year and I'm so happy that it exists. I even store it in the git repo and and it makes backups so easy.

poooood 7y ago

Had a team mate use “dos2unix” on everything in a directory and that defo corrupted the database.