How SQLite database can be survived from a power failure during extending the page count ?

(1) By anonymous on 2021-12-28 05:30:44 [link] [source]

I saw the SQLite does not has a feature to reserve disk space to save the database file. So, with the number of record being increased, the SQLite will increase the database file by a page size (default to 4096). So, my question is if the power failure happened during appending the page to the end of database file, would the database file or its parent directory being corrupted ? If it was true, how to prevent it ?

(2) By Larry Brasfield (larrybr) on 2021-12-28 05:41:39 in reply to 1 [link] [source]

Answering your questions in order: No. Not applicable.

The ACID promise you question is documented here. And within that doc, a link appears to how that is accomplished. If there is some part of the latter which you find unclear or unlikely to be true, please advise with specific details.

(3) By anonymous on 2021-12-28 06:15:13 in reply to 2 [link] [source]

Actually I'm concerning the directory that containing the SQLite database file.

I know the SQLite journal mode can promise any chance of corrupting the database file can be recovered. However, as my experience, I had an application that periodically writes files in different directories to a USB disk. The parent directory of those files sometimes would be corrupted and it was no longer be read by the application, or the directory name became unreadable characters. Also, sometimes there would be any other directory appeared but I had never created them. By using chkdsk utility tool, I can find several errors in that directory.

I guess that the directory structure would be corrupted if the power failure or the USB disk was disconnected while the file was growing its length.

This maybe not a SQLite question, because I'm not familiar with the implementation of the filesystem, I just would like to know if I use SQLite, will the ACID be able to protect the directory structure not being corrupted ?

(4) By Gunter Hick (gunter_hick) on 2021-12-28 06:53:19 in reply to 3 [link] [source]

SQLite will fsync() the directory whenever it creates or deletes a file or the file size changes.

quote 'SQLite does a "flush" or "fsync" operation at key points. SQLite assumes that the flush or fsync will not return until all pending write operations for the file that is being flushed have completed. We are told that the flush and fsync primitives are broken on some versions of Windows and Linux. This is unfortunate. It opens SQLite up to the possibility of database corruption following a power loss in the middle of a commit. However, there is nothing that SQLite can do to test for or remedy the situation. SQLite assumes that the operating system that it is running on works as advertised. If that is not quite the case, well then hopefully you will not lose power too often.' unquote

(5) By Larry Brasfield (larrybr) on 2021-12-28 08:14:06 in reply to 3 [link] [source]

The problems you mention are not specific to SQLite, nor can they be reasonably prevented by applications generally.

It is true that some filesystem designs, along with their implementations in (typically OS) software, are susceptible to corruption when the hardware suddenly ceases to operate. This is why journaling filesystems have been designed, so that filesystem metadata can be robustly kept or rolled back to a point of simple self-consistency when operations resume after a power-loss (or reset button-push or ~~asteroid impact~~hardware mishap.)

Your better bet to head off the corruption you report is to reformat your USB disk to use a more robust filesystem than is found on dime-store devices.

(6) By anonymous on 2021-12-28 14:15:38 in reply to 5 [link] [source]

Thanks for the explain. Currently I am using FAT32 which is not a journaling file system. In order to avoid file system being corrupted, is it possible to reserve a number of pages prior to insert records to this database? I think if we can do this, the file system area will never be modified because the fixed size database file.

(7) By Gunter Hick (gunter_hick) on 2021-12-28 14:46:07 in reply to 6 [link] [source]

You can use the truncate or the fallocate commands to preallocate blocks to a file, if this is supported by the file system on the device. Create the file with SQLite first, then adjust the size. SQLite will extend the file if it runs out of space.

(9) By Simon Slavin (slavin) on 2021-12-29 00:35:26 in reply to 6 [source]

SQLite does numerous things to make sure that it will lose data as seldom as possible, and yield a corrupted database even less often. Your suggested change would make sense in a simple setup (e.g. a text file) but SQLite keeps change data in a journal file until it is sure it has updated the database file. A journaling file system can help. But SQLite just by itself is unusually resistant to many forms of hardware failure.

(8) By Vadim Goncharov (nuclight) on 2021-12-28 19:02:44 in reply to 3 [link] [source]

If your problem is due to some files become inaccessible to SQLite due to directory corruption, you cant try to switch journaling mode from one to another, see https://www.sqlite.org/wal.html and links referenced there.