[tikitiki]

Hi,

I'm expecting some trouble with my configuration of a centralised mysql database on my NAS SYNOLOGY.

I followed the wiki but kodi can't connect to mysql server :

10:50:38 T:3732 ERROR: Unable to open database: MyMusic18 [2003](Can't connect to MySQL server on '62.XX.XX.XX' (10060))
10:50:59 T:3732 ERROR: Unable to open database: MyMusic52 [2003](Can't connect to MySQL server on '62.XX.XX.XX' (10060))
10:50:59 T:3732 ERROR: Unable to create new database

I tried to modify my.cnf :

bind-adress = 0.0.0.0

my kodi advanced settings :

<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>62.XX.XX.XX</host> //it's my public address i would like to access not just in local
<port>3306</port>
<user>kodi</user>
<pass>kodi</pass>
</videodatabase>
<musicdatabase>
<type>mysql</type>
<host>62.XX.XX.XX</host>
<port>3306</port>
<user>kodi</user>
<pass>kodi</pass>
</musicdatabase>
<videolibrary>
<importwatchedstate>true</importwatchedstate>
<importresumepoint>true</importresumepoint>
</videolibrary>
</advancedsettings>

my grants for user kodi :

| Grants for kodi@% |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'kodi'@'%' IDENTIFIED BY PASSWORD '*2249595D6A53B38A77AA4AB2091D9B1A71F88735' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `MyVideos%`.* TO 'kodi'@'%' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `MyMusic%`.* TO 'kodi'@'%' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `%`.* TO 'kodi'@'%' WITH GRANT OPTION

Port 3306 is open in my router.
By the way it doesnt work with local IP either.

I searched an tried many fix but without success.
I would be very greatful if someone have an idea
Thanks

PS : Excuse my bad English

[deadcricket]

You have a typo in your config.

For port you have 3036, it should be 3306. Try fixing that and see if it helps.

[Prof Yaffle]

And 'bind-adresse' should be 'bind-address' (i.e. English spelling) - not sure if that's cut-and-paste or a retype typo, though.

[tikitiki]

Typo was good i just made a mistake in my post (I edited it).

Problem subsists.

My user 'kido' has access to mysql (tried it in ssh)

[icesurf3r]

Hoping someone can help me with a very similar problem.

I'm using a Qnap TS653 pro that I received for Christmas (which handily already has Kodi installed) to store all my media and I'm using MYSQL along side phpmyadmin to create the databases on the Qnap also.

These are the steps I have followed:

1) set up the database using the following -
CREATE USER 'kodi' IDENTIFIED BY 'kodi';
GRANT ALL ON *.* TO 'kodi';
flush privileges;

This creates the databases myvideos93 and mymusic52.

2) I've set up my advancesettings.xml, on my desktop (which I want to be the 'master' if you like - with Kodi on the Qnap and 2 Nucs running openelec as 'slaves') which reads:

Code:

<?xml version="1.0"?>

<advancedsettings>


<videodatabase>

<type>mysql</type>

<host>192.168.0.5</host>

<port>3306</port>

<user>kodi</user>

<pass>kodi</pass>

</videodatabase>


<musicdatabase>

<type>mysql</type>

<host>192.168.0.5</host>

<port>3306</port>

<user>kodi</user>

<pass>kodi</pass>

</musicdatabase>


<pathsubstitution>


<substitute>

<from>special://masterprofile/Thumbnails/</from>

<to>smb://192.168.0.5/xbmc/userdata/Thumbnails/</to>

</substitute>

</pathsubstitution>


<videolibrary>

<hideallitems>true</hideallitems>

<cleanonupdate>true</cleanonupdate>

</videolibrary>


<musiclibrary>

<hideallitems>true</hideallitems>

<cleanonupdate>true</cleanonupdate>

</musiclibrary>


<videoextensions>

<add>.f4v</add>

</videoextensions>

</advancedsettings>

3) Add movies and scrape artwork using local sources only (all my media is scraped outside of kodi, using ember)

So I can see that on Running kodi on my desktop it creates the thumbnails folder in the right place, and it creates entries in the movie database.

However, when I then copy the advancedsettings.xml to the Qnap and run Kodi, it seems unable to access the database:
full debug log here

If anyone can help, it would be really appreciated, this problem has seen me pulling what little hair I have left out since Boxing day.I am all googled out, have looked everywhere I can think of and I'm still no closer to a resolution and now my three children are starting to lose a little patience too. Would like to try and get this sorted for them so they can enjoy the Kodi experience, which was the idea of the NUCS as Christmas presents.

