MySQL Server Support -- Promised and Delivered

Amarok Blog

Sunday, June 21. 2009

Posted by Jeff Mitchell in jefferai

Comments (16)
Trackbacks (0)

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".

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

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.

#1 Mark on 2009-06-21 09:25 (Reply)

Is it able to take the old (amarok 1.4) database back and keep its data?
Thanks

#2 Stéphane on 2009-06-21 09:34 (Reply)

There is an upgrade capability. I don't know how it works. And this question has absolutely nothing to do with this blog post.

#2.1 jefferai on 2009-06-21 14:00 (Reply)

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+0x12a6): 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.

#3 diafero on 2009-06-21 09:52 (Reply)

This isn't the forum for bug reports. But it's fixed in SVN.

#3.1 jefferai on 2009-06-21 13:58 (Reply)

Oh, sorry. And thanks, it works

#3.1.1 diafero on 2009-06-21 14:08 (Reply)

nice one! testing and working so far!

#4 d-fens_ on 2009-06-22 10:48 (Reply)

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.

#5 tbocek on 2009-06-22 17:48 (Reply)

Thanks, Tim!

#5.1 jefferai on 2009-06-22 18:06 (Reply)

I've been waiting for this for a long time; thx! One question; will the cli amarokcollectionscanner honor these settings as well?

#6 zeroff on 2009-06-22 18:35 (Reply)

You're welcome.

As for your question, amarokcollectionscanner has never had anything to do with the database...

#6.1 jefferai on 2009-06-22 19:58 (Reply)

AWESOME. I was just lamenting the lack of remote MySQL in 2.1.

THANKS!

#7 Anonymous on 2009-06-22 19:52 (Reply)

SURE!

#7.1 jefferai on 2009-06-22 19:58 (Reply)

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?)

#8 eebie jeebie on 2009-07-09 17:30 (Reply)

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.

#8.1 jefferai on 2009-07-09 17:35 (Reply)

I wonder whether there is a way to make amarok connect to the mysql server via a unix domain socket?

#9 thomas on 2009-07-14 23:28 (Reply)

Add Comment

Name
Email
Homepage
In reply to
Comment	Enclosing asterisks marks text as bold (word), underscore are made via _word_. Standard emoticons like :-) and ;-) are converted to images. To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly. Enter the string from the spam-prevention image above:
	Remember Information? Subscribe to this entry