#814 Error creating users in MariaDB 10 w/

[sai-mike]

I upgraded a Synology NAS today from MariaDB 5 to MariaDB 10.3.24. I exported two DBs from Maria 5 using Adminer 4.8.1 and then imported them into Maria 10 also using Adminer. When complete, I logged into Maria 10 with Adminer to create the user access accounts for the DBs. When I attempted to create the first user, I received the error:
"The MariaDB server is running with the --strict-password-validation option so it cannot execute this statement"

In Adminer, MariaDB Variables, I find: strict_password_validation = ON
This is the default in MariaDB now (see link below) but a plugin also needs to be loaded to activate it. Synology is using a proprietary plugin. It looks like you can query information_schema to determine if any plugins are loaded. The native simple_password_check plugin also uses this type:
SELECT * FROM PLUGINS WHERE PLUGIN_TYPE = 'password validation';

It appears the syntax has changed when creating users in MariaDB with v10.1.2: "Starting from 10.1.2, MariaDB implements the Password Validation Plugin API".
(https://mariadb.com/kb/en/password-validation/)

When creating a user in Adminer 4.8.1, it generates this SQL statement:
CREATE USER 'fred'@'localhost' IDENTIFIED BY PASSWORD '1Weh4_asG6'
This will fail and needs to be replaced by:
CREATE USER 'fred'@'localhost' IDENTIFIED BY '1Weh4_asG6'
This will succeed.

If you attempt to change a password after the user is created, this will also fail as the syntax of SET PASSWORD has also changed in the above article.

phpMyAdmin addressed this issue by adding a check for whether or not a password plugin was loaded in MariaDB:
https://github.com/phpmyadmin/phpmyadmin/issues/12366

Perhaps Adminer could do the same?

[RoyBellingan]

I had the same issue today

I think the best fix is to have the same behaviour as in the past, where there was a checkbox "use hashed" or a similar name, (if you want I can check what was), default UNCHECKED.

But at the moment any password update will result in a NON WORKING user, and also the internal error is silently discarded

You might thing was ok the edit https://imgur.com/ChDBGja

But in fact the command is wrong

https://imgur.com/nuCPeF5