The new Amarok 2.1 beta was getting very confused with my collection (some tracks were showing up with wrong artists and albums, and other tracks were missing), so I wanted to rebuild my database from scratch.  Only, I didn't want to lose years' worth of statistics that I've been building since the days of Amarok 1.3.  MySQL to the rescue!

# First, copy your existing, semi-screwed-up database to a temporary area:

Code: Select all

mkdir /tmp/amarok
cp -a ~/.kde/share/apps/amarok/mysqle/amarok /tmp/amarok/old

# Now blow away Amarok's copy of your database:

Code: Select all

rm -r ~/.kde/share/apps/amarok/mysqle

# Optionally, you can destroy all of Amarok's config, too:

Code: Select all

rm -r ~/.kde/share/{apps/amarok,config/amarok*}

# Optionally, you can tell MySQL Embedded not to bother initializing InnoDB, since
# Amarok doesn't use it anyway, and it just wastes memory, time, and disk space.

Code: Select all

cat <<EOF > ~/.kde/share/apps/amarok/my.cnf
[amarokserver]
skip-bdb
skip-innodb
default-storage-engine=MyISAM
EOF

# Now start Amarok and wait for it to rebuild your whole collection from scratch.

Code: Select all

amarok

# Once it's finished, fully quit Amarok. (Be sure it's not still in the tray!)

# Now copy the newly built database to the temporary area:

Code: Select all

cp -a ~/.kde/share/apps/amarok/mysqle/amarok /tmp/amarok/new

# To do the magic, we're going to need to start a MySQL server in that temp dir:

Code: Select all

mysqld --no-defaults --console --basedir=/usr --datadir=/tmp/amarok --skip-networking \
    --skip-bdb --skip-innodb --skip-grant-tables --socket=/tmp/amarok/mysqld.sock

# (You might need to type /usr/sbin/mysqld instead of just mysqld.)

# Now in another terminal, start up the MySQL client:

Code: Select all

mysql --socket=/tmp/amarok/mysqld.sock

# And issue this gnarly query:

Code: Select all

REPLACE INTO new.statistics
        (url, createdate, accessdate, score, rating, playcount, deleted)
    SELECT nu.id, os.createdate, os.accessdate, os.score, os.rating, os.playcount, os.deleted
        FROM old.statistics os
            INNER JOIN old.urls ou ON (ou.id = os.url)
            INNER JOIN new.urls nu ON (nu.rpath = ou.rpath);

# Don't panic. It'll take quite a while to run for large collections.

# Once the query is finished, you can exit the MySQL client and terminate the server:

Code: Select all

exit
killall mysqld

# All that remains is to copy the populated statistics table back to Amarok:

Code: Select all

cp -a /tmp/amarok/new/statistics.* ~/.kde/share/apps/amarok/mysqle/amarok/

# And clean up:

Code: Select all

rm -r /tmp/amarok