Amarok/Archives/Amarok 1.4/User Guide/MySQL HowTo

Configuration

Amarok Support

Amarok 1.2 and above support a MySQL database backend in addition to the built-in SQLite database engine. To get MySQL support compiled-in, you need to specify "--enable-mysql" as a configure parameter and re-run "make install" as root. Your configure line probably will look something like this:

$ ./configure --enable-mysql

Please also make sure you have the libmysqlclient libraries, along with the development headers (-dev packages) installed.

MySQL Setup

Amarok 1.4 requires MySQL 4.0 or better, and is known to work with MySQL versions up to 5.0.22 (but, at the time of writing, not 5.0.24). Since Amarok-1.4.2 MySQL-5.0.24 also works. Amarok 1.4.5 work with MySQL 5.0.27.

Older versions of Amarok may work best with MySQL versions < 5.0. One known problem as a result of this is Amarok's DB continually growing and adding multiple entries for every track on each rescan.

If your locale is UTF-8, make sure the default character set for your mqsl daemon is set up to utf8, so that all databases and tables are created with character set utf8. In Debian:

1) Edit /etc/mysql/my.cnf, adding this line to stanzas [client] and [mysqld]:

default-character-set = utf8

2) Restart the mqsql daemon to pick up the new default charset

Do this before you create the database for amarok.

Make sure the MySQL daemon is running. If necessary, add it to your linux startup scripts, via whatever method your distro uses.

Create a root password for MySQL, if you have not already done so.

1 $ mysql -u root 
set password for root@localhost = password('xxxxxxx'); 
flush privileges; 
quit;  

Of course change xxxxxx to the password you want.

Once you have done that, you must create a MySQL database and a user for amarok for through any usual method. You can just use the "mysql" command: (it will ask for your MySQL root password)

1 $ mysql -p -u root
CREATE DATABASE amarok;
USE amarok;
GRANT ALL ON amarok.* TO amarok@localhost IDENTIFIED BY 'PASSWORD_CHANGE_ME'; 

In the above example, a database called "amarok" and a user called "amarok" were created. This user can access the database from localhost using the password "PASSWORD_CHANGE_ME". To allow access from remote hosts, use amarokuser@'%'.

It is very important that you 'GRANT ALL' privileges to user "amarok". In particular, "amarok" needs ALTER privileges on its database.

Once a database exists, open the Configure Amarok screen (found in the Settings menu), and go to the Collection tab. Change the drop-down menu from SQLite to MySQL. You will have to specify the host ("localhost" if the database is in your local box), port (3306 is the common value), and the name of the database that you have created for it ("amarok" in our example). Additionally, the username and password of a user who has write access to the given database needs to be specified (in our example, the user is "amarok", and the password is "PASSWORD_CHANGE_ME").

Remote MySQL Server Gotcha: Most MySQL installs have the daemon listening only to localhost by default.

So if you get errors about not being able to connect to the server or database, (_not_ password related errors) then you will have to edit my.cnf on the host machine (/etc/mysql/my.cnf, most likely), comment out the "bind_address" variable and restart MySQL. You may have to comment out "skip_networking", so that MySQL will listen on a tcp socket.

AFIAK there is no way of tweaking which interfaces it listens on. It listens on only 1 or on all. You should then update your firewall accordingly.

Database Conversions

SQLite -> MySQL

An (unsupported) way to transfer your SQLite3 database to MySQL:

1cd ~/.kde/share/apps/amarok && \
sqlite3 collection.db .dump | \ 
egrep -vi '^(BEGIN TRANSACTION|COMMIT|CREATE|INSERT INTO "devices")' | \
perl -pe 's/INSERT INTO \"(.*)\" VALUES/INSERT INTO \1 VALUES/' | \
mysql -u root -p amarok 

