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:

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:

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:

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:

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.

On my Laptop, I am running Linux Mint 12. On my home media server, I am running Ubuntu 12.04Check out my profile for more information.

@Jon F
What’s your secret with Banshee? Installed it and the cpu usage was way up for quite some time (at least 30 minutes). I clicked a song to view the sort of metadata Banshee would display and it crashed. Not impressed, I uninstall.
btw using mint 10 amd64.

@bemused I’ve never noticed that kind of resource consumption. I’m on Ubuntu 9.10 over here, and Banshee is currently using 6% of my CPU and about 94MB of RAM. Since I run a dual-core box with 4GB or RAM, this is well within acceptable range. I have noticed that Banshee does a library re-indexing on startup – perhaps this is the source of your problems? It probably generates a lot of IO activity that causes some additional resource usage.

@Jon F
I’ve been using Linux for only a year and 1/2 and so far I’ve noticed that not all packages behave the same on my machine – as advertised :). I am using an Acer Aspire with 3GB and dual-core. So what I do is find apps that behave ok on my machine.
Yesterday with Banshee running (for over 8hrs) CPU rates did not go below 13%Banshee’s memory usage was 190MB and 37MB of swap was also in use. The only other time I see swap usage is when running virtualbox.
Today, I noticed the sound would stutter at times.
I’m glad that Banshee works for you. After all that’s all that counts.

@bemused – The best thing that you can do in a situation like that is to file a bug with the project. In the case of banshee, you can file a bug at http://banshee.fm/contribute/file-bugs/. The one thing that I’ve noticed above all in my time on Linux is the community. They want to hear about your trials, and they’ll be happy to try and solve your problems. Just make sure that you do your homework before filing a new bug, and always include a log file!

I know this is an old post, but it was one of the top search results when I Googled this issue, so I figure I would share my experience for others to find.

I ran into the same problem today, but my recovery had an additional wrinkle.

When my banshee.db got corrupted I tried the same dump and restore method, and ended up with a banshee.db file that was zero bytes in size.

The problem was that due to the errors that were detected during the dump step, sqlite wrote a ROLLBACK statement to the last line of the dump file. People familiar with SQL know that a rollback statement means “ignore the contents of the transaction I just entered”. Since the dump is one large transaction, nothing got written to banshee.db.

The fix in my case was to edit the dump file, and replace the last line that said “ROLLBACK;” with this:

COMMIT;

Commit tells sqlite to commit the contents of the transaction to the database. Commit is what would have been at the end of the dump file if sqlite had not encountered any “serious” errors during the dump.

I saved the edited dump file and read it into sqlite again, this time with success. Banshee loaded properly and all of my album info was back. Yay!

The only issue after that was that two songs whose album name I changed just prior to the crash weren’t playable because the recovered database contained their old locations. I removed those two songs from the library, then ran Tools > Rescan Music Library in Banshee, and it added those 2 songs back automatically.

Thanks a lot for sharing, Ryan!
I ran into the very same problem when Banshee updated my music library with tracks that contained exotic UTF data in their ID3.
Your article helped me to recover the database — strangely enough with or without removing the questionable tracks from the INSERTs.