How to protect SQLite database from corruption

Question

I'm trying to figure out which is the safest strategy for protecting my (file-based) SQLite database from corruption (in this case, I'm working with Adobe Air, but this could apply to any webkit browser that uses SQLite, including Mobile Safari).

I'm thinking about creating a database connection, keeping it around for only maybe 5 or 10 seconds and then closing it if it hasn't been used during that period. My thinking is that should the machine crash or the application exit abnormally, chances are good that the file will already be closed and thus less likely to get corrupted. But I know that the more often you open and close a filebased DB, the more likely it is you'll have a critical error.

I'm sure I'm over-thinking this, but for my application it's critical that in the event of a system crash, the application can recover cleanly and quickly and that means I have to try to protect the DB as much as I can.

Does anyone know which strategy is likely to be safer?

Trundle

Answer 1

At the end of this document

File Locking And Concurrency In SQLite Version 3

There are a section named "6.0 How To Corrupt Your Database Files" that discuss corruption hipotetical curroptions problems in sqlite. "Things that can can go wrong".

Medor

That's a very interesting read, though it doesn't help me decide which method to use. I'm guessing that even by closing a database, you're not guaranteeing that data has been committed to disk since that's up to the OS and hardware, not SQLite; so the same risk of corruption exists even with a closed database. However, by closing the database you should also be flushing the journal file. So perhaps it is somewhat safer to close the file continually rather than keeping it open continuously. – Trundle

I had think about it... and as you said, "that's up to the OS and hardware" so... maybe the best aproach in one OS could not be the best aproach in other one. In addition, we may have performance issues if we're open and closing (and flushing) the file every few second (in mobile plataforms). So... I can't give you an answer :( – Medor

Just to follow up, because of race conditions I can't get rid of, I've had to abandon the continual open-and-close approach and keep the DB open permanently through the life of the application. To deal with corruption, I am making hot copies of the database. This technique also worries me a bit (making a copy of an open file), but because SQLite uses journaling and transactions, this should still be safe. Your link helped me understand SQLite much better, so I'm marking this answered. Thanks! – Trundle

Thanks to you! I've also learned some interesting things as a result of your question. – Medor

Answer 2

First do NOT use journal_mode=MEMORY or =OFF.

We can use these commands to reduce the probability of a corruption:

1. PRAGMA synchronous=FULL or PRAGMA synchronous=EXTRA
2. PRAGMA fullfsync=ON (only works on Mac OS X)

But they come with a cost of making the transactions slower. And even with them the db can become corrupted due to other causes like failure in the storage device or in the memory so we must be prepared to the problem.

We can make regular backups, but it has 2 disadvantages:

1. It copies the entire db file on each backup
2. We loose all the transactions that were made after the last backup

I have a customer that was used to make his backup on a pen-drive and the pen-drive was always kept plugged to the computer until a lightning came and destroyed the computer, including the pen-drive. All the data was lost. So the backup must be kept separated from the main computer.

A better alternative is to use replication. With it each transaction executed on the main db is replicated to the replicas.

For SQLite we can use litereplica. It supports Point-in-Time Recovery and I suggest to use it with replication because if some data is accidentally deleted from the main db it will be replicated. With PITR we can restore the db to a previous point in time.

Another important suggestion is to keep the replica in a separate device, and apart from each other. At least not in the same building.

Running PRAGMA integrity_check command once in a while is a good practice due to the fact that SQLite does not do it automatically and it continues writing to the db in some kinds of corruptions.

And if your db uses foreign keys you can do the same with PRAGMA foreign_key_check.

Terpsichorean