SQLite Forum

Can copying an SQLite file while it's updated corrupt the origin?

Can copying an SQLite file while it's updated corrupt the origin?

(1) By Amichai (amichaih) on 2021-02-25 12:41:38 [link] [source]

Hi I'm writing an app that copies an SQLite DB while other processes write data to it.

While I'm aware that the copy may not be updated with the latest or even more - may be corrupted, I would like to ask if the original file may get corrupted due to the copy during other writing actions (again - copying only - on a windows OS via the copy command).



(2) By Warren Young (wyoung) on 2021-02-25 12:58:53 in reply to 1 [link] [source]

Instead of doing it wrong and then asking the consequences, why not just do it right, using either VACUUM INTO or the backup API?

(3) By Amichai (amichaih) on 2021-02-25 13:00:04 in reply to 2 [link] [source]

I wish I could but I have no access to it in that way - it's a different system that enables only copying the files

(4) By Warren Young (wyoung) on 2021-02-25 13:18:59 in reply to 3 [link] [source]

So have those in charge of the remote system create the backup, which you copy out.

(5) By Ryan Smith (cuz) on 2021-02-25 13:28:20 in reply to 1 [source]

Disregarding the obvious badness of the idea (which Warren already dealt with), no, there is no way to "break" the source DB by merely copying the data via the OS, so long as the copy method is a standard one and not some fancy method that tries to lock the file first, for instance.

There is however every chance of breaking it if ever anyone tries to Restore it from that copied backup data, since it will be likely corrupt - which makes one question the very point of doing it.

(6) By Amichai (amichaih) on 2021-02-25 13:28:46 in reply to 4 [link] [source]

Hi, thanks for your response. We are connecting to a different vendor's machine, so asking them stuff is not possible. The copy actions are rapid - we are copying the file to a different machine once in a few mins. My approach to this (as I have no other option) is this is an O.S issue (it's a Windows system) - and files should not get corrupted if copied - however it could be great to hear if someone thinks differently or saw such a situation. Thanks Amichai

(7) By Richard Hipp (drh) on 2021-02-25 13:42:56 in reply to 1 [link] [source]

As you describe it: No, copying a file cannot perturb the file.

However, if you try to copy the database file from a different thread within the same process, just copying the file can lead to corruption. When a separate thread calls close() on the file descriptor that is used to make copy, that will silently cancel all file locks on the database held by other threads within the same process. Cancelling those locks won't cause corruption by itself, but follow-on actions by the other threads that had locks cancelled out from under them could result in problems. This is the infamous Posix Advisor Locking design bug. Note that it is a bug in Posix, not in SQLite. SQLite works around the bug, but the work-around only functions if you access the database file using SQLite. This bug only applies on Unix-like operating systems - Windows is immune.

(8) By anonymous on 2022-02-23 08:30:24 in reply to 2 [link] [source]

Because it is a new feature not available in older versions.