Prevent +Database File Corruption

Avoid Corrupting +your Database Files

One of the key benefits of using an atomic +and durable database engine is that you can be responsibly confident that +the database will not be corrupted by application crashes or power failures. +SQLite is very resistant to database corruption but it is possible to corrupt +a database. This section will describe all of the known techniques for corrupting +a SQLite database so that you can make sure you avoid them.

The following +database corruption situations will be discussed here:

Hardware failure -- +beyond the control of SQLite
System Crash -- when +rollback journal exists
post crash application +recovery
hot journal name conflict

A SQLite database is just an ordinary file in the file system. If +the database is placed in a publicly accessible location then any process +can open that file and write nonsense into the middle of it, corrupting the +database. Similarly, an operating system malfunction or a hardware fault can +cause invalid data to be written into the database file. Both of these issues +are beyond the control of SQLite or of application developers. We only mention +them here for completeness.

After a power loss or system crash and +subsequent system reboot, a rollback journal file will be found in the same +directory as the original database. The presence of this rollback journal +file is the signal to subsequent users that the database is in an inconsistent +state and needs to be restored (by playing back the journal) before it is +used. A rollback journal file that is left over after a crash is called a +“hot journal”.

If after a post-crash reboot some kind application +recovery occurs which deletes, renames, or moves a hot journal, then SQLite +will have no way of knowing that a hot journal existed. It will not know that +the database is in an inconsistent state and will have no way to restore it. +Deleting or renaming a hot journal will result in a corrupted database nearly +every time.

The name of the hot journal is related to the name of +the original database file. If the database file is renamed this means that +SQLite will not see the hot journal based on the original name, no database +recovery will be undertaken and the database will become corrupt.

These +are all of the known ways for corrupting a SQLite database file, and as you +can see, none of these ways are easy to achieve. SQLite databases have proven +to be remarkably reliable and trouble-free. By avoiding the above situations +you can ensure that your databases will be safe and intact even after system +crashes and untimely power failures.