SuQLite

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

1. Killing all process accessing the file
2. Moving the -journal file
3. Moving the .db file itself
4. Restarting Apache
5. 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.