Sqlite: Database is Locked error and unlocking the database

May 26 2011

On a few occasions, my Drupal sites that use SQLite have gotten into various states where Drupal was locked out with an error that reads something like this:

PDOException: SQLSTATE[HY000]: General error: 5 database is locked

Usually this happens after a server crash of some sort. As I understand it, the issue is that a lock was acquired by the process that died, but the lock was never removed. Unfortunately, I have found no obvious way to find and remove the lock. But there is a way to eliminate all of the locks.

Before trying this, make sure you're not just dealing with a file permission issue. Your DB file needs to be accessible by your user ID (and you webserver's user ID).

Removing Sqlite Database Locks Using `.backup`

The general strategy here is to create a backup of the database, where the backup does not have the locks, and then swapping out the database with the backup copy. Here's how to do this.

Use the command line sqlite3 program to do the following (assuming the sqlite database file is named .ht.sqlite, as it is by default in Drupal):

$ sqlite3 .ht.sqlite
sqlite> .backup main backup.sqlite
sqlite> .exit

Now you should have a file called backup.sqlite in the same directory. Next, you need to move your old database, and replace it with the backup copy. As far as I understand it, the backup copy does not have the locks, so this swap will essentially eliminate the problem.

$ mv .ht.sqlite old.sqlite
$ mv backup.sqlite .ht.sqlite

At this point you should be able to once again access the database with the sqlite3 command line client. You may also need to chown or chmod the file in order to grant access to the webserver again. But that's all there is to it.

Once you have tested and verified that your database is once again allowing both reads and writes, you can and should delete the old database file.