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
Comments
Submitted by JamieCameron on Tue, 05/17/2016 - 16:56 Comment #1
Interesting ... so this problem only happens if
skip-name-resolve
is enabled?Submitted by VirtualNoob on Sun, 05/22/2016 - 01:26 Comment #2
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 Comment #3
So if you enter
10.%
as the allowed remote mysql hostnames, Virtualmin should for each user add three rows to themysql.user
table - one for10.%
, 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 commandset password for user@host
can fail EVEN WHEN there is an entry in themysql.user
table foruser
andhost
. I don't fully understand why that would happen though..Submitted by VirtualNoob on Mon, 05/23/2016 - 11:05 Comment #4
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 Comment #5
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 Comment #6
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 Comment #7
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 Comment #8
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 Comment #9
Is there any chance this could be addressed in an upcoming release?
Submitted by JamieCameron on Tue, 06/21/2016 - 23:57 Comment #10
This isn't really possible to handle in Virtualmin, because the
--skip-name-resolve
flag makes it impossible to know which hosts in theuser
table a password can actually be set for.Submitted by VirtualNoob on Wed, 06/22/2016 - 06:02 Comment #11
OK, cheers for the response. I guess we'll remove the flag from the mariadb configs then.
Thanks