Replacing SQLite with MySQL

Important

This page is applicable to pre-6.5.0 versions of SlimServer only (such as 6.2.x or 6.3.x). If you're looking for information on MySQL for SlimServer version 6.5.0 or later then you want this other page.

Replacing SQLite with MySQL (SlimServer 6.3.x or Earlier)

MySQL, an /open source/ database, is not officially supported by SlimDevices.

However, support for its use in SlimServer is pretty easy to get going. Users have reported increased performance especially during rescans and complex searches.

It's recommended that you use MySQL 4.1 or above.

Once you have a working installation of MySQL, you need to create a database for SlimServer and a user to be used to access it. Log in to mysql as root and issue the following (assuming both your database and the user are called slimserver, the part in brackets is optional):

create database slimserver;
grant all on slimserver.* to slimserver [identified by 'password'];

If your Music library contains non-ASCII characers in file or folder names, you should specify the character set encoding you use. For example, if you use UTF-8 encoded Unicode, replace the create database line with

create database slimserver character set utf8;

Next, in your SlimServer pref file, look for the line "dbusername". It should be blank for basic usage with SQLite.

To use with MySQL, change the entries to look like this:

dbusername: YourDBUser
dbpassword: YourDBPass
dbsource: dbi:mysql:database=YourDBName

This assumes your MySQL installation is running on the same host SlimServer is. If you left the password blank for the database user, just set dbpassword to .

See the DBD::mysql documentation for more specific DBD driver information.

For Windows platform without Perl installed: Get the latest DBD-mysql from ActiveState... http://ppm.activestate.com/PPMPackages/zips/8xx-builds-only/Windows/ Unzip it and then unpack the tar.gz file in the MSWin32-x86-multi-thread-5.8 folder. The files in the resulting blib/arch folder should be copied to server/CPAN/arch/5.8/MSWin32-x86-multi-thread and those in blib/lib folder should be copied to server/CPAN. This will make them available to SlimServer. (see: http://forums.slimdevices.com/showthread.php?p=33379#post33379).

On Linux or Unix, simply type 'perl -MCPAN -e shell' from the command line and then an 'install DBD::mysql'. You may need to do an 'install DBI' first if DBD::mysql gives errors.

To avoid db connection timeouts (Bug 947) add the following to your /etc/my.cnf file:

wait_timeout = 31536000

Default wait_timeout value is 28800 (8 hours), max value is 31536000 (1 year).

Note that this bug is claimed fixed (see Bug 947) in current beta versions of SlimServer 6.2 and so this workaround may no longer be necessary depending on which version you are using.

On Windows, if you are running SlimServer and MySQL as services you may have problems if the SlimServer service trys to start before the MySQL service is running. To avoid this (and make the SlimServer service wait for the MySQL service) add the following value to your Registry using regedit:

Key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\slimsvc\Value 
Name: DependOnService
Value Type: REG_MULTI_SZ
Value Data: MySQL

At this stage you need to load the SlimServer database schema into your empty MySQL database. To do this you should restore SQL/mysql/dbcreate.sql file into your database using a tool like MySQL Administrator.

Finally, restart slimserver. If all went well, a rescan should be triggered and the new database will be populated. Check the slimserver log file to make sure it doesn't contain any errors. Running slimserver from the command line will let you see any MySQL or other startup errors during testing. You could also log into mysql and see if the tables have been created. Don't be too concerned if they appear empty - you may not be able to see any contents until the rescan has completed.

The schema for MySQL is kept in the SQL/mysql/ directory, relative to your SlimServer home directory. You only need to look at this if you're interested!