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 ?

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

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!