mysqli_real_connect(): (HY000/1275): Server is running in --secure-auth mode, but 'user'@'localhost' has a password in the old format; please change the password to the new format

The problem is that the passwords are saved the wrong way in Virtualmin/Webmin. As it seems this is handled in the file

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

I tried changing the old INSERT/UPDATE commands with the new one but it did not work.

I found a temporary solution and documented it here: https://www.virtualmin.com/node/64694

But the error is very annoying. Every user that we update destroys the database access to the database associated with this user. Please provide a hotfix asap.

Thank you! Marcus

Status: 
Active
Files: 

Comments

Howdy -- thanks for your report!

Which distro/version is it that you're using there?

Is this MySQL or MariaDB? And what database version? You can tell this from "mysql -V". Thanks!

mysql Ver 15.1 Distrib 10.3.17-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Debian GNU/Linux 10

All up-to-date - The problem is already known and disucssed in the forum - but no real bugfix or anything.

Thanks for the info -- we don't currently have support for MariaDB 10.3 and above, but Jamie is working on that. The issue you mentioned above should be resolved whenever Jamie completes his updates for newer MariaDB versions.

you are funny guys... :) the mariaDB update was installed through Virtualmin. Well everything seems to work except the password creation. The reason I think is because in the file

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

there's a function called

execute_password_change_sql()

This function currently uses this commands to set/update the password like:

update user set password = $encpass

Where this should be

update user set password = password($encpass)

I tried to change that to the correct syntax but I suppose there's other dependencies.

What should we do now? Everytime we change a host configuration everything mysql related breaks ;)

I know how I can fix it temporarily but it's pretty annoying when customer start to change things themselves, we don't know about and then complete websites are down until we notice the issue.

I think this problem should be "fixable" easy. Since I can simply issue the command:

set password for 'user@domain.told'@'localhost' = PASSWORD('pass');

manually on the server and boom - mariaDB is happy again :)

The database packages don't come from Virtualmin, they come from your distro or a third party.

Virtualmin can install packages if you tell it to, it pulls them in using RPM/DPKG through the repositories that have been enabled

But that doesn't change the fact that we don't yet support MariaDB 10.3 or higher :-)

We don't support any distros that offer that (including Debian 10 and CentOS 8), and manually adding a repository that uses that MariaDB version will cause problems, such as you're seeing.

If you're using MariaDB 10.3 or higher, it's not going to work until we push out a Virtualmin update to fix it.

Jamie is still working on that fix, and I unfortunately don't have a timeline. There's more than one issue preventing it from working properly, but we'll release an update when we've been able to address all the issues.

Thank you very much for your swift reply. I fully understand. But to be honest: I am using all regular Debian packages - nothing in addition. This is newest Debian 10 - all regular. Or do you see some special sources?

deb http://ftp.ch.debian.org/debian/ buster main contrib non-free deb-src http://ftp.ch.debian.org/debian/ buster main contrib non-free

deb http://security.debian.org/ buster/updates main contrib non-free deb-src http://security.debian.org/ buster/updates main contrib non-free

deb http://software.virtualmin.com/vm/6/apt virtualmin-buster main deb http://software.virtualmin.com/vm/6/apt virtualmin-universal main

I think this is plain basic Debian 10 out-of-the-box. It comes with MariaDB in that version. Are you saying that Virtualmin does not support the current (pretty much most) widely used Debian Linux? Or am I doing something wrong? See https://mariadb.org/debian-10-mariadb-10-3/

I think this should be top list on your plan - to make the software work with Debian 10 defaults. Just saying... :)

As described above though, we don't yet have support for Debian 10.

If you try to use that distro, you're going to run into database problems.

We're working on it, but at the moment we don't have a Virtualmin version designed to work with that distribution.

The only fix is to either use a supported distro, or to wait until we release support for Debian 10. Sorry!

The following steps may fix your issue, but please, make a full backup of your database first, including the users database, using the root mysql user:

1) Update the value of the password hash of the 'user' user

UPDATE mysql.user set password = PASSWORD('my password text') where user= 'user';

2) Stop 'mysql' server

service mysql stop

3) Set the flag old_passwords=1 on the /etc/my.cnf file

4) Start 'mysql' server

service mysql start

I just want to remember that this is not a bug, both from Virtualmin and MariaDB.

The error message is not wrong, because this is a feature of newer versions of both MySQL and MariaDB, as explained on this issue on MariaDB's repository: https://jira.mariadb.org/browse/MDEV-17789

Thanks for the link paulocoghi!

While we do plan to have full support for this soon, hopefully the above will help folks who are looking to use MariaDB 10.3+ in the meantime.

Yes, alright. I understand. I did not know - to be honest, that Virtualmin is not supporting Debian 10. That's my fault. I just assumed that this works. Although in the same time discussing here perhaps that little bug could have been fixed. I think the only thing that needs to be done is to add the password() to all password set/update functions and that's it. I started to look into it and I find the command in like 6 different files. Have not tried it out yet though but I think this way it could be solved.

Otherwise I can tell that Virtualmin works perfectly well so far on our D10 server.