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