Recovering a Corrupted Banshee Database

A couple of nights ago, I left Banshee running overnight, filling my phone with music to listen to the next day at work. Sometime during that process, the media player crashed in a big way, and my entire system ground to a halt. When I woke up the next morning, my computer was frozen solid, with no keyboard or mouse input accepted, and the system clock hadn’t advanced since 11:16pm the previous night. I did a hard reboot, hoping that all would be well when it came back up.

Boy was I wrong.

The Problem

It seems that when Banshee went down, it took my library database file with it. Now, whenever I launch the application, I get a lovely dialog box that looks something like this:

Launching the application from the command line provides more details:

jonf@THE-LINUX-EXPERIMENT: ~/.config/banshee-1$ banshee-1
[Info  11:20:32.175] Running Banshee 1.9.0: [source-tarball (linux-gnu, x86_64) @ 2010-12-09 13:07:07 EST] [Warn  11:20:32.451] Service `Banshee.Database.BansheeDbConnection’ not started: The database disk image is malformed
database disk image is malformed
[Warn  11:20:32.453] Caught an exception – Mono.Data.Sqlite.SqliteException: The database disk image is malformed

Apparently, the SQLite database that forms the backend of Banshee is severely corrupted, and the application can’t open it for reading. Based on the command line output, I figured that Banshee uses SQLite3 for its database, and after a little bit of research, I found out that it stores the database file at ~/.config/banshee-1/banshee.db. After installing the SQLite3 package from my repositories with the command sudo apt-get install sqlite3, I was able to connect to the database without issue. A little bit of reading over at sqlite.org showed me how to do an integrity check on the database, which came back with some nasty results:

jonf@THE-LINUX-EXPERIMENT:~/.config/banshee-1$ sqlite3 -interactive banshee.db
SQLite version 3.6.22
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite> PRAGMA integrity_check;
*** in database main ***
Main freelist: 2291 of 2292 pages missing from overflow list starting at 18928
On tree page 28 cell 4: Child page depth differs
On tree page 28 cell 6: 2nd reference to page 15475
On tree page 28 cell 6: Child page depth differs
…
On tree page 16807 cell 25: Child page depth differs
On tree page 16807 cell 26: 2nd reference to page 18171
On tree page 16807 cell 26: Child page depth differs

Each line of this output refers to a broken or corrupted index, with a total of 99 errors reported. Yikes.

The Solution

Before attempting to fix the problem, I made a backup of the broken database, just in case some data could be resurrected from it. To do this, I made sure that Banshee wasn’t running, and then made a copy of the database file with the command cp ~/.config/banshee-1/banshee.db ~/.config/banshee-1/banshee.db.old.

Since the integrity check showed a number of broken or corrupted indices, I thought perhaps I would be able to recover the database by rebuilding all of its indices. A quick scan of the SQLite documentation turned up the REINDEX function. Again, I connected to the database through the command-line interpreter, and gave it a shot:

jonf@THE-LINUX-EXPERIMENT:~/.config/banshee-1$ sqlite3 -interactive banshee.db
SQLite version 3.6.22
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite> REINDEX;
Error: database disk image is malformed

That clearly didn’t work.

My next thought was to drop and re-create all of the indices in the database, in hopes that I could rebuild them. I used the graphical tool sqliteman (available from your repositories with the command sudo apt-get install sqliteman) to dump the database schema out to a text file and then scrolled through the file looking for each index. I managed to drop all but four of them, but got the image malformed error whenever I tried to drop the remaining four or recreate any of the ones that were successfully dropped.

Out of ideas, I resorted to attempting to recover what data I could from the mangled file. Using sqliteman, I created a dump of my library data to go along with the schema dump that I had created earlier. The good thing about this dump is that it contains all of the data with none of the indices. Next, I opened up a terminal and navigated to the temporary directory where I had saved the database dump. Using the sqlite3 command line interpreter, I built an image of the old database from the dump:

jonf@THE-LINUX-EXPERIMENT:~/Desktop/banshee$ sqlite3 -interactive banshee.db
SQLite version 3.6.22
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite> .read dump.sql

Finally, I copied the newly created database into the banshee data directory with the command cp ~/Desktop/banshee/banshee.db  ~/.config/banshee-1/banshee.db.

The next time I started the application, all of my data was restored.

Prevention

I’ve put a lot of work into collecting and maintaining all of the music in my Banshee library. In order to avoid losing all of that work, I’ve decided to write a short script that takes a backup image of the library database every so often, and puts it in a safe place on my hard drive. Once I get that put together, I’ll throw it up on here for so that everybody can use it.