3 ms·

> 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. Th

> 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.
- adrianmonk 7y ago
  Writing a copy and renaming works, but is it the only way, or is it just an approach that a lot of software uses because it's simple and it works? In other words, are there ways that might be more work to implement but also more efficient that would be suitable for a database? I agree the risk is pretty small in practice. Though copying with File Explorer isn't the use case I had in mind. It was automatic scheduled backups. Both Windows and macOS make it pretty easy to set these up with an external hard drive. And those could start running while you're using the computer and/or you could start using the computer while backups are running.
  - lolc 7y ago
    An append-only format should work fine with most backup software that copies files. A vacuum phase would still have to do an atomic rename.
  - GlitchMr 7y ago
    Back-up software can use NTFS/ReFS Shadow Copy in Windows. This shouldn't cause issues with SQLite. Copying files is only an issue for manual back-ups done without special software.
  - zlynx 7y ago
    Some backup software gets the modification time of the file, does the copy, then checks the modification time again. If it changed then it recopies the file. If it exceeds some number of attempts it gives up and reports an error for the file.
- Someone 7y ago
  The better way of ‘overwriting’ is to write the new file under a new name, and then atomically move it to where the old file was. If you have to overwrite (e.g. because of lack of disk space), the program that rewrites that file should open it in exclusive mode. A back-up tool should open files to backup in a mode that disallows others from writing to it, and retry that a few times, each time after waiting a few seconds. If that keeps failing, there’s a problem. Do you skip backing up the file or retry With a “it’s fine if others are reading” mode, with the risk of getting a hybrid of multiple versions? There is no fully correct answer there. And it gets worse. Not all programs write files in exclusive mode. Some might even open a file multiple times while rewriting it. Also, consistency may work cross-files, for example with Mac OS packages. And then, of course, there’s anti-virus tools. They often work below the file system, reading and writing files without the OS being aware of it. The reason that backup tools work relatively reliable and speedy at all mostly is that most programs that write to files will either regularly close them, or just append to them. Databases are the exception. They tend to keep files permanently open and regularly write to them. If you use SQLite in a ’normal’ application for user data, you should flush data after (about) every write (if you use it for caching, you can be a bit more loose, as its relatively fine to discard the database when it gets corrupted.
lolc 7y ago
Yes it is unsafe yet everybody does it. As long as you accept that some backups may be corrupted, it's fine. You'll still have older backups to go to. Of course, you have to notice the corruption in the first place! On a snapshotting system such as ZFS it should be fine because there won't be a race between updates and copying the WAL file and the DB file. (Haven't investigated it though.)
gwbas1c 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? I've written backup software: You only know if another process has an open file handle if you write your own driver. (Or integrate with 3rd party drivers, like ELDOS.) In most cases, writing a driver is overkill, so you have to accept that you might read a file mid-write. In general, the other process should lock the file, but there's no way to force every programmer in the world to write their software to be convenient for you.
btilly 7y ago
Let's turn this around. How would you avoid this? Suppose that you are copying the file front to back. While you are doing this, an update comes in that changes something in the front and back which refer to each other, while your copy is somewhere in the middle. The result is that you get a backup with a data structure in the back of the file referring to stuff in the front of the file that isn't there. Your backup copy is therefore corrupted. This problem is almost impossible to avoid. You just do the best that you can.

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.

adrianmonk 7y ago
Writing a copy and renaming works, but is it the only way, or is it just an approach that a lot of software uses because it's simple and it works? In other words, are there ways that might be more work to implement but also more efficient that would be suitable for a database? I agree the risk is pretty small in practice. Though copying with File Explorer isn't the use case I had in mind. It was automatic scheduled backups. Both Windows and macOS make it pretty easy to set these up with an external hard drive. And those could start running while you're using the computer and/or you could start using the computer while backups are running.
- lolc 7y ago
  An append-only format should work fine with most backup software that copies files. A vacuum phase would still have to do an atomic rename.
- GlitchMr 7y ago
  Back-up software can use NTFS/ReFS Shadow Copy in Windows. This shouldn't cause issues with SQLite. Copying files is only an issue for manual back-ups done without special software.
- zlynx 7y ago
  Some backup software gets the modification time of the file, does the copy, then checks the modification time again. If it changed then it recopies the file. If it exceeds some number of attempts it gives up and reports an error for the file.
Someone 7y ago
The better way of ‘overwriting’ is to write the new file under a new name, and then atomically move it to where the old file was. If you have to overwrite (e.g. because of lack of disk space), the program that rewrites that file should open it in exclusive mode. A back-up tool should open files to backup in a mode that disallows others from writing to it, and retry that a few times, each time after waiting a few seconds. If that keeps failing, there’s a problem. Do you skip backing up the file or retry With a “it’s fine if others are reading” mode, with the risk of getting a hybrid of multiple versions? There is no fully correct answer there. And it gets worse. Not all programs write files in exclusive mode. Some might even open a file multiple times while rewriting it. Also, consistency may work cross-files, for example with Mac OS packages. And then, of course, there’s anti-virus tools. They often work below the file system, reading and writing files without the OS being aware of it. The reason that backup tools work relatively reliable and speedy at all mostly is that most programs that write to files will either regularly close them, or just append to them. Databases are the exception. They tend to keep files permanently open and regularly write to them. If you use SQLite in a ’normal’ application for user data, you should flush data after (about) every write (if you use it for caching, you can be a bit more loose, as its relatively fine to discard the database when it gets corrupted.

lolc 7y ago

Yes it is unsafe yet everybody does it. As long as you accept that some backups may be corrupted, it's fine. You'll still have older backups to go to. Of course, you have to notice the corruption in the first place! On a snapshotting system such as ZFS it should be fine because there won't be a race between updates and copying the WAL file and the DB file. (Haven't investigated it though.)

gwbas1c 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? I've written backup software: You only know if another process has an open file handle if you write your own driver. (Or integrate with 3rd party drivers, like ELDOS.) In most cases, writing a driver is overkill, so you have to accept that you might read a file mid-write. In general, the other process should lock the file, but there's no way to force every programmer in the world to write their software to be convenient for you.

btilly 7y ago

Let's turn this around. How would you avoid this? Suppose that you are copying the file front to back. While you are doing this, an update comes in that changes something in the front and back which refer to each other, while your copy is somewhere in the middle. The result is that you get a backup with a data structure in the back of the file referring to stuff in the front of the file that isn't there. Your backup copy is therefore corrupted. This problem is almost impossible to avoid. You just do the best that you can.