This article explains how to use MySQL as a backend storage on a NAS for mAirList.(Sorry, screenshots in German.)
MySQL is an open source database management system. Being free of charge, fast and networking-enabled, MySQL can well serve as the backend for any networked mAirListDB installation, but be aware of using MySQL in a multi-user environment (where two or more mAirList computers or processes will share the same database) because of some issues regarding the multi-user functionality. In this case we strongly encourage you to use [[setup-postgresql|PostgreSQL]] instead!
Download libmysql.dllhere or here*. Downloading directly from mysql.com doesn’t necessarily prove successful. Use the 32-bit version, please. Copy her into the directory containing mairlist.exe.
Install the MySQL-Server on your NAS.
NAS-systems by QNAP have this preinstalled; the access has to be activated only.
On Synology-NAS install the package MariaDB, v5 is fine. However, if you choose MariaDB v10, a little more care must be taken while configuring mAirList later on. Choose a password for the root-user during install.
Create a new user, called „mairlist“ for simplicity or any other name you prefer and a password for it. Lower case names work best. This user should be granted access from any client within your network.
Create a new database, called „mairlist“ for simplicity or any other name you prefer. Lower case names work best. Make sure the Collation is utf8_general_ci. The new user created just before should be granted authorization to write to and change this database (notadministration). This step can be executed by checking Create new database … in the Create User-Dialogue we’ve seen right before. If you forgot to check, use the following page.
On with mAirList: In the configuration menu choose Database > „Add“ > Networked (MySQL). For Host name choose the IP-address corresponding to your NAS, without the http:// – i.e. 192.168.xxx.yyy or whatever your intranet is constructed like. Name is the name of the database you have created one moment ago. As Default login choose the username for the database you have set before and its respective password.
Press Test login (back in the Tab Connection). In case of error messages, check your connection to your NAS, particularly regarding her firewall (such as pinging her), or check the correct location of libmysql.dll. If everything has gone right so far, you will get sort of a „yes, but …“-message …
… which is normal by now, because the freshly created database is lacking any structure yet. To fix this, change to the Setup-Tab and press the button Transfer database structure. mAirList will be constructing the required pattern and notify you if finished.
Now connect mAirList to the new database: Open Database in mAirList and choose the location(s) of your audio files. Having them saved on your NAS, their respective directory has to be mounted to your mAirList computer first.
Synchronize your database. Depending on the extent of your audio archive, this process may take quite a while. Unchecking the Auto-cue-feature can save some time here.
Installed regards
TSD
* Please note the annotation by @Torben regarding the download oflibmysql.dll:
Exactly. Unfortunately your screenshot doesn’t show how you do it But it’s actually pretty simple, just enter Port=3307 under Advanced Connection Options.
In case you wonder why MariaDB is running on port 3307 anyway - it’s because Synology allows you to install and run the original MySQL server and MariaDB side by side (something you would not do on a regular Linux server – most Linux distributions now ship with MariaDB instead of MySQL, and actually use MariaDB when you install the mysql-server package on your server). So it uses a different default port for MariaDB to avoid conflicts.
Another thing, please note that it might not be legal to use the libmysql.dll from the original MySQL package unless you have a commercial license of MySQL. If you want to make sure that you don’t violate any software licenses, use the client DLL from MariaDB.
Thank you for your tutorial. I tried to setup a DB on my Synology. I’m using MariaDB 5, i copied the dll in the mairlist folder and checked the new Databaseconnection. Everything is fine, but when i try to clone my SQLite-DB (local DB) into the MariaDB, i get after 25% this error (screenshot).
What is wrong there? I didn’t find any entry with “514-Srache”
The error occurs where there is more than one value stored for the same attribute of an item in the library, e.g. when you have both “Year=1977” and “Year=1978”. This is something that should not happen normally, but a bug im some old versions may have caused this.
The SQL database schema has a rule (UNIQUE constraint) that should avoid this, but SQLite does not enforce it, so you only see these errors when converting to a different system.
To find out which items/attributes are affected, you can issue this SQL command in the SQL console in the About menu:
SELECT item, name FROM item_attributes GROUP BY item, name HAVING COUNT(*) > 1 ORDER BY item, name;
If it’s too many to fix manually, it is also possible to batch delete the double attributes with an SQL query. Let me know.
My mairlist-DB is now running on a Synology DS214-Play.
But i have some performance issues. A DB request takes a little time, but the DB-answer comes then very quickly. If i try to open a DB entry from a song (for cueing etc.) it takes very long. Does anyone have the same problems and, if necessary, a solution or idea?
I have a similar behavior, with the local databes, that is SQLite.
On my Server I’m running PostgreSQL and it works just fine, with no performance issue.
mAirlist is running in a KVM Virtualized, and the PostgreSQL is running inside a LXC Linux container on the same hardware machine.
It seems there is no actual package, you can just download and install. Doesn’t this NAS support any Container like docker or LXC?
On my qunap NAS I had both available and I chose LXC, because I found it is much easier to use. It behaves like an independent virtual Linux Server and you can just ssh into it and install any Linux component.
I did not become a friend of Docker (yet) but it should be possible to do something similar and rund PostgreSQL.
After killing the saving pig I upgraded to the pro version and made the database on the nas.
All went well except one thing on the main pc is a seccond harddisk that has a copy of the music files on the nas.
In the database I redirect to the H disk (copy) but when I open a playlist on a laptop (demo) it says all the music is on H instead of the nas.
Is this a setting or is it not possible to redirect on one pc.
My thoughts were the pc is quicker on loading from the harddisk as trough the network.
If I place ramps both pc and laptop read the database.
Done that, but only on the mairlist pc because the laptop has only access to the nas directly.
And if I save a playlist (on the main pc) the laptop can’t open it because it doesn’t have access to the H disk.
But it’s actually pretty simple, just enter 
– until now, thank you, 
