mysql_native_password does not have default value on MariaDB 10.2

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 :

After applying the same patch, it worked.

Thanks

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 just got issue again with the last virtualmin version on Mariadb 10.3, while transfering to a other Mariadb host (dockerized).

Same fix apply, it solved the issue :

File available here.

Please release it !

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 :

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 !