MySQL Server Support — Promised and Delivered
We told you it was coming. Sure, that was a while back, so you probably thought we forgot about it. Or maybe you thought we were simply playing politics, tossing empty promises to our users.
Well...you were wrong. 
It may be a bit later than planned -- we wanted to have it in time for 2.1, but it didn't happen -- but as of revision 984572, there is now support for storing an Amarok database on a MySQL server instead of the embedded MySQL database. There's no configuration dialog in the GUI yet, but it's pretty simple to set up, as explained below. All you have to do is add a few things into your amarokrc file and make a valid user on the MySQL server instance of your choice -- you don't even need to create the database yourself. (In fact, you shouldn't -- you should let Amarok create the database so we can ensure that the character set and collation are set right.)
Here's how to do it.
- Update to at least r984572 (of course, updating to the latest revision is probably your best bet).
- Wipe your build dir clean and rebuild. Not necessarily necessary, but as 47 files were changed in that commit, it's not a bad idea.
- After install, run kbuildsycoca4 --noincremental, just in case.
- On your MySQL server, run a command like: "GRANT ALL ON amarokdb.* TO 'amarokuser'@'localhost' IDENTIFIED BY 'mypassword'; FLUSH PRIVILEGES;" Be sure to substitute for "amarokdb", "amarokuser", "localhost", and "mypassword" as appropriate.
- Open up your amarokrc file, usually in ~/.kde4/share/config/amarokrc. Add a [MySQL] section:
[MySQL]
UseServer=true
Database=amarokdb
Host=localhost
Password=mypassword
User=amarokuser - Close the file and start Amarok. It should create the database and start a scan of your files. If you want to switch back to the embedded collection, simply set "UseServer" to false.
Pretty easy! Be sure to let me know if you have problems -- file a bug and assign it to "mitchell" at a domain of "kde" plus a dot plus "org".
June 21st, 2009 - 09:25
That is good news. The next step would be to have a central setting for using Databases in KDE. More and more apps (Akonadi, Digikam, Nepomuk, KGet …) are making use of SQlite/Mysql and some of them even ship the database with them.
June 21st, 2009 - 09:34
Is it able to take the old (amarok 1.4) database back and keep its data?
Thanks
June 21st, 2009 - 09:52
Since I updated to said revision, Amarok does not compile any more for me:
[ 66%] Built target amarok_collection-mysqlecollection_automoc
Linking CXX shared module ../../../lib/libamarok_collection-mysqlecollection.so
/usr/lib/mysql/libmysqld.a(client.o): In function `mysql_close_free_options’:
(.text+0xf4e): undefined reference to `yaSSL_CTX_free’
/usr/lib/mysql/libmysqld.a(client.o): In function `mysql_get_ssl_cipher’:
(.text+0×12a6): undefined reference to `yaSSL_get_cipher_name’
…
It seems some of the linker flags with the regards to mysql are not properly set – at least that is what http://forum.ubuntu-fr.org/viewtopic.php?id=289837 says about this error.
June 21st, 2009 - 13:58
This isn’t the forum for bug reports. But it’s fixed in SVN.
June 21st, 2009 - 14:00
There is an upgrade capability. I don’t know how it works. And this question has absolutely nothing to do with this blog post.
June 21st, 2009 - 14:08
Oh, sorry. And thanks, it works
June 22nd, 2009 - 10:48
nice one! testing and working so far!
June 22nd, 2009 - 17:48
Just wanted to say thank you for this and the UTF-8 support. These aren’t as sexy as e.g. moveable panels but they are absolutely great improvements and I really appreciate that someone is working on making the internals solid rather than polishing the exterior.
June 22nd, 2009 - 18:06
Thanks, Tim!
June 22nd, 2009 - 18:35
I’ve been waiting for this for a long time; thx! One question; will the cli amarokcollectionscanner honor these settings as well?
June 22nd, 2009 - 19:52
AWESOME. I was just lamenting the lack of remote MySQL in 2.1.
THANKS!
June 22nd, 2009 - 19:58
You’re welcome.
As for your question, amarokcollectionscanner has never had anything to do with the database…
June 22nd, 2009 - 19:58
SURE!
July 9th, 2009 - 17:30
I’m wondering, why not an .amarok/ folder in each folder of the collection, and inside that have an XML file with metadata for all of the music files in that folder?
I’m thinking that the use case for storing on a remote server is that you want to store the metadata along with the music anyway..
If the user moves files around –>
Store metadata based on a per-file UID so that it still applies when found in another folder. Move metadata to the correct place when discovered.
If double metadata is found for a file –>
Store a per-file timestamp with the metadata and use whatever was most recently modified.
Or is there a specific feature of MySQL databases that makes them better than XML files (indexing?)
July 9th, 2009 - 17:35
Not a good idea. For one, people don’t want their music collections cluttered up with random file crap (iTunes does it and it’s annoying as hell). Second, XML is extremely slow to read and parse. Third, then you have to come up with a system to ensure those files are up to date, which is far more difficult than doing it in a central location.
July 14th, 2009 - 23:28
I wonder whether there is a way to make amarok connect to the mysql server via a unix domain socket?