Planetary Ponderings

Last week I noticed that in one of my usual software updates, I got an upgrade to Quassel. The Ubuntu packager had packaged version 0.5rc1 and put it in the backports repository. This was all great, and I was amped to upgrade my Quassel Core to 0.5rc1 as well. Little did I know the issues it would cause...

Upgrading Quassel Core is usually a very simple process: shut down the old version and run the new version. This time didn't prove to be so successful. I ran the Core, it detected an older database and it upgraded the database. That all went well, and then the trouble started.

I tried to connect to the Core from my Client, but I only got as far as the "connected to server" screen. Quassel didn't hang, it just didn't get any further. So I "aborted" the connection and tried again. Same thing. Then I logged into the server to see if there were any error messages in the logfile. Sure enough, there were some database errors:

2009-08-28 13:43:08 Error: unhandled Error in QSqlQuery!
2009-08-28 13:43:08 Error:                   last Query:
 UPDATE buffer
SET lastseenmsgid = :lastseenmsgid
WHERE userid = :userid AND bufferid = :bufferid
2009-08-28 13:43:08 Error:               executed Query:
 UPDATE buffer
SET lastseenmsgid = ?
WHERE userid = ? AND bufferid = ?
2009-08-28 13:43:08 Error:                 bound Values: :bufferid=106, :lastseenmsgid=817853, :userid=3
2009-08-28 13:43:08 Error:                 Error Number: 5
2009-08-28 13:43:08 Error:                Error Message: database is locked Unable to fetch row
2009-08-28 13:43:08 Error:               Driver Message: Unable to fetch row
2009-08-28 13:43:08 Error:                   DB Message: database is locked

Database locked? An SQLite database? According to the SQLite documentation, that only happens when you have more than one application attempting to access the database. SQLite doesn't support more than 1 connection at a time. How can this be? I only have 1 instance of Quassel Core running.

So I killed the Core, and when it didn't want to quite, I killed it with -9. Then I opened the database file, and ran VACUUM on it, since I could see that it was corrupt (SQLite fortunately keeps a journal file so that you can recover the database). After that I ran the core again and I could log in, excellent!

Once I was back up and running I asked the other users on my core (there are 2 others) if they could log in. They both got the same result that I initially got. I logged out and logged in again, and sure enough, I got stuck at the "connected" screen again. I logged back into the server and killed the Core with a -9 again, and repaired the database with VACUUM again.

For my next step, I decided to try to migrate my Core to a PostgreSQL database. The Core very nicely takes care of this with the --select-backend command line option. I installed PostgreSQL 8.3 on my server, as that was the available version. Then I started with the migration.

The migration started successfully, but bombed out while importing the backlog:

Transferring Backlog...
*****************************************Migration Failed!
AbstractSqlMigrationReader::transferMo(): unable to transfer Migratable Object of type Backlog!
WriterError:
  executed Query:
INSERT INTO backlog (messageid, time, bufferid, type, flags, senderid, message)
VALUES (?, ?, ?, ?, ?, ?, ?)
  bound Values:
0 :  42739
1 :  2008-12-13T08:28:33
2 :  2
3 :  1024
4 :  0
5 :  1
6 :  Disconnecting.
  Error Number: -1
  Error Message: "ERROR:  insert or update on table "backlog" violates foreign key constraint "backlog_bufferid_fkey"
DETAIL:  Key (bufferid)=(2) is not present in table "buffer".
QPSQL: Unable to create query"

Oh dear, another problem! I went back to the SQLite database, searched for this record and found that there was no corresponding buffer in the buffer table. To fix this, I simply set the buffer id to an existing buffer (it's old and the data doesn't seem to be that important).

I logged back into the PostgreSQL server, cleared out the database, started everything from the beginning again, and got hit with another "phantom" buffer. So it was back to fixing the problematic row, clearing out the database, and starting from the beginning again. And then I hit another problem record.

Fortunately for me, that was the last problem record, and the rest of the migration went smoothely. Once that was done, it was time for me to try to log in again. I also asked one of my other users to try to log in as well. He logged in fine, but I couldn't get in. My Client would start downloading the backlog ever so slowly, and then timeout before it actually got anywhere.

During this whole process, I'd been in the Quassel IRC channel chatting with some of the guys, and one of them pointed out a bug in PostgreSQL 8.3 whereby the index in the prepared statement was being ignored. I was told that 8.4 seemed to mostly fix that bug, but I didn't really want to install newer, possibly unstable, software on my Ubuntu LTS server. I tried once more to use the SQLite database, and after yet another unsuccessful attempt, I started hunting around for a repository with 8.4 precompiled for me.

I found a Launchpad PPA with 8.4 for Ubuntu Hardy, and installed it. I had to re-setup my user and database, but once I'd done that I proceeded to once again migrate my data. The migration once again was successful, and this time I could log in!

I asked both my users to try to log in as well, and sure enough, they could! Yayness!

Useful Links

• Quassel Website
• Setting up a Quassel PostgreSQL backend