MariaDB version 10 & NAS

Hi I have a NAS (Synology DS412) which has many applications which can be installed and used - one being MariaDB 10.

HeidiSQL says it can connect with MariaDB 10 but I can't get it to do this (see attached). Looking at the help link, the error seems to indicate that MariaDB will only accept an IP of 127.0.0.1 whereas my NAS is on the 192.168.0.xx IP range which home networks often use.

There are steps to take to resolve this but it means accessing some configuration files, which I've no idea how I can do that on my NAS (I've asked Synology about this).

Any thoughts as to how I can resolve this connectivity issue would be appreciated.

You need to connect to the MariaDB instance once and change that root account, to make it allow IPs other than 127.0.0.1. I suppose Synology also provides some client for MariaDB, maybe a web based client?

Yes, it provides phpMyAdmin (on the NAS) which works fine. It's just that I need to get into the NAS to use phpMyAdmin and I'd prefer using HeidiSQL as I also connect to several other MSSQL database elsewhere.

The Synology help people are suggesting the issue is with HeidiSQL method of connecting, which is very unhelpful as it's clear to me MariaDB is the problem, restricting access to 127.0.0.1 IP initially. And they haven't givem me a clue as to access the MariaDB configuration files.

Ok, in phpMyAdmin, you should be able to run a custom query, so you could create a new user like this:

CREATE USER 'root'@'192.168.%' IDENTIFIED BY 'yOurPassW0rd';
GRANT EVENT, DROP, DELETE, CREATE VIEW, CREATE TEMPORARY TABLES, CREATE TABLESPACE,
  CREATE ROUTINE, CREATE, ALTER ROUTINE, ALTER, SHOW VIEW, SHOW DATABASES, SELECT,
  PROCESS, EXECUTE  ON *.* TO 'root'@'192.168.%';
FLUSH PRIVILEGES;

Hi Thanks for this I'll give this a go. However, Synology came back with something similar, creating a new User from phpMyAdmin using the UI, which seemed to go OK. So I now have a new User with Host set as 192.168.0.12 (see attached screenshot). However, when, in HeidiSQL, I press Open I get the error message shown - which is saying Host 192.168.0.28 is not allowed....! Where does it get .28 from when the IP named is .12? Very Odd - HeidiSQL seems to have an incorrect IP address it picks up. I've closed down and opened it again several times and no change.

Where does it get .28 from when the IP named is .12

The .28 is yours, from the client side. The .12 is on the server side.

So I now have a new User with Host set as 192.168.0.12

This is wrong, you have to set the account's ip to 192.168.0.28, or to wildcard 192.168.%.

Worked a treat!

Many thanks for your help, great support!

Nice thread. May I attach to it? This is actually exactly the same issue that I'm currently facing.

Same situation:

• MariaDB and phpMyAdmin on my Synology
• HeidiSQL on my PC

I have no access from HeidiSQL with none of the users (root, Rezept_DB, Simons_Bikeservice) though their host is set with wildcard.

I'm wondering if this is related to the host adress stated in clear text in the error window ('root'@'Desktop-PC.fritz.box' Can anybody give me a hint what I missed to setup properly?

Thanks for any ideas.

Best Regards Simon

Note your both users with "%" host don't have global privileges. I cannot see if they have database privileges, but if those are missing, then this is likely the problem.

The "root" user with host "198.168.%" probably doesn't match because the server is resolving IP addresses. Not sure. You may create (with phpMyAdmin) another "root" user with host set to "Desktop-PC.fritz.box" to test that. Alternatively, change the ip wildcard "198.168.%" to "%".

Thanks for the hint.

Yes that's right. The specific users have not global privileges, only specific ones in their repsonding databases.

The root user's host was set to work only in internal environment. However, it doesn't solve the issue when host is set to '%' or even to 'Desktop-PC.fritz.box':

Now I have set a user root defined for 3 hosts and none of them will allow the connection. Is there any other setting that can deny the connection?

Could it be you just provide a wrong password in the connection? That would at least give exactly that "Access denied" error.

Thank you very much! That was the right hint.

But not, that I wrote it wrong. This was quite unlikely as I took it all the time from my Password Manager and pasted it into the field - from creating the user up to HeidiSQL to connect it.

The password was so safe with such specical signs, that I assume it wasn't transferred or interpreted correctly ('\x"^"0#ikI.w'). When I now changed it to a simpler version it worked.

Thanks again, you safed my Sunday. :-)

Best Regards Simon