MySQL via MariaDB on remote server - user creation fails

Submitted by VirtualNoob on Tue, 05/17/2016 - 06:05

Hey

I have a remote MySQL server instead of using the local install on a virtualmin node, in fact the remote MySQL server is a MaxScale SQL Proxy with a cluster of MariaDB nodes in replication. The cluster of nodes and the MaxScale config have all be tested and function as expected.

If I manually create the mysql user / host combination then the virtualmin node can connect and operate as normal.

If Virtualmin is used to set up a user, either by adding a user with DB permissions or by adding the MySQL feature to a virtual server, then creation fails. Virtualmin tries to create a user as username@hostname (and the manually configured username@10%). Because I have enabled skip-name-resolve in the MySQL servers for increased performance the creation of username@hostname does not succeed on the MySQL server, and so the subsequent attempt to modify the password of that same user fails with the error:

MySQL database failed! : SQL set password for 'newtest'@'fqdn' = password('obfuscated') failed : Can't find any matching row in the user table at /usr/libexec/webmin/web-lib-funcs.pl line 1427

In the default server template Allowed MySQL client hosts is set to 10.% as we run the MySQL cluster on private IPs and the Virtualmin nodes have an interface on the private IP too.

So it seems that I can't find a way to prevent Virtualmin from wanting to add a duplicate user for the Virtualmin node hostname, which is unnecessary for our setup and is actually causing the error. Is there a way to prevent this default action and have Virtualmin create only the user@10.% specified in the default server template?

Thanks

Status: 
Active

Comments

Submitted by JamieCameron on Tue, 05/17/2016 - 16:56

Interesting ... so this problem only happens if skip-name-resolve is enabled?

Submitted by VirtualNoob on Sun, 05/22/2016 - 01:26

Hey Jamie

Sorry for the delay in responding but yes the issue is only present when skip-name-resolve is active. I can manually create the users with 'user'@'10.%' and everything works perfectly on the MySQL cluster.

The script used by Virtualmin to create users just defaults to create a user identified by the Virtulmin hostname alongside the ones specified manually. A simple checkbox flag on the Allowed MySQL client hosts could resolve the problem, basically asking if Virtualmin should create a MySQL user at the Virtualmin hostname too.

Cheers

Submitted by JamieCameron on Sun, 05/22/2016 - 08:34

So if you enter 10.% as the allowed remote mysql hostnames, Virtualmin should for each user add three rows to the mysql.user table - one for 10.% , one for the Virtualmin system's hostname, and one for whatever IP that hostname resolves to.

It looks like that when skip-name-resolve is set, the SQL command set password for user@host can fail EVEN WHEN there is an entry in the mysql.user table for user and host. I don't fully understand why that would happen though..

Submitted by VirtualNoob on Mon, 05/23/2016 - 11:05

Yeah, so here are some useful extracts...

Virtualmin GUI output during new virtual server creation:

Creating MySQL login ..
.. MySQL database failed! : SQL set password for 'newdomain'@'weba.localfqdn' = password('afdgfghyrty') failed : Can't find any matching row in the user table at /usr/libexec/webmin/web-lib-funcs.pl line 1427.

MySQL query after that runs:

MariaDB [(none)]> select User,Host from mysql.user;
+-------------+----------------------+
| User        | Host                 |
+-------------+----------------------+
| obfuscated  | 10.%                 |
| ha_check    | 10.%                 |
| ha_root     | 10.%                 |
| newdomain   | 10.%                 |
| replication | 10.%                 |
| web_root    | 10.%                 |
| root        | 127.0.0.1            |
| root        | ::1                  |
| obfuscated  | localhost            |
| root        | localhost            |
| newdomain   | weba.localfqdn       |
+-------------+----------------------+

Is there anything else you need from me to troubleshoot further? Or is there some way to prevent the default behaviour and just have Virtualmin create users based on the address(es) provided in Allowed MySQL client hosts?

Submitted by VirtualNoob on Mon, 05/23/2016 - 11:10

Oh and during a MariaDB restart, this appears in the logs:

[Warning] 'user' entry 'newdomain@weba.localfqdn' ignored in --skip-name-resolve mode.

Submitted by JamieCameron on Mon, 05/23/2016 - 12:39

Ah .. that "[warning]" is the real source of the problem. Virtualmin expects that it can call "set password" for every user@host in the user table, but actually that isn't true :-(

Submitted by JamieCameron on Mon, 05/23/2016 - 12:42

Basically, I would recommend not using the --skip-name-resolve flag as it causes MySQL to behave in a quite unusual way.

Submitted by VirtualNoob on Wed, 05/25/2016 - 17:11

Yeah I can see the issue is caused by the setting of that flag, and that by having not set the system operates without error. However, to maximise performance our aim is to run our cluster without the additional overhead of a hostname lookup at each connection.

This could be handled quite easily by Virtualmin as far as I can tell. A checkbox flag alongside the option Allowed MySQL client hosts would give all the user control that is needed, then a conditional check at user creation / modification to either include the virtualmin hostname or not. Is this something that could potentially be included in a new virtualmin release?

Submitted by VirtualNoob on Tue, 06/21/2016 - 11:33

Is there any chance this could be addressed in an upcoming release?

Submitted by JamieCameron on Tue, 06/21/2016 - 23:57

This isn't really possible to handle in Virtualmin, because the --skip-name-resolve flag makes it impossible to know which hosts in the user table a password can actually be set for.

Submitted by VirtualNoob on Wed, 06/22/2016 - 06:02

OK, cheers for the response. I guess we'll remove the flag from the mariadb configs then.

Thanks