MySQL database updated but accounts locked out

[BoBeR182]

#179 (comment)

Based on the conversation here.
I updated my MySQL to version 9, but either the database migration script did not run correctly. https://bugs.mysql.com/bug.php?id=115225 or I did something wrong.

2024-10-17T17:56:44.540476Z 0 [Warning] [MY-010312] [Server] The plugin 'mysql_native_password' used to authenticate user 'root'@'localhost' is not loaded. Nobody can currently login using this account.
2024-10-17T17:56:44.540497Z 0 [Warning] [MY-010312] [Server] The plugin 'mysql_native_password' used to authenticate user 'ezxss'@'%' is not loaded. Nobody can currently login using this account.
2024-10-17T17:56:44.540503Z 0 [Warning] [MY-010312] [Server] The plugin 'mysql_native_password' used to authenticate user 'root'@'%' is not loaded. Nobody can currently login using this account.

Downgrading to 8.0 does not fix it, nor can I enable mysql_native_authentication anymore.

Is there a way to keep my user accounts and reports or should I bite the loss?

[ssl]

Hey @BoBeR182,

#186 (comment)

Yeah, your database is still using mysql_native_password while this is no longer supported. It should be updated to caching_sha2_password.

Easy way would indeed be creating a new database, but that shouldn't be the way to go.

You should login to your database, presumably by root. If you can't because root was also using mysql_native_password, login in safemode mysqld_safe --skip-grant-tables &

Update the password
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'your_new_password';
ALTER USER 'ezxss'@'%' IDENTIFIED WITH caching_sha2_password BY 'user_password';
And restart MySQL.

[BoBeR182]

Hmm, inside the mysql docker image, there is no mysqld_safe command.
I will try to extract the database and change the login, then inject it back.

[BoBeR182]

I found a solution:

1. Edit docker-compose.yml to have --skip-grant-tables in the command: section of the ezxssdb service.
2. Run the following:

docker exec -it ezxss-ezxssdb-1 sh
sh-5.1# mysql
mysql> FLUSH PRIVILEGES;
mysql> SELECT User, Host, plugin FROM mysql.user WHERE plugin = 'mysql_native_password'; 
mysql> ALTER USER 'ezxss'@'%' IDENTIFIED WITH caching_sha2_password BY 'password_from_env';
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY 'password_from_env';
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password_from_env';

3. Remove the --skip-grant-tables and restart the container.

Should we close the issue? Or should there be a migration guide/script to allow for seamless one-click from previous versions and databases?

[ssl]

Great you found a solution @BoBeR182!

For now we'll leave it like this. I will link to this issue if anyone else faces the same issue. Thank you!