Fixing Amarok’s Collection Database

The latest version of X Windows (Xorg) really wants to use something called hardware abstraction layer to handle devices, but I’m experiencing some side effects.  For example,  enabling  Hardware Abstraction Layer Daemon (hald) caused my music player (Amarok) to lose all its song collection information.  I sure didn’t want to have to re-rate all those songs and lose all the other  song play and similar artists info.  It’s not life or death, but no sense to lose it.

It turns out there’s a pretty elegant fix to retain all your song information even after installing hald.  I think this would also work in the less specific case where you moved your music from one location to another.

1. Fully rescan the collection in Amarok. (tools, rescan collection).  Close Amarok.

2. Open the mysql console on the command line, using
mysql -u root -p

3. Issue the following commands at the mysql prompts
mysql>use amarokdb;
mysql>delete from tags;
mysql>quit;

Then, restart Amarok, and rescan the collection.

Credit for this solution goes to Max Kossick: Original post.

The technical description:
Checking out the devices table in MySQL, I noticed Amarok sees hard drives (and probably other devices) differently now that hal is running.

Before hald, Amarok saw the drives like: /org/kde/mediamanager/fstab/devad0s1a
After hald, it’s more like: /org/freedesktop/Hal/devices/volume_uuid_0AA3_32DF

So the solution was to empty the tags table.  My guess is that during the rescan, Amarok then notices there aren’t any tags in its collection database for all these files, and re-updates the tags.  While looking at the files, it notices, hey – those are in new locations now.   (The deviceid is a foreign key to the devices table.)

If you’re morbidly curious about the Amarok collection table structure, there’s a diagram here: Amarok Table Structure

If for some reason, you don’t trust the easy solution, you can meticulously update deviceID for each table where it’s listed.  This solution borrows extremely heavily from Max’s original post (credit for this portion goes to Seb Ruiz).

mysql> use amarokdb;
mysql> UPDATE embed SET deviceid=B WHERE deviceid=A;
mysql> UPDATE images SET deviceid=B WHERE deviceid=A;
mysql> UPDATE labels SET deviceid=B WHERE deviceid=A;
mysql> UPDATE lyrics SET deviceid=B WHERE deviceid=A;
mysql> UPDATE playlists SET deviceid=B WHERE deviceid=A;
mysql> UPDATE statistics SET deviceid=B WHERE deviceid=A;
mysql> UPDATE tags SET deviceid=B WHERE deviceid=A;
mysql> UPDATE tags_labels SET deviceid=B WHERE deviceid=A;
mysql> UPDATE uniqueid SET deviceid=B WHERE deviceid=A;

You can figure out what values to use for deviceid (instead of A and B)
by doing the following:
mysql>select distinct deviceid from tags;

That's all for now.
Scott