Befriend QColdsweat with a MySQL like database

Although this guide is specific for:

• QNAP devices,
• MariaDB database included in the firmware (QTS),
• and phpMyAdmin (respective administration tool).

It should be easily adaptable for other devices, databases or administration tools. It also assumes that you've made yourself familiar with QColdsweat's folder structure.

Before you decide to do this!

What you should know:

1. Coldsweat currently only supports MySQL/MariaDB 5.5, not any higher version.
2. Which version of MariaDB is included in QTS? Well, that's a bit unclear... For example, QTS 4.2.3 (build 20170213) includes MariaDB v5.5.51 for TS-251 and MariaDB v5.5.44 for TS-212. Generally though, the major version (e.g. 5.5) should hopefully be the same on every device. You can check your own version through step #1 below.

WARNING!
The bottom line to all this is that QColdsweat may stop working after virtually any QTS upgrade (when QNAP decides to switch to a higher version of MariaDB). Now, even if the upgrade is done for you automatically, your database might become corrupted in extreme cases because upgrade procedures are not always completely automatic. With that in mind, I urge anyone taking this route to regularly backup their MariaDB, unless installing one's own database is preferred.

Note: you probably won't succeed in installing MariaDB from source because of CMAKE dependency. That particular program is not available in either QTS or Entware. Possible solution might be cross-compilation.

Step 1: enable MariaDB

1. Open the web console of your QNAP and login.
2. Control Panel -> Application Servers -> Server SQL.
3. Enable the service and allow "TCP/IP networking". Feel free to set a non-standard port if you like.
4. Hit the Reinitialize root password and Reinitialize database buttons.
5. Whem prompted, agree to install phpMyAdmin.

Step 2: setup the database

1. Open phpMyAdmin and login:
   • Login: root.
   • Password: admin.
2. SQL tab -> Run: SET PASSWORD FOR root@localhost = PASSWORD('<your-new-password>');
   • Note: obviously, don't forget to switch <your-new-password> for your intended password.
   • Note: Changing the default password is important!
3. Make sure the default storage engine is NOT set to MyISAM.
   • Note: in short, MyISAM is old, restrictive and Coldsweat doesn't like it.
4. User accounts tab -> create a new password-enabled localhost user named coldsweat.
   • Note: leave the Create database with same name and grant all privileges option unchecked.
   • Note: apply priveleges as shown here.
5. Databases tab -> create a new database named coldsweat.
   • Note: set the appropriate utf8mb4 localized encoding. For me, it was utf8mb4_czech_ci.
6. User accounts tab again -> select the newly created coldsweat user and click on "Edit privileges". Now switch to the "Database" tab in this view and grant the user privileges over database coldsweat.

Optionally, you can initialize the coldsweat database through the "Import" tab (e.g. after you convert your SQLite database into an ".sql" script, see here).

Step 3: install mysql connector for QColdsweat

1. SSH into your QNAP.
2. Execute: opkg install python-mysql.

Coldsweat uses python-mysql to communicate with MySQL-like databases. At the time of writing, latest version of the module is 1.2.5-1 and it is compatible with:

• Python 2.7.x.
• MySQL/MariaDB 3.23 through 5.5.

Step 4: integrate QColdsweat with MariaDB

1. SSH (or sFTP) into your QNAP.
2. Locate and edit the <data-folder>/config file as follows:
   • Prepend the connection_url: sqlite:////home/coldsweat/database line with a semicolon (comment it).
   • Replace the ;connection_url: mysql://username:password@localhost/coldsweat line with connection_url: mysql://coldsweat:<your-new-password>@127.0.0.1/coldsweat where <your-new-password> is your intended password in step #2.

Step 5: restart the QPKG

Equivalent to disable/enable operations in the App Center.