MySQL user problem upon creation of new domain

Submitted by Locutus on Fri, 08/17/2012 - 07:12

Apparently, at least since Ubuntu 12, the "%" host in MySQL does not encompass "localhost" anymore. At least my tests suggested that (access denied from localhost when the user only exists with "%").

When creating a new user in an existing domain, Virtualmin creates three host formst, "127.0.0.1", "%" and "localhost".

But when creating a new domain, only the "%" form gets created for the administrative user, hence trying to use it as MySQL user from local sites will fail.

Status: 
Closed (fixed)

Comments

Submitted by andreychek on Fri, 08/17/2012 - 10:03

Jamie, two users did mention this issue, but I'm not seeing it on my own Ubuntu 12.04 systems... on my system, MySQL users are being added with "localhost" as the host, rather than "%".

Is there a setting that would cause it to use one instead of the other?

Note though that I think all the Ubuntu 12.04 systems I have were upgraded from 10.04.

Submitted by JamieCameron on Fri, 08/17/2012 - 12:34

By default, Virtualmin only creates one entry with the host set to "localhost". However, this can be configured at System Settings -> Server Templates -> MySQL Database -> Allowed MySQL client hosts.

You can enter localhost % 127.0.0.1 into that field, along with any other remote hosts to allow for new domains.

Submitted by Locutus on Fri, 08/17/2012 - 17:36

Okay, Jamie is right, I had this option set to "%" in my config, since as I said, so far the "%" encompassed localhost as well.

So with this setting being nicely configurable, it might help other users avoid this issue if you placed a notice someplace prominent about the changed behavior of MySQL starting with Ubuntu 12.

Thanks for quick reply as usual! :)

Submitted by JamieCameron on Fri, 08/17/2012 - 18:38

Are you sure Ubuntu 12.04 changed this? It seems unlikely that MySQL would change the behaviour of its configuration tables.

Submitted by Locutus on Sat, 08/18/2012 - 03:45

I'm not 100% sure as in "scientifically proven", but I have strong indications:

On Ubuntu 10.04, which my production VMs are running, I have Virtualmin users with access to specific databases. In MySQL, they were created with solely "%" as hostname. And they can connect using mysql -h localhost -u username -p just fine.

On Ubuntu 12.04, which I installed on my new experimental VM, I get an "access denied" when I do the same. I have to add a second MySQL permission entry with "localhost" as hostname, only then they can connect.

I just double-checked that: On both systems, directly in Webmin, I created a MySQL user "test123" with password "test123", hostname "%". Ubuntu 10.04: Login okay. Ubuntu 12.04: Access denied. Create user "test123" with host "localhost" on Ubuntu 12.04: Login okay.

This is clearly a changed behavior, or possibly a bug, in MySQL. Ubuntu 10.04 uses MySQL 5.1.63 presently, and Ubuntu 12.04 uses version 5.5.24.

I also googled the topic and found evidence that other users also stumbled upon this.

Thing is, while the defaults of Virtualmin are set so that this issue does not present itself, it will lead to big trouble when you migrate from 10.04 to 12.04 by restoring VMin backups, and keep the old settings and database user properties. In my case, all DB users I created to specifically be used for MySQL operation would get an access denied.

Submitted by andreychek on Sat, 08/18/2012 - 09:35

Hi Locutus! Thanks for all your input.

I believe you that you're seeing that issue, but I'm struggling to reproduce it myself.

I have a user named "test" in my database. It previously had the "host" field set to localhost... I changed that to be "%":

select user,host from user where user = 'test';
+------+------+
| user | host |
+------+------+
| test | %    |
+------+------+

So there's only one record for the test user in there.

And then, on the command line, when I access it, it allows me in:

# mysql -u test -p -h localhost
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 58
Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)

Does that look similar to what you tried?

I was reading through the MySQL docs to try and discover why it was having trouble with that... I did see that, in cases where it could not resolve a hostname, it might have trouble.

So, what if you try the IP address -- 127.0.0.1, rather than the name "localhost" -- does it work in that case?

Submitted by Locutus on Sun, 08/19/2012 - 05:07

Hey Eric!

I just did another test. Created a user "test123" with host "localhost" - Login WORKS. Then I changed the host to "%" and tried:

  • "-h localhost" - Login FAILS
  • "-h 127.0.0.1" - Login FAILS
  • "-h lyra.tianet.de" (resolves to the VM's external IP) - Login WORKS
  • "-h 176.9.191.19" - Login WORKS.

I'm using 64-bit Ubuntu, in case that makes a difference. MySQL version string is exactly the same as yours.

Also, when you did your test, how did you change the host to "%"? In Webmin or using SQL commands? If the latter, did you issue a "FLUSH PRIVILEGES;" after changing? Cause the user table is not re-read unless you do that (or restart MySQL), so you might have ended up testing "localhost" twice. :)

Submitted by andreychek on Sun, 08/19/2012 - 20:39

Jamie, now that Locutus has set me straight by reminding me to run "FLUSH PRIVILEGES;", I can confirm this behavior not only on Ubuntu, but also with CentOS when using MySQL 5.5.

That is, when accessing MySQL when a user has the "Host" field set to "%", it's not accepting "localhost" or "127.0.0.1" as a valid hostname, access will be denied.

However, it does allow you to connect using another IP on the server (assuming MySQL is listening on that IP).

On CentOS, I installed this MySQL version from the IUS repository:

# mysql -V
mysql  Ver 14.14 Distrib 5.5.25a, for Linux (x86_64)

On Ubuntu, it's this version:

mysql  Ver 14.14 Distrib 5.5.24, for debian-linux-gnu (x86_64)

Submitted by JamieCameron on Sun, 08/19/2012 - 23:11

That's very interesting to know .. I am surprised that MySQL would change the meaning of a field like that, but it looks like that is actually what happened.

To handle this case in Virtualmin, I will have the MySQL restore process always grant access to localhost if % was granted previously. That should handle the issue of migration between incompatible systems..

Submitted by Locutus on Mon, 08/20/2012 - 05:03

Glad to see that I'm not seeing ghosts here. ;)

@Jamie: In addition to the modification to the restore process, you should also make sure that the user is warned when they create new domains, and the Virtualmin option we talked about earlier is set to "%" only. It should either auto-add "localhost" then or display a warning.

Submitted by JamieCameron on Mon, 08/20/2012 - 15:48

I will have Virtualmin auto-add localhost when creating a new domain as well.