mysql_native_password does not have default value on MariaDB 10.2

When virtualmin try to create a user on a MariaDB 10.2 server, I got this error :

insert into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject) values ('localhost', 'example.com', '', '', '', '') failed : Field 'authentication_string' doesn't have a default value

It comes from the feature-mysql.pl fil, function get_user_creation_sql() :

if (($variant eq "mysql" && &compare_versions($ver, "8") >= 0 ||
     $variant eq "mariadb" && &compare_versions($ver, "10.2") >= 0) &&
    $plainpass) {
        my $native = &is_domain_mysql_remote($d) ?
                        "with mysql_native_password" : "";
        return ("insert into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject) values ('$host', '$user', '', '', '', '')", "flush privileges", "alter user '$user'\@'$host' identified $native by '".&mysql_escape($plainpass)."'");
        }
elsif (&compare_versions($ver, "5.7.6") >= 0) {
        return ("insert into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject, plugin, authentication_string) values ('$host', '$user', '', '', '', '', 'mysql_native_password', $encpass)");
        }
...

The firs "if" instruction is true, but The user table does not have default value on the "plugin" field by default.

Also, even you set the "plugin" field to "mysql_native_password", I got a syntax error on the second statement : the "by" keyword is not available here in Mariadb (see https://mariadb.com/kb/en/library/alter-user/ ), only the "USING" or "AS" keyword is accepted, and only the "AS" keyword is compatible with both Mariadb and Mysql.

So I fixed it by using the old method (without "alter userr") :

if (($variant eq "mysql" && &compare_versions($ver, "8") >= 0 ||
     $variant eq "mariadb" && &compare_versions($ver, "10.2") >= 0) &&

By

if (($variant eq "mysql" && &compare_versions($ver, "8") >= 0 ||
     $variant eq "mariadb" && &compare_versions($ver, "10") < 0) &&

And it works. Notice that I did not test with MariaDB 10.3, maybe we should use

&compare_versions($ver, "10.3") >= 0

?

Thanks

Status: 
Active

Comments

Assigned: Unassigned »

Thanks for your report! I'm passing this to Jamie for further comment.

Is your MariaDB server running on a different system to Virtualmin? That's what would cause is_domain_mysql_remote to return true

Sorry for late reply.

Yes the mariadb 10.2 server is running remotely (in a local container). that's why "with mysql_native_password" is added.

For info, I just upgrade an other server to Mariadb 10.2 on Debian 9 (using an official Maraidb repo mirror), I got the same error :

MySQL database failed! : mysql::execute_sql_logged failed : SQL insert into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject) values ('localhost', 'example.com', '', '', '', '') failed : Field 'authentication_string' doesn't have a default value at /usr/share/webmin/web-lib-funcs.pl line 1477

After applying the same patch, it worked.

Thanks

Ok, we are actively working on fixing this - MariaDB is surprisingly quite different from MySQL 8.

Looks like the correct fix is the change the lines :

if (($variant eq "mysql" && &compare_versions($ver, "8") >= 0 ||
     $variant eq "mariadb" && &compare_versions($ver, "10.2") >= 0) &&
    $plainpass) {

to :

if ($variant eq "mysql" && &compare_versions($ver, "8") >= 0 &&
    $plainpass) {

JAMIE you mean in?

feature-mysql.pl

Also for Mariadb 10.4 and < 10.2 ?

marco1492's picture
Submitted by marco1492 on Mon, 04/06/2020 - 15:56

I did change the /usr/share/webmin/virtual-server/feature-mysql.pl as Jamie suggested in #6. It does NOT fix the problem. Continues just like before. I am on MariaDB version 10.3.22... Frustrating...

Have you tried upgrading the Webmin 1.942? It incorporates a bunch of mysql/mariadb fixes.

I got the same issue with webmin 1.955.

Here the new patch to fix it :

diff --git i/feature-mysql.pl w/feature-mysql.pl
index f990c7c..55fe3d7 100755
--- i/feature-mysql.pl
+++ w/feature-mysql.pl
@@ -2839,7 +2839,8 @@ elsif ($variant eq "mysql" && &compare_versions($ver, "8") >= 0 && $plainpass) {
                        "with mysql_native_password" : "";
        return ("insert into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject) values ('$host', '$user', '', '', '', '')", "flush privileges", "alter user '$user'\@'$host' identified $native by '".&mysql_escape($plainpass)."'");
        }
-elsif ($variant eq "mysql" && &compare_versions($ver, "5.7.6") >= 0) {
+elsif ( ($variant eq "mysql" && &compare_versions($ver, "5.7.6") >= 0) ||
+       ($variant eq "mariadb" && &compare_versions($ver, "10.2") >= 0) ) {
        return ("insert into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject, plugin, authentication_string) values ('$host', '$user', '', '', '', '', 'mysql_native_password', $encpass)");
        }
 elsif (&compare_versions($ver, 5) >= 0) {

This affect only mariadb 10.2 version (10.3 is handled in the update, and versions <= 10.1 are working).

Thanks

marco1492's picture
Submitted by marco1492 on Mon, 09/21/2020 - 12:49

I am working on it as well...

Could you plz post yr full feature-mysql.pl ??? (I know, it's got 93k...) :-)

Ilia's picture
Submitted by Ilia on Mon, 09/21/2020 - 13:37

Unlike MariaDB, the caching_sha2_password plugin is now the default authentication plugin in MySQL 8.0.4 and above, based on the value of the default_authentication_plugin system variable!

I just got issue again with the last virtualmin version on Mariadb 10.3, while transfering to a other Mariadb host (dockerized).

SQL set password for 'mydomain'@'127.0.1.1' = password('mypassword') failed : Can't find any matching row in the user table

Same fix apply, it solved the issue :

diff --git a/usr/share/webmin/virtual-server/feature-mysql.pl b/usr/share/webmin/virtual-server/feature-mysql.pl
index eacc403..dd8529d 100755
--- a/usr/share/webmin/virtual-server/feature-mysql.pl
+++ b/usr/share/webmin/virtual-server/feature-mysql.pl
@@ -2869,7 +2869,10 @@ if ($variant eq "mariadb" && &compare_versions($ver, "10.4") >= 0) {
                ($plainpass ? "'".&mysql_escape($plainpass)."'"
                            : "password $encpass"));
        }
-elsif ($variant eq "mysql" && &compare_versions($ver, "5.7.6") >= 0) {
+elsif (
+       ($variant eq "mysql" && &compare_versions($ver, "5.7.6") >= 0)
+       || ($variant eq "mariadb" && &compare_versions($ver, "10.2") >= 0)
+)      {
        my $changepasssql = "update user set authentication_string = $encpass where user = '$user' and host = '$host'";
        if ($plainpass) {
                $changepasssql = "alter user '$user'\@'$host' identified $plugin by '".&mysql_escape($plainpass)."'";

File available here.

Please release it !

Ilia, didn't we already fix these before the 1.970 release?

Ilia's picture
Submitted by Ilia on Thu, 01/21/2021 - 06:47

I just got issue again with the last virtualmin version on Mariadb 10.3, while transfering to a other Mariadb host (dockerized).

Default installation of MariaDB 10.3 on Debian 10 does have Password field. You can fix this by creating Password filed in mysql table.

Please release it !

No, I'm sorry. The process of switching password for privileged and unprivileged user is not the same. Your aforementioned patch would fail in many other situations. There was a reason to keep it the way it is now.

However, if you could provide details for reproducing an issues I would be glad to have a closer look. What you were transferring and where (OS and MariaDBs versions on source and target)?

Ilia, didn't we already fix these before the 1.970 release?

Yes. But it seems Virtualmin MySQL lib related, based on what @xorax reported.

Default installation of MariaDB 10.3 on Debian 10 does have Password field. You can fix this by creating Password filed in mysql table.

I figure out why : there is a Debian patch on Maraidb 10.3 that add a Password field to the user table. This change is not in the Mariadb 10.4 / Debian.

This is because of this Debian patch that Virtualmin still work with the "old Mysql way" on Mariadb 10.3-Debian, and works differently on Mariadb 10.4.

Here I'm using the standard Mariadb 10.2 Docker image which of course does not include this Debian patch.

To start & test it :

docker run --name mariadb-10-2 -h mariadb-10-2.localhost \
        -p 127.0.0.1:3308:3306 \
        --restart=always \
        -e MYSQL_ROOT_PASSWORD=yourpass \
        -d mariadb:10.2.12

And Mariadb 10.2 is not released in any version of Debian (9 => 10.1, 10 => 10.3). I use the Virtualmin feature to add an external MySQL server (very usefull) and so I think it should support all MySQL/Mariadb servers versions (not only the ones released by the OS), that's not easy I agree.

The process of switching password for privileged and unprivileged user is not the same. Your aforementioned patch would fail in many other situations. There was a reason to keep it the way it is now.

The "alter user" syntax I force by patching get_user_creation_sql() is already used in execute_password_change_sql() for mariadb >= 10.2 (same as in my patch). So this syntax was already working and "approoved" for Mariadb >= 10.2, and it's working.

So I don't know what you mean about "The process of switching password for privileged and unprivileged user is not the same", here we change only the user creation process I guess. Do you mean there is a case an unpriviledged user may run this command and it will fail ? If yes I guess it will fail with mariadb 10.4 too anyway (where we use "alter user" syntax).

I just tested my patch on Debian 10 with Mariadb 10.3 (from Debian repo) and it's working well : I tested with root user to create a domain with a database, I changed the password, create an user database; and check that the user can still connect and see the 2 databases : everything is ok. I also tested to create a user with the current version, and change it's password with my patch : it's working. What are the others checks I should do ?

So to resume, at MariaDB side :

"set password" syntax works only on :

  • Mariadb <= 10.1 (with or without debian patch)
  • Mariadb 10.3 with debian patch

"alter user" syntax works only on :

  • Mariadb >= 10.2 without debian patch
  • Mariadb 10.3 with debian patch

I see an other case where not using the "alter user" syntax will cause issue : it's in set_mysql_user_connections() : here it's used only for mariadb >= 10.4, but I guess it should be forced for Mariadb => 10.2 and Mysql >= 8. I guess it's bogus on Mysql 8 today, but I don't think many people use this feature.

Is there something I missed ?

Thanks !