I just upgraded my Kubuntu 10.10 to 11.04 and was surprised to find that upon opening Amarok (2.4.0) all of my statistics had disappeared.

I am using the external MySQL database functionality, and I can confirm from the back-end that the statistics are missing. Lucky for me I have a backup of the database from a few days ago. I'd like to restore this backup. I tried the following: quit amarok, restore the backup using mysql command line client, then start Amarok. The results were very strange. After starting Amarok all my tracks and statistics seemed to be there. Then Amarok began to do its normal periodic "Update Collection" thing automatically, and after that process finished Amarok showed only a couple dozen random artists (instead of the thousands I'd expect) and had some statistics. I've tried this process several times and also done a couple full scans, but the same thing happens each time -- Amarok's update collection process wipes out almost everything.

Very very weird. I feel confident that this mysql backup file has all my statistics data -- but how do I restore it?

Here's a launchpad issue about it:

https://bugs.launchpad.net/ubuntu/natty ... bug/768641

WORKAROUND:

Here are the steps I took to get my statistics data back using my MySQL backup file. Note that this procedure will wipe out your other Amarok settings such as bookmarks, layout, etc. Also note that in the following commands you'll have to replace the mysql user and password with your own, as well as replace the full path to your backup file.

Code: Select all


# (quit amarok)

# get rid of old settings files...
mv ~/.kde/share/config/amarokrc ~/.kde/share/config/amarokrc.old
mv ~/.kde/share/apps/amarok ~/.kde/share/apps/amarok.old

# create two databases to work with (old and new)...
mysql -uuser -ppass -e "create database amarok01;"
mysql -uuser -ppass -e "create database amarok02;"

# load the backup file into the "old" database
mysql -uuser -ppass amarok01 < /path/to/amarok_db/backup/file.sql

# do a bunch of stuff in the GUI...
# (launch amarok)
# (stop auto scan of music)
# (configure amarok to use database `amarok02`
# (set specific collection folder appropriately if necessary)
# (un-check "Watch folders for changes")
# (restart amarok)
# (do a full collection scan)
# (quit amarok)

# execute query to pull stats from old db into new db...
mysql -uuser -ppass -e "truncate amarok02.statistics;" amarok02
mysql -uuser -ppass -e "
  insert into amarok02.statistics
  select
    null,
    u2.id,
    s1.createdate,
    s1.accessdate,
    s1.score,
    s1.rating,
    s1.playcount,
    s1.deleted
  from amarok01.statistics s1
  join amarok01.urls u1 on u1.id = s1.url
  join amarok02.urls u2 on u2.rpath = u1.rpath; " amarok02

# see if it worked...
# (launch amarok)
# (do a full collection scan)
# (spot-check tracks as needed)

# if it all worked...
rm ~/.kde/share/config/amarokrc.old
rm -rf  ~/.kde/share/apps/amarok.old
mysql -uuser -ppass -e "drop database amarok01;"