MySQL 5.7: User Changes

Wow ... that changes everything.

Does Ubuntu 14.04's MySQL package use version 5.7, or did you install this from another source?

No, that would have come from another source.

Ubuntu 14.04 provides MySQL 5.5.x.

"In MySQL 5.7, the password field in mysql.user table field was removed, now the field name is 'authentication_string'."

http://stackoverflow.com/questions/30692812/mysql-user-db-does-not-have-...

I think I will need to add code to detect the MySQL version and use the new password field.

It would be good to check the entire list of changes on mysql. You can't change passwords and stuff as easily as before. They have a new set password syntax that has to be used.

It seems DBD::mysql does have an option to check the server version to alter how you change passwords etc.

Are any Linux distributions including MySQL 5.7 by default? It seems like when that happens, a lot of existing installs are going to be broken or need a fairly complex upgrade to rename the password column.

None that I had seen BUT the speed differences are WELL worth the upgrade. It was no piece of cake but again well worth the effort.

No, not yet.

The most recent Linux distribution supported by Virtualmin is Debian 8, and that comes with MySQL 5.5.46.

The next distribution to make a release that Virtualmin will support is Ubuntu 16.04 (which comes out in April 2016). It's still early on in that release cycle, but so far they're testing MySQL 5.6.25 with it.

That may work but it seems from the documentation that they want you to use the

set password for 'xxx'@'xxx' = 'xxxxxx';

I tinkered with using the field name with update statements and password('xxx') but it seemed a bit flakey to me. YMMV.

A quick and dirty fix for anyone who needs it:

In /usr/share/webmin/virtual-server/feature-mysql.pl

Search for "# Query local MySQL server"

Line 1530:

 <span class="geshifilter"><code class="text geshifilter-text">local $d = &amp;mysql::execute_sql($mysql::master_db, &quot;select user.user,user.password from user,db where db.user = user.user and (db.db = '$db' or db.db = '$qdb')&quot;);</code></span>

needs to be changed to

 <span class="geshifilter"><code class="text geshifilter-text">local $d = &amp;mysql::execute_sql($mysql::master_db, &quot;select user.user,user.authentication_string from user,db where db.user = user.user and (db.db = '$db' or db.db = '$qdb')&quot;);</code></span>

Thanks to the fix that upstairs provided but there are just more...

Such as here:

Creating MySQL login .. .. MySQL database failed! : SQL insert into user (host, user, password, ssl_type, ssl_cipher, x509_issuer, x509_subject) values ('localhost', '', password(''), '', '', '', '') failed : Unknown column 'password' in 'field list' at ../web-lib-funcs.pl line 1397.

This will take a fair bit of work to fix, but hopefully it will be done by the next Webmin release - I will update this ticket with details.

@richardevs, I noticed this after posting; and modded the appropriate files and now have no issues. It also helps to have mysql not running in strict mode. If anyone needs modified source get at me on jp(at]txt3 dotnet.

Re changing virtualmin code would it not be enough to check for whether password or authentication_string exist and substitute in the sql queries? Small performance hit...

Right, it's not technically a huge challenge - there's just quite a few places in the Webmin and Virtualmin code to update.

I don't do perl and I mentioned earlier about the library exposing the version of MySQL, can't you to a ternary expression in the code like

select ... from ... where (mysql_version < 5.7 ? 'password' : 'authentication_string') ...

Seems like it might be an easy fix but as you say, if it appears in a lot of places, it'll take a while.

I hit the same related error in a couple other places too like when clicking on a user under a domain. I can't edit the user.

The only places I could find reference to this was in /usr/share/webmin/virtual-server/feature-mysql.pl

I have put up the steps I needed to get things working up on my blog, including my modded and working feature-mysql.pl code.

Note that I have not included a check for mysql version in the perl code, so only use this if you have probs with the 5.7 version of mysql.

Link: http://kaffeine.cf/2015/12/09/webminvirtualmin-vs-mysql-server-community...

FYI, Webmin 1.780 and Virtualmin 5.0 will support the new user table format as seen in MySQL 5.7.

Hello

When will be version of webmin and virtualmin release in which mysql 5.7 will resolved ?

Webmin 1.780 has just been released, and includes MySQL 5.7 support. Virtualmin 5.0 will be out soon, and will also include 5.7 support.

I am just trying to restore a backup due to a migration (new server has mysql 5.7 older one has 5.6), and getting this error with the latest Webmin / Virtualmin. Do we need to do anything, or tick any settings to get this to work?

PS: Just noticed the mention that Virtualmin 5.0 is out soon that will have this support. Sorry.

Hey Jamie,

Just installed Virtualmin 5 and tried restoring a backup from a server with mysql 5.6 to a server with mysql 5.7, but still getting this error. Will raise a new bug in the Virtualmin queue.

PS here we go http://virtualmin.com/node/39205

Damn, looks like there is a MySQL 5.7-specific use case that we don't handle yet. Looking into it now..

Ok, the next Virtualmin release will fix these bugs.

Hello,

I've just tried to restore a backup on a server with MySQL 5.7 and got the same error:

Restoring allowed MySQL hosts .. Restore failed : SQL insert into user (host, user, password, ssl_type, ssl_cipher, x509_issuer, x509_subject) values ('%', '*****', password('*******'), '', '', '', '') failed : Unknown column 'password' in 'field list'

Systems is: CentOS Linux 7.2.1511 Webmin 1.791 Authentic Theme 17.72 MySQL version 5.7.12

I badly need to restore this backup, please help.

Thanks - Rogerio

Hi i don't know if this is related to this post. But my Backup from Ubuntu 12.04 (mysqld 5.5.49-0ubuntu0.12.04.1-log, WebMin 1.791) not work on a fresh install with Ubuntu 16.04 (mysqld 5.7.12-0ubuntu1, Webmin 1.795). It seems that the connection to the mysql server not works on my Wordpress WEB Sites.

I am happy if the backup from 12.04 to 16.04 works soon...

Thanks for your works and best regards Steve

stephanw, can you share some more details about the problem you're seeing there? Are you receiving any errors in the logs when that occurs? For example, some errors may be appearing in $HOME/logs/error_log.

Still error: "Failed to create virtual server : SQL select password from user where user = ...
Ubuntu Linux 14.04.3
MySQL Server version: 5.7.13

Webmin version 1.780
Virtualmin version 4.18

faina09 , those Webmin and Virtualmin versions are older. You may want to update to the most recent versions and see if that resolves the problem you're experiencing.

thank you. I added to my file /etc/apt/sources.list the repos

and updated webmin to 1.810, but virtualmin do not upgrade, neither fro apt.get neither form webmin interface... any hint?

You'd want to make sure you're using a supported Linux distribution/version:

https://www.virtualmin.com/os-support.html

If the distribution you're using is supported, then you'd need to review the lines in your sources.list file to ensure they're pointing at the Virtualmin repository.

If you continue to have problems updating Virtualmin, since it appears you're using Virtualmin GPL, what we'd suggest is for you to open up a new Forum thread, and there, let us know what distro/version you're using, as well as the contents of your /etc/apt/sources.list file. Thanks!

My OS is supported (ubuntu 14.02); I added

deb http://software.virtualmin.com/gpl/ubuntu virtualmin-universal main

and then upgrade process worked fine. Thank you! BTW, also the problem with MySQL 5.7 is resoved with Virtualmin 5.03 gpl