If you were paying close attention, you would have noticed that the “Elbee Elgee Development” section of my footer was messed up for the past few days, and with good reason. That little bit of content is generated by parsing the RSS feed for the timeline on my Elbee Elgee Trac installation. Sometime earlier this week, the SQLite database that provides the backend for that install freaked out and locked itself up good n’ tight which necessitated me spending several hours debugging and de-wedging the DB.
The problem can be summed up thusly: it seems that Trac sites running on FastCGI have a tendency to try to access the default SQLite database fairly quickly and, if the DB files happen to be stored on an incorrectly-tuned NFS share, a race condition can ensue and incorrect file locking can occur, leaving the DB in a locked state.
It was initially a very long and frustrating process, as I scoured teh Googols for information on how to unlock a SQLite database. I found that a second file was created in the db/ subdir of my Trac site – trac.db-journal, which apparently indicates that a transaction is in progress or was incorrectly aborted. Search result after search result implied that one could unlock the database by
- Killing all process accessing the file
- Moving the -journal file
- Moving the .db file itself
- Restarting Apache
- Rebooting the machine
Since I’m on a shared hosting account at Dreamhost, options 4 and 5 were obviously Right Out. No amount of lsof or fuser tweakage, combined with judicious use of `kill -9` managed to unwedge the DB, so #1 was a bust. Options 2 and 3 allowed me to use the sqlite command line tool to connect to the database and run SELECT statements, but any INSERTs were met with “Database locked”-style error messages and thus Trac stayed broken.
Then it hit me – since I could SELECT, I could (conceivably) dump the contents and then reimport them into another DB. I cd’d into my Trac site’s db/ folder and executed the following:
echo '.dump' | sqlite3 trac.db > trac.dump; cat trac.dump | sqlite3 trac2.db
Using the sqlite3 command line tool, I verified that everything was copacetic and breathed a sigh of relief. Stupid SQLite.
I guess this seals it: when I finally move from Trac 0.9.6 to 0.10.3, I’m going to move to a real RDBMS like MySQL so this sort of stupid file locking crap doesn’t happen again.
NOTE: Apologies for the 3rd Grade-level insult inherent in this post’s title (it’s pronounced “Suck-well Lite”). Pretty much sums up my feelings, though.
Oh man, this is exactly what I needed. Thank you for writing this up.
[…] you, http://literalbarrage.org/blog/archives/2007/03/23/suqlite/, for my formhistory.sqlite locked db […]
Very helpful. All other responses to this question on the tubes was speculation.
My thanks as well. Allowed us to fix a 3rd party app’s database which was just SQLite with a company-specific file extension.
These solutions only works if the filesystem is not NFS. If you have NFS, you have to copy the files on a different filesystem and start rescuing your data.
Untrue. Each and every one of the steps I performed, I performed on a Dreamhost box that was NFS mounting the user account’s home directory via NFS.
Additionally, there is no reason that these commands wouldn’t work on an NFS mount. What leads you to that conclusion, Sumit?
Strange, as the solutions stated above didn’t worked on two database files which were on NFS mount. May be our NFS is too old. As I moved the database files on ext3 mount the commands started working.