2020-05-25, 18:47
How to centralize KODI mySQL library with Synology MariaDB10
I’m writing this ‘HowTo’ because it took me several hours to get this working and it should be done in 30 minutes. The reason is mainly because there isn’t a good howto and almost nothing about troubleshooting and the ones on the internet are mostly outdated. So, here’s my attempt.
If your experienced and in a hurry, I’ll tell you what solved it in my specific case,otherwise just read everything.
All my KODI devices logged the error: Unable to open database: MyVideos116 [2002](Can't connect to MySQL server on 'x.x.x.x' (115))
Solution: Although on a Synology DiskStation NAS the MariaDB-10 database is running on port 3307, you still have to make your KODI devices connect to port 3306! (instead of what some internet guides tell you.)
What’s the goal.
If you have a Synology DiskStation and multiple KODI devices it is very nice to centralize the KODI library in a Synology MariaDB database and they’ll all stay in sync.
Episodes you watched on one TV will show as “watched” on every KODI device. If you stop watching a movie in the living room, and resume watching in the bedroom, you can start right where you left off. Furthermore I noticed that KODI runs much faster since I’m using this setup.
Thus, Yeah, you want this!, here we go.
What you need.
Summery
In this scenario you have a LAN network 192.168.1.0/24 and all devices are in the same network.
If you have a different setup, change the IP addresses accordingly.
Here’s a short summary of what you need to do.
Synology Diskstation
Open MariaDB10 from the Package Centre.
Enable the option : Enable TCP/IP connection on port 3307 and click Apply.
Login to your Synology DiskStation and open the Package Centre.
Install phpadmin (if necessary)
Login to your Synology DiskStation, open Control Panel - Security and select the Firewall.
Make sure your firewall is enabled.
In the Firewall profile section click ‘edit rules’
Change ‘All interfaces’ to ‘LAN’ via the pull down menu
Click the ‘create’ button (In this scenario the network is 192.168.1.0, please adjust to yours.)
Your Synology firewall is now allowing 3306 network traffic from your LAN.
Enable the SSH service on port 22 and click the apply button.
(Don’t forget to disable this service again once you’re done !!)
You can’t do this via the GUI so I’ve written this down in detail.
Start PUTTY and connect to the ip-adress of your Synology DiskStation and login.
Enter the command :
Type in your password and you’ll now have full access so be careful.
Enter the command :
Enter the command :
If you do not see a file named ‘my.cnf’ enter the following commands:
Enter the command :
Now you should see the following output:
If you already have a file called my.cnf, you should edit it accordingly.
TIP: make sure the my.cnf has [-rw-r--r—] permissions (0444 and not 0777) otherwise :
chmod 0444 /var/packages/MariaDB10/etc/my.cnf
I also marked out the bind-address in the other my.cnf file at the location: /volume1/@appstore/MariaDB10/usr/local/mariadb10/etc/mysql . I’m not really sure this needs to be done but in my case it worked. Now some ‘vi’ instructions:
Enter the command :
Enter the command :
We just made a copy of the original file in case things go wrong.
Now we can safely edit the my.cnf file and 'mark out' the bind-address = 0.0.0.0 line with an # character.
To do this just enter the command :
Now use the cursors to move down to the [mysqld] section and go to the line bind-address = 0.0.0.0.
Press the ‘i’ button once. (now you’re in insert mode)
Press the ‘#’ button once. (The #-character should be inserted at the beginning of the line)
Press the ‘ESC’ button once (now you left the insert mode again into command mode)
Type in wq! and press the enter button (wq! is the command for write+quit+override)
(If you`ve screwed up, enter the command : cp my.old my.cnf , and you’re back to where you were.)
Now MariaDB10 will accept TCP connections from your local network.
** You now must restart the MariaDB service or simply just reboot your DiskStation.**
Login to your Synology DiskStation and open the Package Centre, start phpadmin and login.
I’ll describe an Windows and Android (TVbox) KODI client, but other devices should be fairly similar.
Create a new text file and paste the code below.
Save this file with the name : advancedsettings.xml
<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>192.168.1.150</host><! --CHANGE TO YOUR OWN DISKSTATION ADRES -->
<port>3306</port><! --DO NOT USE 3307 here -->
<user>kodi</user>
<pass>Pa$$w0rd!<pass><! --CHANGE WITH OWN PASSWORD -->
</videodatabase>
<musicdatabase>
<type>mysql</type>
<host>192.168.1.150</host><! --CHANGE TO YOUR OWN DISKSTATION ADRES -->
<port>3306</port><! --DO NOT USE 3307 here -->
<user>kodi</user>
<pass>Pa$$w0rd!<pass><! --CHANGE WITH OWN PASSWORD -->
</musicdatabase>
<videolibrary>
<importwatchedstate>true</importwatchedstate>
<importresumepoint>true</importresumepoint>
<cleanonupdate>true</cleanonupdate> <!-- Also clean library during library update -->
<importwatchedstate>true</importwatchedstate>
<hideallitems>true</hideallitems> <!-- removes the "*All" items from the video library -->
<hideemptyseries>true</hideemptyseries> <!-- hide empty series in the video library -->
</videolibrary>
<video>
<excludefromscan>
<regexp>\@eaDir</regexp>
<regexp>\@eadir</regexp>
<regexp>\@EADIR</regexp>
<regexp>\#recycle</regexp>
<regexp>-trailer</regexp>
<regexp>[!-._ \\/]sample[-._ \\/]</regexp>
</excludefromscan>
<excludefromlisting>
<regexp>\@eaDir</regexp>
<regexp>\@eadir</regexp>
<regexp>\@EADIR</regexp>
<regexp>\.DS_Store</regexp>
<regexp>-trailer</regexp>
<regexp>[!-._ \\/]sample[-._ \\/]</regexp>
<regexp>\#recycle</regexp>
</excludefromlisting>
</video>
<network>
<buffermode>1</buffermode> <!-- Default is 1 -->
<cachemembuffersize>52428800</cachemembuffersize> <!-- Default is 20971520 bytes -->
<readbufferfactor>2.0</readbufferfactor> <!-- Default is 1.0 -->
</network>
</advancedsettings>
Don’t forget to edit the text above to reflect the IP address of your server on your LAN and the username/password of your MariaDB database. This basic setup should get your video and music libraries synced, but you can also sync other portions of Kodi, as well as sync multiple profiles with the name tag if you use them.
Now copy this advancedsettings.xml to the userdata folder of your KODI devices.
If you already have a fully configured KODI Device that makes use of video and audio files on your Synology NAS, you want all the others to be the same. Therefore copy the following files from that KODI device to the other devices in the userdata folder as well.
(If you can’t find AppData, enable the hidden files option in the view tab.)
Copy the newly created advancedsettings.xml to this folder.
On a Android (TVBox) device:
Install a file manager APP (e.g. Total Commander)
Start the file manager APP and go to the folder : Android/data/org.xbmc.kodi/files/.kodi/userdata
Copy the newly created advancedsettings.xml to this folder. (Including the other xml files if you want).
*** Now it’s time to start KODI on your device and all should work. ***
Check that databases are created in MariaDB10
Kodi will automatically create the two new databases configured in the advancedsettings.xml.
Open phpadmin and login.
Click on the Databases tab and you should see two new databases
NOW…..For safety don’t forget to disable the SSH service again as described before.
Here are some additional KODI tips
KODI - movies - select files - long press movie - select 'add to library'
- this file contains - movies
- information supplier - The movie database
- settings - preffered language - <choose your own language> - press OK
- Press OK
- Do you want to renew all items in this location ? - YES
KODI - series- select files - long press series- select 'add to library'
- this file contains - series
- information supplier - The movie database
- settings - preffered language - <choose your own language> - press OK
- Press OK
- Do you want to renew all items in this location ? - YES
KODI - Music - select files - long press music and select add to library - full index even if exists - yes.
Troubleshooting tips.
Important log information can be found in the Kodi.log on your KODI device.
KODI Log file Windows : C:\Users\Computer\AppData\Roaming\Kodi\Kodi.log
KODI Log files ANDROID TVBOX : Android/data/org.xbmc.kodi/files/.kodi/temp/Kodi.log
MariaDB/MySQL problems.
Error : Unable to open database: MyVideos116 [2002](Can't connect to MySQL server on '192.168.1.150' (115))
ERROR 2002 (HY000): Can't connect to MySQL server on '192.168.1.100' (115)
Enter the command : cd /volume1/@appstore/MariaDB10/usr/local/mariadb10/bin
Enter the command : ./mysql --host=x.x.x.x --protocol=tcp --port=3306 -u kodi -p
(replace x.x.x.x with your DiskStation LAN ip-adress e.g. 192.168.1.150)
(don’t forget the ‘.’ Before /mysql !)
Enter the kodi password (in this example Pa$$w0rd)
If everything is configured OK, you should see :
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is x
Server version: 10.3.21-MariaDB Source distribution
Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Enter the command : status
You should see output with the following lines:
Current user: [email protected]
Connection: 192.168.1.x via TCP/IP
To Quit -> Enter the command : quit
If you get an error like ERROR 1045 (28000): Access denied for user 'kodi'@x.x..x.x' (using password: NO), there is an obvious problem with the kodi user account, delete it and start again.
If you get an error like ERROR 2002 (HY000): Can't connect to MySQL server on '192.168.1.150' (115), the Database is not accepting connection from the LAN network. Try connecting to the localhost using the command : ./mysql --host=127.0.0.1 --protocol=tcp --port=3306 -u kodi -p
ERROR Connection refused
Start PUTTY, connect to your DiskStation on port 3306 using TELNET
If you get Connection refused it’s not ok. The MariaDB configuration or firewall is not enabled to accept connections from the (LAN) network. If you get Connection reset by peer its ok.
Configure php extensions
Login to your Synology DiskStation and open Web Station.
In Web Station > PHP Settings > Profile > Edit > Core tab Search > search for ‘socket’
mysqli.default_socket > Value : /run/mysqld/mysqld10.sock
pdo.mysqli.default_socket > Value : /run/mysqld/mysqld10.sock
In Web Station > PHP Settings > Profile > Edit > Core tab Search > search for ‘port’
mysqli.default_port > Value : 3306
I’m writing this ‘HowTo’ because it took me several hours to get this working and it should be done in 30 minutes. The reason is mainly because there isn’t a good howto and almost nothing about troubleshooting and the ones on the internet are mostly outdated. So, here’s my attempt.
If your experienced and in a hurry, I’ll tell you what solved it in my specific case,otherwise just read everything.
All my KODI devices logged the error: Unable to open database: MyVideos116 [2002](Can't connect to MySQL server on 'x.x.x.x' (115))
Solution: Although on a Synology DiskStation NAS the MariaDB-10 database is running on port 3307, you still have to make your KODI devices connect to port 3306! (instead of what some internet guides tell you.)
What’s the goal.
If you have a Synology DiskStation and multiple KODI devices it is very nice to centralize the KODI library in a Synology MariaDB database and they’ll all stay in sync.
Episodes you watched on one TV will show as “watched” on every KODI device. If you stop watching a movie in the living room, and resume watching in the bedroom, you can start right where you left off. Furthermore I noticed that KODI runs much faster since I’m using this setup.
Thus, Yeah, you want this!, here we go.
What you need.
- Synology DiskStation running 6.2 and MariaDB 10.
- KODI devices (in this case a Windows laptop and an Android 9 TVBOX, both with KODI 18.6)
- Putty (download at www.putty.org )
- Time (not for sale)
- a bit of Linux and Windows knowledge.
Summery
In this scenario you have a LAN network 192.168.1.0/24 and all devices are in the same network.
If you have a different setup, change the IP addresses accordingly.
Here’s a short summary of what you need to do.
- Synology DiskStation : install MariaDB10 and enable TCP/IP connection on port 3307.
- Synology DiskStation : open port 3306 in the Synology firewall
- Synology DiskStation : install phpadmin and configure kodi user
- Synology DiskStation : enable SSH and configure MariaDB10 for Remote Client Access
- KODI: Configure Kodi devices to use your new MariaDB Database server.
Synology Diskstation
- MariaDB
Open MariaDB10 from the Package Centre.
Enable the option : Enable TCP/IP connection on port 3307 and click Apply.
- Phpadmin
Login to your Synology DiskStation and open the Package Centre.
Install phpadmin (if necessary)
- Firewall
Login to your Synology DiskStation, open Control Panel - Security and select the Firewall.
Make sure your firewall is enabled.
In the Firewall profile section click ‘edit rules’
Change ‘All interfaces’ to ‘LAN’ via the pull down menu
Click the ‘create’ button (In this scenario the network is 192.168.1.0, please adjust to yours.)
- PORT SECTION: Select custom Type = destination port, Protocol = TCP, Port = 3306 (not 3307)
- SOURCE IP : Select Specific IP – IP Range : from 192.168.1.1 to 192.168.1.254 and click OK
- ACTION : allow and click OK
Your Synology firewall is now allowing 3306 network traffic from your LAN.
- Enable SSH service
Enable the SSH service on port 22 and click the apply button.
(Don’t forget to disable this service again once you’re done !!)
- Synology MariaDB – enable connections from the (LAN) network
You can’t do this via the GUI so I’ve written this down in detail.
Start PUTTY and connect to the ip-adress of your Synology DiskStation and login.
Enter the command :
Code:
sudo su –
Enter the command :
Code:
cd /var/packages/MariaDB10/etc
Code:
ls –al
Enter the command :
Code:
echo [mysqld] > my.cnf
echo skip-networking=0 >> my.cnf
echo skip-bind-address >> my.cnf
cat my.cnf
Code:
[mysqld]
skip-networking=0
skip-bind-address
TIP: make sure the my.cnf has [-rw-r--r—] permissions (0444 and not 0777) otherwise :
chmod 0444 /var/packages/MariaDB10/etc/my.cnf
I also marked out the bind-address in the other my.cnf file at the location: /volume1/@appstore/MariaDB10/usr/local/mariadb10/etc/mysql . I’m not really sure this needs to be done but in my case it worked. Now some ‘vi’ instructions:
Enter the command :
Code:
cd /volume1/@appstore/MariaDB10/usr/local/mariadb10/etc/mysql
Code:
cp my.cnf my.old
We just made a copy of the original file in case things go wrong.
Now we can safely edit the my.cnf file and 'mark out' the bind-address = 0.0.0.0 line with an # character.
To do this just enter the command :
Code:
vi my.cnf
Now use the cursors to move down to the [mysqld] section and go to the line bind-address = 0.0.0.0.
Press the ‘i’ button once. (now you’re in insert mode)
Press the ‘#’ button once. (The #-character should be inserted at the beginning of the line)
Press the ‘ESC’ button once (now you left the insert mode again into command mode)
Type in wq! and press the enter button (wq! is the command for write+quit+override)
(If you`ve screwed up, enter the command : cp my.old my.cnf , and you’re back to where you were.)
Now MariaDB10 will accept TCP connections from your local network.
** You now must restart the MariaDB service or simply just reboot your DiskStation.**
- Synology MariaDB – create and configure kodi user
Login to your Synology DiskStation and open the Package Centre, start phpadmin and login.
- Select the users tab
- Click add user account
- Username = kodi
- Server name = %
- Password = Pa$$w0rd! (You can choose your own password, but must use a complex password)
- Enable the option select all privileges on wildcard name (username\_%)
- Click the Select all next to the Global privileges option
- Scroll down and press Go.
- Configure KODI Devices with advancedsettings.xml
I’ll describe an Windows and Android (TVbox) KODI client, but other devices should be fairly similar.
Create a new text file and paste the code below.
Save this file with the name : advancedsettings.xml
xml:
<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>192.168.1.150</host><! --CHANGE TO YOUR OWN DISKSTATION ADRES -->
<port>3306</port><! --DO NOT USE 3307 here -->
<user>kodi</user>
<pass>Pa$$w0rd!<pass><! --CHANGE WITH OWN PASSWORD -->
</videodatabase>
<musicdatabase>
<type>mysql</type>
<host>192.168.1.150</host><! --CHANGE TO YOUR OWN DISKSTATION ADRES -->
<port>3306</port><! --DO NOT USE 3307 here -->
<user>kodi</user>
<pass>Pa$$w0rd!<pass><! --CHANGE WITH OWN PASSWORD -->
</musicdatabase>
<videolibrary>
<importwatchedstate>true</importwatchedstate>
<importresumepoint>true</importresumepoint>
<cleanonupdate>true</cleanonupdate> <!-- Also clean library during library update -->
<importwatchedstate>true</importwatchedstate>
<hideallitems>true</hideallitems> <!-- removes the "*All" items from the video library -->
<hideemptyseries>true</hideemptyseries> <!-- hide empty series in the video library -->
</videolibrary>
<video>
<excludefromscan>
<regexp>\@eaDir</regexp>
<regexp>\@eadir</regexp>
<regexp>\@EADIR</regexp>
<regexp>\#recycle</regexp>
<regexp>-trailer</regexp>
<regexp>[!-._ \\/]sample[-._ \\/]</regexp>
</excludefromscan>
<excludefromlisting>
<regexp>\@eaDir</regexp>
<regexp>\@eadir</regexp>
<regexp>\@EADIR</regexp>
<regexp>\.DS_Store</regexp>
<regexp>-trailer</regexp>
<regexp>[!-._ \\/]sample[-._ \\/]</regexp>
<regexp>\#recycle</regexp>
</excludefromlisting>
</video>
<network>
<buffermode>1</buffermode> <!-- Default is 1 -->
<cachemembuffersize>52428800</cachemembuffersize> <!-- Default is 20971520 bytes -->
<readbufferfactor>2.0</readbufferfactor> <!-- Default is 1.0 -->
</network>
</advancedsettings>
Don’t forget to edit the text above to reflect the IP address of your server on your LAN and the username/password of your MariaDB database. This basic setup should get your video and music libraries synced, but you can also sync other portions of Kodi, as well as sync multiple profiles with the name tag if you use them.
Now copy this advancedsettings.xml to the userdata folder of your KODI devices.
If you already have a fully configured KODI Device that makes use of video and audio files on your Synology NAS, you want all the others to be the same. Therefore copy the following files from that KODI device to the other devices in the userdata folder as well.
- guisettings.xml
- sources.xml (This file contains information where your video and music files are located)
- RssFeeds.xml
- profiles.xml
(If you can’t find AppData, enable the hidden files option in the view tab.)
Copy the newly created advancedsettings.xml to this folder.
On a Android (TVBox) device:
Install a file manager APP (e.g. Total Commander)
Start the file manager APP and go to the folder : Android/data/org.xbmc.kodi/files/.kodi/userdata
Copy the newly created advancedsettings.xml to this folder. (Including the other xml files if you want).
*** Now it’s time to start KODI on your device and all should work. ***
Check that databases are created in MariaDB10
Kodi will automatically create the two new databases configured in the advancedsettings.xml.
Open phpadmin and login.
Click on the Databases tab and you should see two new databases
- MyMusic72 (The name will differ with KODI release)
- MyVideos116 (The name will differ with KODI release)
NOW…..For safety don’t forget to disable the SSH service again as described before.
Here are some additional KODI tips
KODI - movies - select files - long press movie - select 'add to library'
- this file contains - movies
- information supplier - The movie database
- settings - preffered language - <choose your own language> - press OK
- Press OK
- Do you want to renew all items in this location ? - YES
KODI - series- select files - long press series- select 'add to library'
- this file contains - series
- information supplier - The movie database
- settings - preffered language - <choose your own language> - press OK
- Press OK
- Do you want to renew all items in this location ? - YES
KODI - Music - select files - long press music and select add to library - full index even if exists - yes.
Troubleshooting tips.
Important log information can be found in the Kodi.log on your KODI device.
KODI Log file Windows : C:\Users\Computer\AppData\Roaming\Kodi\Kodi.log
KODI Log files ANDROID TVBOX : Android/data/org.xbmc.kodi/files/.kodi/temp/Kodi.log
MariaDB/MySQL problems.
Error : Unable to open database: MyVideos116 [2002](Can't connect to MySQL server on '192.168.1.150' (115))
ERROR 2002 (HY000): Can't connect to MySQL server on '192.168.1.100' (115)
- Check what port mysql is running on
PUTTY command : netstat anp | grep mysqld
The last column shows you that mysqld bound itself to port 3306 listening on all interfaces.
- Check there is a listener on mysql port
PUTTY command : netstat ln | grep mysql
You should see a listener with /run/mysqld/mysqld10.sock
- Check if there is any network traffic when starting KODI
PUTTY command : tcpdump port 3306
Start KODI and you should see a lot of IP traffic rushing over your screen
- Test the database connection using putty on 127.0.0.1 and 192.168.1.x
Enter the command : cd /volume1/@appstore/MariaDB10/usr/local/mariadb10/bin
Enter the command : ./mysql --host=x.x.x.x --protocol=tcp --port=3306 -u kodi -p
(replace x.x.x.x with your DiskStation LAN ip-adress e.g. 192.168.1.150)
(don’t forget the ‘.’ Before /mysql !)
Enter the kodi password (in this example Pa$$w0rd)
If everything is configured OK, you should see :
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is x
Server version: 10.3.21-MariaDB Source distribution
Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Enter the command : status
You should see output with the following lines:
Current user: [email protected]
Connection: 192.168.1.x via TCP/IP
To Quit -> Enter the command : quit
If you get an error like ERROR 1045 (28000): Access denied for user 'kodi'@x.x..x.x' (using password: NO), there is an obvious problem with the kodi user account, delete it and start again.
If you get an error like ERROR 2002 (HY000): Can't connect to MySQL server on '192.168.1.150' (115), the Database is not accepting connection from the LAN network. Try connecting to the localhost using the command : ./mysql --host=127.0.0.1 --protocol=tcp --port=3306 -u kodi -p
ERROR Connection refused
Start PUTTY, connect to your DiskStation on port 3306 using TELNET
If you get Connection refused it’s not ok. The MariaDB configuration or firewall is not enabled to accept connections from the (LAN) network. If you get Connection reset by peer its ok.
Configure php extensions
Login to your Synology DiskStation and open Web Station.
In Web Station > PHP Settings > Profile > Edit > Core tab Search > search for ‘socket’
mysqli.default_socket > Value : /run/mysqld/mysqld10.sock
pdo.mysqli.default_socket > Value : /run/mysqld/mysqld10.sock
In Web Station > PHP Settings > Profile > Edit > Core tab Search > search for ‘port’
mysqli.default_port > Value : 3306