Setting Up mAirListDB With MySQL On a NAS

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.dll here 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.

  • Make sure that the NAS’s firewall is allowing internal traffic over port 3306 (or 3307, if MariaDB v10 is used).

  • Create a new database and a user for it. For this purpose, the programme phpMyAdmin should be used.

    • On QNAP-systems, activate the preinstalled package.

    • On Synology devices install the respective package.

  • Login as root with the password you set before.

  • 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 (not administration). 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.

  • So far, the NAS is prepared by now.

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

  • If you deal with MariaDB v10, the port has to be set to 3307 in the Advanced-Tab under Additional options ….

  • 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 of libmysql.dll:


Edit: Screenshot-Issue fixed.

Thanks for the tutorial, TSD! Just one note:

Exactly. Unfortunately your screenshot doesn’t show how you do it :wink: 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.

Yes, this is because I wasn’t exactly sure how to do – until now, thank you, @Torben. I will fix that. I have fixed that.

Always learning regards

TSD

1 Like

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”

Thank you and best regards, Timo

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.

Thank you, that worked for me :slight_smile:

This also works with a home license?

It doesn’t, I’m afraid. Networked databases is a feature reserved to the Professional Edition.

Limited regards

TSD

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?

Thanks and best regards, Timo

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.

Hello Timo, my mAirList PostgreSQL database is running on a Synology DS213 (my best buy ever!).

No performance issues at all. Which database are you running?

Hello, i’m using MariaDB5. How did you install PostgreSQL? I haven’t seen this package in my synology…

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.

Docker should be easily available on the Synologies within the package manager.

Contained regards

TSD

@Timo I am using the internal PostgreSQL database from Synology. See this topic:

https://community.mairlist.com/t/upgrade-mairlist/9538

It’s some sort of hack, but works very well for me.

Thank you all, i’ll give it a try :slight_smile: