MySQL user's password not set correctly by Virtualmin (empty)

VuOnline's picture
Submitted by VuOnline on Tue, 12/06/2016 - 06:33

I have recently been having problems relating to MySQL server permissions. I can not think of anything which I have changed that could have created the problem, other than a change of both the external and internal IP addresses.

I have roughly 50 virtual servers managed by Virtualmin, all using an external mysql server. All these websites are working correctly and are able to connect to their databases.

Steps to reproduce:

  1. Create a virtual server with managed db.
  2. Connect to the newly created db with the credentials supplied in step 1.
  3. Access is denied.

Or alternatively:

  1. Clone an existing virtual server without changing password.
  2. Connect to the newly created db with the new user and db but same password.
  3. Access is denied.

FTP access is always successful with the same credentials that MySQL should use. However, I have discovered that I am only able to connect to the MySQL user if I leave the password blank.

If I login to the db server as root and change the new MySQL user's password directly and then try and connect to the db from anywhere using that password, the connection succeeds.

So it appears that Virtualmin is not setting the MySQL password correctly.

Other things worth noting:

I am able to edit the database using the Edit Database tool in Virtualmin.

If, immediately after virtual server creation, I try to update the administration password in Virtualmin, I receive the output in the screenshot attached. Most notably the error: MySQL user does not exist!

Many thanks!

Files: 
Image icon screen_shot_2016-12-06_at_11.49.23.png
Plain text icon feature-mysql.pl_.txt
Status: 
Closed (fixed)

Comments

Submitted by andreychek on Tue, 12/06/2016 - 08:50

Howdy -- thanks for contacting us!

Just to clarify, you're saying all existing domains work just fine -- the issue you're experiencing is with newly created domains?

Do you know roughly when this behavior started?

Also, which Webmin and Virtualmin version are you using now? And can you paste in the output of the command "mysql -V"?

VuOnline's picture
Submitted by VuOnline on Tue, 12/06/2016 - 09:01

Hi!

Yes that's exactly correct.

As far as I am aware the behaviour may have started early last week or late the week before.

Webmin version: 1.821

Virtualmin version: 5.05

mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper

Submitted by JamieCameron on Tue, 12/06/2016 - 17:13

Do you have hashed passwords enabled on your system? Because this seems very similar to another recently reported issue (which also effects MySQL 5.7.16+) that is triggered by a combination of that version and password hashing in Virtualmin.

VuOnline's picture
Submitted by VuOnline on Wed, 12/07/2016 - 05:19

No, passwords are stored as clear text in Virtualmin, however if I login to the database and have a look at the password column for the mysql.users table, the values all appear to be hashed. But it must have worked fine this way for several months until recently.

Submitted by JamieCameron on Wed, 12/07/2016 - 22:56

Did you recently upgrade MySQL to version 5.7.16 ?

VuOnline's picture
Submitted by VuOnline on Thu, 12/08/2016 - 03:49

Hi Jamie Cameron,

That is possible yes, we did run apt update around that time but I cannot remember if MySQL was included in that.

Would you suggest we downgrade MySQL or is there a patch to MySQL/Virtualmin?

Or is there a better way to confirm the problem is caused by the latest MySQL version?

Thanks!

Submitted by JamieCameron on Sat, 12/10/2016 - 19:52

If you like, I could send you a beta version of Virtualmin that includes a fix for this issue?

VuOnline's picture
Submitted by VuOnline on Mon, 12/12/2016 - 04:34

That would be great, some instructions to safely make the update would be fantastic as well.

Thanks!

Submitted by JamieCameron on Mon, 12/12/2016 - 15:01

Sure ... are you running Virtualmin GPL or Pro, and on which Linux distribution?

VuOnline's picture
Submitted by VuOnline on Tue, 12/13/2016 - 03:09

Virtualmin Pro (50 domains) running on Ubuntu 16.04.1 LTS (GNU/Linux 4.4.0-53-generic x86_64)

How much has changed in the beta version? Of course, it is important that it is not too buggy.

VuOnline's picture
Submitted by VuOnline on Tue, 12/13/2016 - 04:09

Additionally, would you mind elaborating on the exact nature of the problem and how the beta version has a fix?

If this is an issue potentially affecting many users, would it not be best to release a patch for version 5.05 that includes just the fix?

Submitted by JamieCameron on Tue, 12/13/2016 - 17:41

I've attached an update for just the MySQL handling code in Virtualmin to this bug report - save it as /usr/{share,libexec}/webmin/virtual-server/feature-mysql.pl and then run /etc/webmin/restart , and let me know if that helps.

VuOnline's picture
Submitted by VuOnline on Wed, 12/14/2016 - 04:24

Category: Support request » Bug report

VuOnline's picture
Submitted by VuOnline on Wed, 12/14/2016 - 04:25

Hi Jamie Cameron,

Great news, although unfortunately, I cannot find the attached update, could you please point me to the link?

Thanks,

Submitted by JamieCameron on Wed, 12/14/2016 - 16:44

I've just re-attached it to this bug report.

VuOnline's picture
Submitted by VuOnline on Thu, 12/15/2016 - 04:39

Great! It works perfectly, thank you for your help.

VuOnline's picture
Submitted by VuOnline on Thu, 12/15/2016 - 04:41

Although, I do not have a /usr/libexec folder, so didn't copy it into there; assuming that doesn't matter?

VuOnline's picture
Submitted by VuOnline on Thu, 12/15/2016 - 05:29

Actually, sorry, but the patch has not worked. I even created the /usr/libexec/webmin/virtual-server/ folder and copied the file in there too. I restarted webmin. But unfortunately, virtualmin still creates new mysql passwords as empty and cannot update existing passwords.

Any ideas?

Submitted by JamieCameron on Thu, 12/15/2016 - 23:55

Oh, are you on a Debian or Ubuntu system? In that case, the file should go in /usr/share/webmin/virtual-server

VuOnline's picture
Submitted by VuOnline on Fri, 12/16/2016 - 03:38

Hi JamieCameron,

I am on Ubuntu 16.04.1 LTS (GNU/Linux 4.4.0-53-generic x86_64)

Yes, I followed your instructions and put it in that folder, as well as the libexec one.

Submitted by JamieCameron on Fri, 12/16/2016 - 23:53

Note that this patch won't fix users that already can't login to MySQL - you would need to re-do the cloning or change the password.

VuOnline's picture
Submitted by VuOnline on Mon, 12/19/2016 - 05:51

Yes when I create a new virtual server, the new mysql user's password is empty, not the same as the virtual server's password that I set. And when I try and change the password in the panel, it has no effect on the mysql user.

It appears the patch may not have fixed the issue.

Any ideas?

Submitted by JamieCameron on Tue, 12/20/2016 - 00:56

Ok, let me see if I can re-produce the exact config on your system.

What output do you get if you run grep hash /etc/webmin/virtual-server/config

Submitted by JamieCameron on Wed, 12/21/2016 - 00:47

So I did some testing on a fully updated Ubuntu 16.04 system with MySQL 5.7.16, but was able to login to the DB for newly created domains just fine.

Do you have Virtualmin configured to create a different password for MySQL for new domains?

VuOnline's picture
Submitted by VuOnline on Wed, 12/21/2016 - 09:49

Would it be possible for you to share a public key with me and I will give you access?

VuOnline's picture
Submitted by VuOnline on Wed, 12/21/2016 - 10:08

Also, do you think it might have something to do with the fact that the MySQL database server is running remotely, in AWS RDS.

Submitted by JamieCameron on Wed, 12/21/2016 - 12:34

That could be a problem. What version of the MySQL tools do you have locally, and what's the remote version?

Submitted by JamieCameron on Wed, 12/21/2016 - 17:12

I'm at a loss as to what is happening here, sorry (assuming that you are running the latest Webmin and Virtualmin versions).

VuOnline's picture
Submitted by VuOnline on Thu, 12/22/2016 - 05:27

Hi Jamie Cameron,

That's ok, I understand it is a frustrating problem and I am really grateful for your time and help to fix it.

AWS RDS Version: MySQL 5.6.27 Output of mysql -V command: mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper

Webmin version: 1.821 Virtualmin version: 5.05

Could it be the mismatch of MySQL versions?

MySQL client 5.7 is installed by default on Ubuntu 16.04, so if the version mismatch is the issue, then it is weird that the problem only cropped up recently, as the system has always been running Ubuntu 16.04 and, therefore, MySQL client 5.7

Submitted by JamieCameron on Thu, 12/22/2016 - 23:59

The cause could be the version mismatch between your system and the remote MySQL. Normally this wouldn't be a problem, but MySQL 5.7.16 changed the password hashing functions in a way that could cause exactly this problem..

VuOnline's picture
Submitted by VuOnline on Fri, 12/23/2016 - 06:02

I realise, if that is the case and the version mismatch is the issue, then the problem was not caused by Virtualmin and therefore would not normally be covered by your support. So I cannot thank you enough for going the extra mile to help.

I will downgrade MySQL to the 5.6.35 version and let you know of the result.

Mostafa's picture
Submitted by Mostafa on Fri, 12/23/2016 - 07:25

I think you need to check the file /etc/webmin/mysql/version and see if the version that is stored there matches your current running mysql server! If they don't match, just open Webmin->Servers->Mysql server so the above file is automatically updated.

I am running Centos 7 + mysql 5.7.17 from mysql official repos and they're working great for me.

Submitted by JamieCameron on Fri, 12/23/2016 - 16:48

Yeah, let us know if downgrading MySQL helps. Afterwards, go to Webmin -> Servers -> MySQL Database, and check that the version shown at the top of the page is correct.

Mostafa's picture
Submitted by Mostafa on Sat, 12/24/2016 - 02:12

No Jamie, Im talking about a different issue here. I had the same problem with my server (blank password row!) and I found the reason to be incorrect mysql version stored in /etc/webmin/mysql/version

I think Virtualmin uses this file to build proper sql records to create db users and this file is not updated unless you manually visit Webmin->Servers->MySQL Database

It would be great if Recheck-Configuration of Virtualmin checks for this as well that the version stored in /etc/webmin/mysql/version matches mysql -e 'SELECT @@version';

Submitted by JamieCameron on Sun, 12/25/2016 - 00:41

AH, I see now - Virtualmin doesn't deal properly with the case where the remote and local mysql versions differ significantly. We'll fix that though..

Mostafa's picture
Submitted by Mostafa on Sun, 12/25/2016 - 04:47

It not only affects remote mysql versions but also local mysql servers as well. I had the very similar issue when upgraded to mysql 5.7 and the issue comes from caching on this file /etc/webmin/mysql/version which should be replaced by mysql -e 'SELECT @@version'; instead.

Submitted by JamieCameron on Tue, 12/27/2016 - 00:30

Ok, the next releases of Webmin and Virtualmin will fix this issue properly.

Submitted by JamieCameron on Tue, 12/27/2016 - 00:31

Status: Active » Fixed

VuOnline's picture
Submitted by VuOnline on Thu, 01/05/2017 - 06:41

Hi there,

Thanks for your help guys. I have downgraded mysql-client on my local machine. The shortened output of dpkg -l | grep mysql now reads:

ii  libdbd-mysql-perl                 4.033-1ubuntu0.1
ii  libmysqlclient20:amd64            5.7.16-0ubuntu0.16.04.1
ii  mysql-client-5.6                  5.6.16-1~exp1
ii  mysql-client-core-5.6             5.6.16-1~exp1
ii  mysql-common                      5.7.16-0ubuntu0.16.04.1
ii  php-mysql                         1:7.1+49+deb.sury.org~xenial+2
ii  php5.6-mysql                      5.6.29-1+deb.sury.org~xenial+1
ii  php7.0-mysql                      7.0.14-2+deb.sury.org~xenial+1

I notice here that mysql-common is still at version 5.7.16, perhaps I have not downgraded all packages correctly?

And if I cat /etc/webmin/mysql/version I get: 5.6.16

Looking in Webmin > Servers > MySQL Database Server I see also MySQL version 5.6.16

My remote MySQL version is 5.6.27

And yet Virtualmin always creates mysql passwords empty.

We are totally happy to let a member of the Virtualmin team look around the server to try and figure out the issue directly as otherwise it may take too long to work back and forth with ideas considering it has already taken a month so far through nobodies fault except the nature of the issue and, I suspect, the difference in timezones.

Submitted by andreychek on Thu, 01/05/2017 - 08:31

Hmm, I'm wondering if it might be simpler just to package up new Webmin/Virtualmin versions for you that includes the fixes for this.

Jamie, are we planning releases for both of these soon? Or could we perhaps give these guys a pre-release version of it?

VuOnline, are you running Webmin and Virtualmin on both systems, or just the one?

Submitted by JamieCameron on Thu, 01/05/2017 - 15:25

The 1.830 release of Webmin should already include one half of this fix. I can send you a Virtualmin update for the other half if you like?

VuOnline's picture
Submitted by VuOnline on Tue, 01/10/2017 - 03:44

andreychek, I am running webmin and virtualmin on just the web server, not the database server.

Is this thought to be a webmin/virtualmin bug or mysql version mismatch issue?

Submitted by JamieCameron on Tue, 01/10/2017 - 15:00

It's a Webmin/Virtualmin bug triggered by a MySQL version mismatch.

VuOnline's picture
Submitted by VuOnline on Wed, 01/11/2017 - 04:09

Ok then, please send me the update when you can. Thank you

Submitted by JamieCameron on Wed, 01/11/2017 - 18:13

Are you running Virtualmin GPL or Pro, and on which Linux distribution?

VuOnline's picture
Submitted by VuOnline on Thu, 01/12/2017 - 06:06

Virtualmin Pro on Ubuntu 16.04

Submitted by JamieCameron on Fri, 01/13/2017 - 00:25

Ok, I will email you an updated Debian package.