Prevent Database File Corruption

This guide describes how databases might be corrupted.

Introduction

Although SQLite is very resistant to database corruption, thanks to the atomic features, it is still possible for a database file to become corrupt. By understanding the known ways of corrupting a SQLite database, you can make sure you avoid them.

Intended audience:

This document is intended to be used by Symbian platform licensees and third party application developers.

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.

Related information
SQLite