Periodically check SQLite DB integrity

SQLite is a pretty robust database, but, being an embedded database it is more susceptible to corruption than a traditional standalone database.

Recently I encountered a corrupt SQLite database. Not sure how the database got corrupt in the first place, however, it was quiet interesting to find out that even though the database was corrupt some of the queries executed without any error, but, returned wrong results, while some queries failed with “database disk image is malformed” error.

So database is corrupt, but, how to find out what is wrong and how to fix it?

SQLite’s PRAGMA integrity_check can be used to check the integrity of a sqlite database.

This pragma does an integrity check of the entire database. It looks for out-of-order records, missing pages, malformed records, and corrupt indices. If any problems are found, then strings are returned (as multiple rows with a single column per row) which describe the problems. At most integer errors will be reported before the analysis quits. The default value for integer is 100. If no errors are found, a single row with the value “ok” is returned.


Luckily, my database only had a few corrupt indexes. reindexing the corrupt indexes fixed the problem.

The moral of the story:

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s