Some had trouble with the previous script that was here, but the above version worked. If you get an error, (album names with backslashes can cause problems) then drop the last line above and output the results to a file. {{Output|1cd ......\1 VALUES/' > ~/tmp/amarok_dump Edit the file to fix the errors in whatever handy text editor you prefer. Delete any tables that were created in the Amarok database (I used phpMyAdmin, but if you want to do this via MySQL commands and don't know how... seek information in the MySQL Reference Manual). Then try the last part again using your output file. {{Output|1cat ~/tmp/amarok_dump | mysql -u root -p amarok If you receive any error like this

ERROR 1071 (42000) at line 25380: Specified key was too long; max key length is 1000 bytes

open the amarok_dump and replace every VARCHAR(1024) with VARCHAR(996)

The original script tried to commit the following substitution: s/VARCHAR\(256\)/VARCHAR\(255\)/ however my SQLite dump file only had VARCHAR(255) references. You might want to check yours to make sure by running something like: {{Output|1grep "VARCHAR(256)" ~/tmp/amarok_dump If you get any results then try to fix them with:

1perl -pe 's/VARCHAR\(256\)/VARCHAR\(255\)/' < ~/tmp/amarok_dump > ~/tmp/amarok_dump2 && mv ~/tmp/amarok_dump2 ~/tmp/amarok_dump

MySQL also comes by default with a useful command line utility called replace, which can replace multiple strings within a file:

1replace "VARCHAR(256)" "VARCHAR(255)" -- ~/tmp/amarok_dump

You can also specify several pairs of strings for the search/replace provided you remember to terminate the line with:

1-- ~/tmp/amarok_dump

In case the mysql Amarok database does have some records already, you might truncate all tables as shown

1mysqldump -u root -p[ROOT_PASSWORD] amarok --no-data --compact --add-drop-table | grep ^DROP | sed -e 's/DROP TABLE IF EXISTS/TRUNCATE TABLE/g'| mysql -u root -p[ROOT_PASSWORD] amarok

Then run the command as described in the beginnig of this section and let Amarok rescan your library (Tools->Rescan Collection).

An alternate method is to import my SQLite data as follows:

Start Amarok, change to MySQL, and build the database (but don't play any songs) Download SQLite Database Browser Export the statistics database to a dump file, amarok_dump.sql Remove all BEGIN TRANSACTION, COMMIT and CREATE sql commands Import the file using MySQL:

1cat amarok_dump.sql | mysql -u root -p amarok

If you receive any errors, its possible that you played some songs and a statistics entry already exists for a particular file. If this is the case, you will have to edit amarok_dump.sql, find the offending line and remove EVERYTHING before it (since those commands have already been executed by MySQL - you will get errors otherwise). If you want to still use the offending line, replace INSERT INTO with REPLACE INTO. Re run the above command.

MySQL -> SQLite

An (unsupported) way to transfer your MySQL database to SQLite3:

(Note this will not currently work with Amarok >= 1.4.2 due to changes in the schema)

1CREATE TABLE album (id INTEGER PRIMARY KEY ,name VARCHAR(255) );
CREATE TABLE artist (id INTEGER PRIMARY KEY ,name VARCHAR(255) );
CREATE TABLE directories (dir VARCHAR(255) UNIQUE,changedate INTEGER );
CREATE TABLE genre (id INTEGER PRIMARY KEY ,name VARCHAR(255) );
CREATE TABLE images (path VARCHAR(255),artist VARCHAR(255),album VARCHAR(255) );
CREATE TABLE related_artists (artist VARCHAR(255),suggestion VARCHAR(255),changedate INTEGER );
CREATE TABLE statistics (url VARCHAR(255) UNIQUE,createdate INTEGER,accessdate INTEGER,percentage FLOAT,playcounter INTEGER,rating INTEGER);
CREATE TABLE tags (url VARCHAR(255),dir VARCHAR(255),createdate INTEGER,album INTEGER,artist INTEGER,genre INTEGER,title VARCHAR(255),year INTEGER,comment VARCHAR(255),track NUMERIC(4),bitrate INTEGER,length INTEGER,samplerate INTEGER,sampler BOOL );
CREATE TABLE year (id INTEGER PRIMARY KEY ,name VARCHAR(4) );
CREATE INDEX album_idx ON album( name );
CREATE INDEX album_tag ON tags( album );
CREATE INDEX artist_idx ON artist( name );
CREATE INDEX artist_tag ON tags( artist );
CREATE INDEX directories_dir ON directories( dir );
CREATE INDEX genre_idx ON genre( name );
CREATE INDEX genre_tag ON tags( genre );
CREATE INDEX images_album ON images( album );
CREATE INDEX images_artist ON images( artist );
CREATE INDEX percentage_stats ON statistics( percentage );
CREATE INDEX playcounter_stats ON statistics( playcounter );
CREATE INDEX related_artists_artist ON related_artists( artist );
CREATE INDEX sampler_tag ON tags( sampler );
CREATE INDEX url_stats ON statistics( url );
CREATE INDEX url_tag ON tags( url );
CREATE INDEX year_idx ON year( name );
CREATE INDEX year_tag ON tags( year ); 

and import your mysqldump at your "sqlite>" prompt:

1.read amarok.sql

Amarok 1.4.8 and MySQL 5.0.45

I managed to convert my MySQL database to SQLite running Amarok 1.4.8. I cannot guarantee that it worked perfectly or that it will work for you, but this is how I did it.

Backup the old SQLite database and export the MySQL database (with some conversions).

1cd ~/.kde/share/apps/amarok && \
mv collection.db collection.db.old && \
mysqldump -u root --skip-opt --skip-comments -p -n -t amarok | grep -v magnatune_ | sed -e "s/\\\n/\n/g" | sed -e "s/\\\'/\'\'/g" | sed -e "s/\\\\\"/\"\"/g" > amarok.sql && \
sqlite3 collection.db 

Paste the following SQL statements at the prompt to create the tables and indices (I generated this by exporting the schema of a fresh SQLite database created by Amarok 1.4.8).

1CREATE TABLE tags (url VARCHAR(1024),dir VARCHAR(1024),createdate INTEGER,modifydate INTEGER,album INTEGER,artist INTEGER,composer INTEGER,genre INTEGER,title VARCHAR(255),year INTEGER,comment TEXT,track NUMERIC(4),discnumber INTEGER,bitrate INTEGER,length INTEGER,samplerate INTEGER,filesize INTEGER,filetype INTEGER,sampler BOOL,bpm FLOAT,deviceid INTEGER);
CREATE TABLE album (id INTEGER PRIMARY KEY ,name VARCHAR(255));
CREATE TABLE artist (id INTEGER PRIMARY KEY ,name VARCHAR(255));
CREATE TABLE composer (id INTEGER PRIMARY KEY ,name VARCHAR(255));
CREATE TABLE genre (id INTEGER PRIMARY KEY ,name VARCHAR(255));
CREATE TABLE year (id INTEGER PRIMARY KEY ,name VARCHAR(255));
CREATE TABLE images (path VARCHAR(1024),deviceid INTEGER,artist VARCHAR(255),album VARCHAR(255));
CREATE TABLE embed (url VARCHAR(1024),deviceid INTEGER,hash VARCHAR(1024),description VARCHAR(255));
CREATE TABLE directories (dir VARCHAR(1024),deviceid INTEGER,changedate INTEGER);
CREATE TABLE uniqueid (url VARCHAR(1024),deviceid INTEGER,uniqueid VARCHAR(32) UNIQUE,dir VARCHAR(1024));
CREATE TABLE admin (noption VARCHAR(255), value VARCHAR(255));
CREATE TABLE related_artists (artist VARCHAR(255),suggestion VARCHAR(255),changedate INTEGER );
CREATE TABLE amazon ( asin VARCHAR(20), locale VARCHAR(2), filename VARCHAR(33), refetchdate INTEGER );
CREATE TABLE lyrics (url VARCHAR(1024), deviceid INTEGER,lyrics TEXT, uniqueid VARCHAR(32));
CREATE TABLE playlists (playlist VARCHAR(255), url VARCHAR(1024), tracknum INTEGER );
CREATE TABLE labels (id INTEGER PRIMARY KEY , name VARCHAR(255), type INTEGER);
CREATE TABLE tags_labels (deviceid INTEGER,url VARCHAR(1024), uniqueid VARCHAR(32), labelid INTEGER REFERENCES labels( id ) ON DELETE CASCADE );
CREATE TABLE podcastchannels (url VARCHAR(1024) UNIQUE,title VARCHAR(255),weblink VARCHAR(1024),image VARCHAR(1024),comment TEXT,copyright VARCHAR(255),parent INTEGER,directory VARCHAR(255),autoscan BOOL, fetchtype INTEGER, autotransfer BOOL, haspurge BOOL, purgecount INTEGER );
CREATE TABLE podcastepisodes (id INTEGER PRIMARY KEY , url VARCHAR(1024) UNIQUE,localurl VARCHAR(1024),parent VARCHAR(1024),guid VARCHAR(1024),title VARCHAR(255),subtitle VARCHAR(255),composer VARCHAR(255),comment TEXT,filetype VARCHAR(255),createdate VARCHAR(255),length INTEGER,size INTEGER,isNew BOOL );
CREATE TABLE podcastfolders (id INTEGER PRIMARY KEY , name VARCHAR(255),parent INTEGER, isOpen BOOL );
CREATE TABLE statistics (url VARCHAR(1024),deviceid INTEGER,createdate INTEGER,accessdate INTEGER,percentage FLOAT,rating INTEGER DEFAULT 0,playcounter INTEGER,uniqueid VARCHAR(32) UNIQUE,deleted BOOL DEFAULT 0,PRIMARY KEY(url, deviceid) );
CREATE TABLE devices (id INTEGER PRIMARY KEY ,type VARCHAR(255),label VARCHAR(255),lastmountpoint VARCHAR(255),uuid VARCHAR(255),servername VARCHAR(255),sharename VARCHAR(255));
CREATE INDEX album_idx ON album( name );
CREATE INDEX artist_idx ON artist( name );
CREATE INDEX composer_idx ON composer( name );
CREATE INDEX genre_idx ON genre( name );
CREATE INDEX year_idx ON year( name );
CREATE UNIQUE INDEX url_tag ON tags( url, deviceid );
CREATE INDEX album_tag ON tags( album );
CREATE INDEX artist_tag ON tags( artist );
CREATE INDEX composer_tag ON tags( composer );
CREATE INDEX genre_tag ON tags( genre );
CREATE INDEX year_tag ON tags( year );
CREATE INDEX sampler_tag ON tags( sampler );
CREATE INDEX images_album ON images( album );
CREATE INDEX images_artist ON images( artist );
CREATE INDEX images_url ON images( path, deviceid );
CREATE UNIQUE INDEX embed_url ON embed( url, deviceid );
CREATE INDEX embed_hash ON embed( hash );
CREATE UNIQUE INDEX directories_dir ON directories( dir, deviceid );
CREATE INDEX uniqueid_uniqueid ON uniqueid( uniqueid );
CREATE INDEX uniqueid_url ON uniqueid( url, deviceid );
CREATE INDEX tags_artist_index ON tags( artist );
CREATE INDEX tags_album_index ON tags( album );
CREATE INDEX tags_deviceid_index ON tags( deviceid );
CREATE INDEX tags_url_index ON tags( url );
CREATE INDEX embed_deviceid_index ON embed( deviceid );
CREATE INDEX embed_url_index ON embed( url );
CREATE INDEX related_artists_artist ON related_artists( artist );
CREATE INDEX url_podchannel ON podcastchannels( url );
CREATE INDEX url_podepisode ON podcastepisodes( url );
CREATE INDEX localurl_podepisode ON podcastepisodes( localurl );
CREATE INDEX url_podfolder ON podcastfolders( id );
CREATE INDEX devices_type ON devices( type );
CREATE INDEX devices_uuid ON devices( uuid );
CREATE INDEX devices_rshare ON devices( servername, sharename );
CREATE UNIQUE INDEX lyrics_url ON lyrics( url, deviceid );
CREATE INDEX lyrics_uniqueid ON lyrics( uniqueid );
CREATE INDEX playlist_playlists ON playlists( playlist );
CREATE INDEX url_playlists ON playlists( url );
CREATE UNIQUE INDEX labels_name ON labels( name, type );
CREATE INDEX tags_labels_uniqueid ON tags_labels( uniqueid );
CREATE INDEX tags_labels_url ON tags_labels( url, deviceid );
CREATE INDEX tags_labels_labelid ON tags_labels( labelid );
CREATE UNIQUE INDEX url_stats ON statistics( deviceid, url );
CREATE INDEX percentage_stats ON statistics( percentage );
CREATE INDEX rating_stats ON statistics( rating );
CREATE INDEX playcounter_stats ON statistics( playcounter );
CREATE INDEX uniqueid_stats ON statistics( uniqueid ); 

Then import the actual data exported from MySQL.

1.read amarok.sql
.quit 

Automatic Backups

Note: in addtion to the instructions below it is also necessary to backup ~/.kde/share/config/amarokrc as this contains important database version information.

This can be done easily using a cron job. Create a shell script and place it in your /etc/cron.weekly directory (or your cron directory).

1 #!/bin/sh
 # Backup the Amarok MySQL database
 # Version 2007-02-06-b, 
 # now "/bin/sh -> /bin/dash" compatible; backup files now named "amarok.mysql.tar.bz2"; ask before overwrite
 

 ######### CHANGE SETTINGS HERE #########
 # Location to place Amarok-database backups:
 BACKUP_BASE_DIR=~/backup
 
 # Name of MySQL-database:
 AMAROKDB_NAME=amarok
 
 # Username and password for Amarok-MySQL-database:
 AMAROKDB_USER=amarok
 AMAROKDB_PW=PASSWORD
 
 # Location of amarokrc:
 AMAROKRC=~/.kde/share/config/amarokrc
 ######### END OF SETTINGS SECTION #########
 
 BACKUP_SUBDIRECTORY=`date +%Y-%m-%d`
 
 if [ ! -w $BACKUP_BASE_DIR -o ! -d $BACKUP_BASE_DIR ]
 then
   printf %b "\n\n $BACKUP_BASE_DIR doesn't exist or is not writable!\n\n"
   printf %b "STOP.\n\n"
   exit 1;
 fi
 
 if [ ! -r $AMAROKRC ]
 then
   printf %b "\n\n $AMAROKRC doesn't exist or Amarok is not installed!\n\n"
   printf %b "STOP.\n\n"
   exit 1;
 fi
 
 mkdir -p $BACKUP_BASE_DIR/amarok/$BACKUP_SUBDIRECTORY

                #using a temporary file in order not to overwrite an existing copy when the script is 
                #manually invoked a second time (e.g. right before restoration of existing backup of same date!)
 mysqldump -u $AMAROKDB_USER -p$AMAROKDB_PW $AMAROKDB_NAME -c -e --hex-blob > /tmp/amarok.mysql

 tar --remove-files -C /tmp -cvvjf /tmp/amarok.mysql.tar.bz2 amarok.mysql

 mv -i -v /tmp/amarok.mysql.tar.bz2 $BACKUP_BASE_DIR/amarok/$BACKUP_SUBDIRECTORY
 
 # The config file amarokrc will also be secured:
 cp -i -v $AMAROKRC $BACKUP_BASE_DIR/amarok/$BACKUP_SUBDIRECTORY
 

This script will create a directory by date in '~/backup/amarok' and store a bz2 compressed MySQL dump and a copy of the configuration file '~/.kde/share/config/amarokrc' into it. This assumes the username is 'amarok' and the database name is 'amarok'. In the settings section, replace "PASSWORD" by your password and the names as necessary. It will also require you to GRANT ALL in your permissions for the Amarok user.

Important: This will not backup your playlists or album covers. Static .m3u playlists are stored by default by Amarok in ~/.kde/share/apps/amarok/playlists (your home directory). Dynamic playlists are stored in the file ~/.kde/share/apps/amarok/smartplaylistbrowser_save.xml. Both must at present be copied manually from your chosen backup folder as the above script does not capture them. Album covers can simply be copied across with 'sudo nautilus' and pasting the backed-up folder over the unwanted folder; the album covers folder is ~/.kde/share/apps/amarok/albumcovers.

Restore From an Automatic Backup

Restoring from a previous backup can be done in a few steps. First, remove any old databases and create a new amarok database (this example uses the same naming convensions as the above backup script):

1# New version. Previous example forgot to mention the need to reset
# the access rights via "GRANT ALL"...

$ mysql -p -u root
DROP DATABASE amarok;
CREATE DATABASE amarok;
USE mysql;
GRANT ALL ON amarok.* TO amarok@localhost IDENTIFIED BY 'PASSWORD_CHANGE_ME';
FLUSH PRIVILEGES;
QUIT; 

Next, we need to restore the backup. Lets assume we have one from July 12th, 2006:

1$ cd ~/backup/amarok/2006-07-12/
$ tar -xvjf amarok.mysql.tar.bz2
$ mysql -p -u amarok -h localhost amarok < amarok.mysql 

Now your database should be restored. Note that the first instance of 'amarok' is the username and the second instance is the database name.

It is also necessary to restore ~/.kde/share/config/amarokrc as this contains important database version information.

Repair a Corrupted Database

When your database is corrupted, you cannot see all your songs in the collection browser, and you could see in /var/log/messages that the MySQL database is corrupted. To repair the database, launch the mysqlcheck command as root:

1mysqlcheck -p --auto-repair --all-databases 

Then rescan your collection to update the database.

Tips for Sharing a MySQL Database

• It is *most* important that all machines that are sharing your database have the music files mounted on exactly the same path. At the same time, make sure all copies of Amarok are configured with exactly the same folders checked. If either the paths or the folders differ, your different instances of Amarok will be constantly rescanning the collection.
• Make sure you use exactly the same version of amarok on all machines sharing the database.
• Use a wrapper script on the amarok binary to prevent it from starting (and thus wiping your database) if your music mount(s) isn't available.
• Restrict incremental updating via "Watch folders for changes" (On the Amarok settings, Collection page) to just one machine. This way, when new music is added, only one machine will scan it into the database. Alternatively, you can disable incremental scanning on all machines, and run a manual collection rescan whenever you like (Tools Menu, Rescan Collection).
• Make sure all computers have the same Amarok database name/username/password set.

(It may be possible to set up multiple users with access to the Amarok database)

• IMPORTANT NOTE: Since the introduction of Dynamic Collections in Amarok 1.4.2, if you wish to use a shared database, you will need to add

1DynamicCollection=false

to the [Collection] section of

1~/.kde/share/config/amarokrc

as posted in Bug #136826