Apologies for the long post, but I've tried to be as thorough and provide as much info as possible.

[tikitiki]

It's not the same problem I'm unable to connect with my database.

In your case you have an accesss problem. (Access denied for user 'kodi'@'HouseServer')
Check your permissions

[Prof Yaffle]

@tikitiki... several thoughts... you have a network/connection issue AFAICT....

1. Make sure mysql/mariadb is actually started on the Synology.

2. Double-check your bind statement - I believe either bind-address = 0.0.0.0 or #bind-address = <anything> will work (i.e. comment it out entirely)

3. Both machines are on the same subnet, yes? I mean, you don't need a specific route from your client to the 62.x.x.x host?

4. See if there's anything in the Synology log files (if there are any) when the client tries to connect

5. See if there's any user-level authentication on the remote db

6. I don't think you mentioned your client OS - do you have any firewall rules preventing outbound traffic, especially if you're on Windows?

[Prof Yaffle]

@icesurf3r - if it works remotely (from your desktop) but not locally (from the QNAP) then I'd suspect the binding again. From the wiki:

Note: Replace 192.168.0.5 with the appropriate address.
Note: To allow local and remote access try comment out bind-address using a hash(#) or use bind-address = 0.0.0.0

So....

1. I presume your IP addresses are all fine, and that 0,5 is indeed HouseServer (it should be, otherwise remote wouldn't work)

2. Check that you're not binding to 0.5 in my.cnf - if so, you're excluding the loopback address (127.0.0.1) that the Qnap will be using to talk to itself. You need 0.0.0.0 or comment out the line entirely.

[icesurf3r]

Prof Yaffle,

Thanks for this. However, I'm not sure how to do this on the QNAP through phpMyadmin.

I'm guessing this is something that could be done through SSH and putty but again at a loss at how to do this. I'll start googling again, but in the meantime if you have any pointers on how to do this, it would be appreciated.

[tikitiki]

@Prof Yaffle

1. it's started i can access with mysql workbench or ssh

2. i try to bind 0.0.0.0. or comment the line. Same problems

3. yes same subnet

4. it's a NAS SYNOLOGY RN-102 don't know where to find logs

5. Why can i check that ? i can access with ssh or mysql so normally it's good for KODI too isn't it ?

6. Client Windows 10. Kodi in exception and i tried without firewall. Same problems.

[zephura]

Could you please indicate which routing rules you setup into your firewall/router dealing with SQL.
How did you configure your SQL redirection rules?

It's fine if the port 3306 is open, it's better if redirection rules are properly setup.
BTW, for security reason, you should open the port 33306 from WAN to your router
and redirect port 33306 to port 3306 of your MySQL server.

If your MySQL server is working (you can access it through its local IP),
it is a matter of traffic rather than access.

[Prof Yaffle]

@icesurf3r

Try /etc/, /etc/config/ or /etc/mysql/ - or even run find / -name my.cnf -print from the command line and search the whole system. You may also want to look for my.conf, just in case.

You could also change the setting in advancedsettings on the QNAP to point to localhost or 127.0.0.1, perhaps - explicitly tell it to look locally.

@tikitiki

Check that you don't have skip-networking set in the config file; if you do, disable/comment that as it prevents any network functions.

Re: user-level privileges... in the mysql command-line client on the Synology, connect as an admin/root user (e.g. mysql -u root -p) and show databases - you'll see a mysql database listed alongside any others. SELECT User, Host FROM mysql.user will then list all users on that database... any that are listed as localhost access can *only* connect from that device. To change this, you either need to manipulate the database through SQL (so the host field = %) or use phpMyAdmin... I think... I'm at the edge of my knowledge here...

<trying hard to keep two conversations separate here... now I know why threadjacking causes problems >

[tikitiki]

Yeah problem solved

It was in my mysql config.
I need to comment the line 'skip-external-locking'

And i change the port by default for more safety

Thanks everybody

[icesurf3r]

I've also solved my problem, after a fashion.

After all the head scratching, googling and config file altering - I simply disabled the firewall on my router. Hey Presto, everything works as it should.

Not an ideal solution, but at least I have identified the problem. I'm guessing I just need to work out what ports to open on the router so everything can communicate as it should with the firewall on?

[Prof Yaffle]

Why would a firewall on your router affect QNAP-to-QNAP traffic? That's very odd - unless your traffic is exiting the device and coming back in (so look to loopback addressing instead of 'proper' IP addressing).