MySQL User Creation fails for new domains on MySQL version >= 8.0.11

When adding a new domain with MySQL enabled, I get the following error message:

Creating MySQL login .. .. MySQL database failed! : mysql::execute_sql_logged failed : SQL insert into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject, plugin, authentication_string) values ('localhost', 'EXAMPLE', '', '', '', '', 'mysql_native_password', password('REMOVED')) failed : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('REMOVED'))' at line 1 at /usr/libexec/webmin/web-lib-funcs.pl line 1493.

After digging a bit, it seems that the PASSWORD() function has been deprecated as of MySQL 8.0.11. I am running MySQL 8.0.12.

Status: 
Closed (fixed)

Comments

Title: MySQL User Creation fails for new domains » MySQL User Creation fails for new domains on MySQL version >= 8.0.11
Body: View changes
Assigned: Unassigned »

Howdy -- thanks for letting us know! That does indeed sound like an issue with Virtualmin's support for MySQL 8.

Note though that we really only do testing with the MySQL/MariaDB versions that are included with the distributions we support.

We'll certainly get that fixed! But we unfortunately don't have an ETA for that.

I'm passing this along to Jamie for further review.

MySQL's method for setting user passwords has unfortunately varied significantly since release, so supporting them all in Virtualmin is painful.

Where did you see that the "password" function has been removed?

The statement that the password function has been removed is here: https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#functi...

I am happy to do this manually until the new version is supported. How may I enable a database without creating it, which fails every time? Ideally would like to do this for existing domains - somewhere else it mentioned about changing the default template, but that does not seem to work.

I think I'm going to have to re-do the way password setting is done in Virtualmin ... stay tuned.

Ok, support for the password hashing method used in MySQL 8 has been implemented and will be included in the next release.

Error Enabling MySQL Database!

I get this error:

Creating MySQL login .. .. MySQL database failed! : mysql::execute_sql_logged failed : SQL alter user 'apazadito'@'localhost' identified by 'xxxxxxxxxxxxx' failed : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'user 'apazadito'@'localhost' identified by 'xxxxxxxxxxxxx'' at line 1 at /usr/share/webmin/web-lib-funcs.pl line 1478.

Updating Webmin user .. .. done

Updating Webmin user .. .. done

Saving server details .. .. done

Re-loading Webmin .. .. done

There are more people affected by this problem! See this link!

https://www.virtualmin.com/node/64694#comment-808676

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.