Getting Amarok to use an external MySQL database [Solved]

[Fitzcarraldo]

Getting Amarok 2.2.2.90 and MySQL 5.1.44-r1 working together makes configuring PulseAudio look like a walk in the park. This is why Linux will never be for 'ordinary people'.

The latest version of Amarok (2.2.2.90) has to use an external MySQL database; the developers have removed the use of MySQL Embedded as an option.

EDIT (March 23, 2010 11:25 UTC): My mistake, Amarok >=2.2.2.90 does not oblige the user to use an external database server; what the Amarok developers have done is to make MySQL the only possible external database server: MySQL in Amarok 2 - The Reality.

I have just spent the whole afternoon battling with MySQL and Amarok in order to create and configure an external MySQL database for Amarok. I even had to remove all Qt packages and install the latest versions (don't ask), although hopefully you won't have to go to those lengths.

Apart from the instructions on the official Amarok Web site, Google pointed me to various blogs, forum threads and other Web sites with instructions on how to set up MySQL and Amarok 2. The trouble is, none of them worked in my case: I had to 'cherry pick' from them in order to achieve my goal.

Anyway, I tried to keep some notes of what I did, and here they are in case they help someone. No guarantees that I have remembered everything, as I had to go through many hoops, but hopefully there is enough information here to at least point you in the right direction if you're stuck. (I've omitted the whole saga of removing all Qt packages and installing the latest versions: hopefully that was only necessary because at one point I deleted /usr/lib/mysql/* in order to get rid of a MySQL database that I had somehow managed to screw up.)

I'm posting this in the Portage shed, but most of this is independent of the package manager you use.

First I un-installed the older versions of Amarok and MySQL (to see if it would clear out obsolete files), and installed the latest versions:

Code: Select all

# emerge -C virtual/mysql
# emerge -C app-db/mysql
# emerge -1v mysql
# emerge -C amarok amarok-utils
# rm /home/fitzcarraldo/.kde4/share/apps/amarok/
# emerge -1v amarok amarok-utils

^The embedded USE flag for Amarok should be off, i.e. USE="-embedded" (I didn't need to do it explicitly).

Then I edited /etc/mysql/my.cnf and changed:

Code: Select all

datadir = /var/lib/mysql

to:

Code: Select all

datadir = /usr/lib/mysql

Then I set up the MySQL root user's password for the new version of MySQL:

Code: Select all

# emerge --config =dev-db/mysql-5.1.44-r1


Configuring pkg...

 * MySQL MY_DATADIR has changed
 * from /var/lib/mysql
 * to /usr/lib/mysql
 * Previous MY_DATADIR (/var/lib/mysql) does not exist
 * Attempting to use /usr/lib/mysql
 * Creating the mysql database and setting proper
 * permissions on it ...
 * Insert a password for the mysql 'root' user
 * Avoid ["'\_%] characters in the password
    >   <------- [I typed "mysqlrootpassword" here, without the quotes]
 * Retype the password
    >   <------- [I typed "mysqlrootpassword" here, without the quotes]
.100311 12:36:42 [Warning] The syntax '--default-character-set' is deprecated and will be removed in a future release. Please use --character-set-server instead.
100311 12:36:42 [Warning] The syntax '--skip-locking' is deprecated and will be removed in a future release. Please use --skip-external-locking instead.
100311 12:36:42 [Warning] //usr/sbin/mysqld: Option '--set-variable' is deprecated. Use --variable-name=value instead.
....ERROR 2002 (HY000): Can't connect to local MySQL server through socket '//var/run/mysqld/mysqld26445.sock' (2)
 * Loading "zoneinfo", this step may require a few seconds ...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '//var/run/mysqld/mysqld26445.sock' (2)
/var/tmp/portage/dev-db/mysql-5.1.44-r1/temp/environment: line 3734: //var/run/mysqld/mysqld10751.pid: No such file or directory
kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
 * Stopping the server ...
 * Done

#

I didn't know what the ERROR 2002 messages above signified, but pressed on regardless. Looking at the error message in retrospect, I expect it was because the MySQL Server was not running.

N.B. MySQL's 'root' user is not the same as your Linux 'root' user. You can use whatever password you want for the MySQL 'root' user. I just decided to specify "mysqlrootpassword" (without the quotes) as the MySQL 'root' user's password because I couldn't think of anything better.

Then I started the MySQL server:

Code: Select all

# /etc/init.d/mysql start
* Starting ...
* Starting  (/etc/mysql/my.cnf)
[ ok ]

and logged in to MySQL as the MySQL 'root' user in order to set up a database for Amarok:

Code: Select all

# mysql -p -u root
Enter password:  <------ [I typed "mysqlrootpassword" here, without the quotes]
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.44-log Gentoo Linux mysql-5.1.44-r1

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE amarok;
Query OK, 1 row affected (0.00 sec)

mysql> USE amarok;
Database changed
mysql> GRANT ALL ON 'amarok' .* TO 'amarok'@'localhost' IDENTIFIED BY 'mysqlamarokpassword';
Query OK, 0 rows affected (0.40 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> EXIT;
Bye
#

---------------------------

EDIT (March 12, 2010 12:39 GMT): If MySQL won't accept the MySQL 'root' user's password when you try to log-in as shown above, then you can set it again using the following command:

Code: Select all

# /usr/bin/mysqladmin -u root -h localhost password ‘mysqlrootpassword’

---------------------------

Then I edited the file /home/fitzcarraldo/.kde4/share/config/amarokrc and changed the MySQL section from this:

Code: Select all

[MySQL]
CheckCount=23
UseServer=true

to this:

Code: Select all

[MySQL]
CheckCount=1
Database=amarok
Host=localhost
Password=mysqlamarokpassword
UseServer=true
User=amarok

And now a crucial step:

Code: Select all

# chmod 775 /usr/lib/mysql

otherwise Amarok will not be able to start and will spit out the following message if you launch it from a Konsole/Terminal window:

Code: Select all

amarok: error while loading shared libraries: libmysqlclient.so.16: cannot open shared object file: Permission denied

Make sure the MySQL Server is in the default runlevel so that it will be launched automatically next time you boot:

Code: Select all

# rc-update -v show | grep mysql
                mysql |
         mysqlmanager |
# rc-update add mysql default
* mysql added to runlevel default

What a PITA. Still, it's a nice feeling when you fire up Amarok, do Tools > Update Collection and see the progress bar moving, and Tools > Cover Manager actually working.

[sabayonino]

Hi Fitz

thanks for this

I think it was enough to change only the permissions of the amarok database

Code: Select all

chmod 775 /var/lib/mysql/amarok

not all mysql folder

without changing the entire path to the database in the configuration file for mysql in case there were any other databases on /var/lib/mysql

I never deleted amarokrc and amarok+external-mysql has worked well

matter of luck?

[mikey0000]

Strange as the amarok version 2.2.2.90 in the entropy database seems to be working fine for me, with no external database unless it was setup when i installed it, currently under the database settings im using no external database.

??

want to know any package versions etc to figure this one out?

and Mysql is definitely not running.

[Fitzcarraldo]

My mistake, mikey0000, Amarok >=2.2.2.90 does not oblige the user to use an external database server; what the Amarok developers have done is to make MySQL the only possible external database server: MySQL in Amarok 2 - The Reality. Anyway, the external MySQL database server is working fine for me, so I'm happy again.

[Fitzcarraldo]

OK, an update on the procedure now that SL 5.2 and the latest versions of Amarok (2.3.0) and MySQL (5.1.45-r1) have been released, as things are slightly different.

N.B. These versions of Amarok and MySQL are not yet in the Entropy repository; I used Portage to install them. If you're an Entropy user, don't forget to use the command equo database gentoosync after you merge the packages, so that Entropy is aware what you've done.

First I installed the above-mentioned latest versions of Amarok and MySQL:

Code: Select all

# emerge -1v mysql
# emerge -1v amarok amarok-utils

^The embedded USE flag for Amarok should be off, i.e. USE="-embedded" (I didn't need to do it explicitly).

Then I set up the MySQL root user's password for the new version of MySQL:

Code: Select all

# emerge --config =dev-db/mysql-5.1.45-r1

Configuring pkg...

* Attempting to use /usr/lib/mysql
* Creating the mysql database and setting proper
* permissions on it ...
* Insert a password for the mysql 'root' user
* Avoid ["'\_%] characters in the password
    >   <------- [I typed "mysqlrootpassword" here, without the quotes]
* Retype the password
    >   <------- [I typed "mysqlrootpassword" here, without the quotes]
* Loading "zoneinfo", this step may require a few seconds ...
* Done

 #

Then I started the MySQL server:

Code: Select all

# /etc/init.d/mysql start
* Starting ...
* Starting  (/etc/mysql/my.cnf)
[ ok ]

and logged in to MySQL as the MySQL 'root' user in order to set up a database for Amarok:

Code: Select all

# mysql -p -u root
Enter password:  <------ [I typed "mysqlrootpassword" here, without the quotes]
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.45-log Gentoo Linux mysql-5.1.45-r1

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE amarok;
Query OK, 1 row affected (0.00 sec)

mysql> USE amarok;
Database changed
mysql> GRANT ALL ON amarok .* TO amarok@localhost IDENTIFIED BY 'mysqlamarokpassword';
Query OK, 0 rows affected (0.40 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> EXIT;
Bye
#

^Notice that the syntax of the GRANT command is slightly different to that in my previous post for MySQL 5.1.44-r1

And now a crucial step:

Code: Select all

# chmod 775 /usr/lib/mysql

otherwise Amarok will not be able to start and will spit out an error message if you launch it from a Konsole/Terminal window.

Then I launched Amarok, selected Settings > Configure Amarok... > Database
and entered precisely the following:

Server: localhost
Username: amarok
Password: mysqlamarokpassword
Database: amarok

Make sure the MySQL Server is in the default runlevel so that it will be launched automatically next time you boot:

Code: Select all

# rc-update -v show | grep mysql
                mysql |
         mysqlmanager |
# rc-update add mysql default
* mysql added to runlevel default

That's it.

(BTW, notice that I left datadir=/var/lib/mysql this time.)