sha256_password deprecated warnings filling logs

Question

I've configured an InnoDB MySQL v8.0.19 Group Replication Cluster in single-primary mode. I have several webapps accessing the cluster via their own MySQL Router instance in a 1:1 relationship, as per the suggested pattern.

Everything appears to be working fine, but the logs for my primary server are being filled with the following message:

[Warning] [MY-013360] [Server] Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

Dumping the mysql.user table, I see that the generated mysql_router users are using the 'mysql_native_password' plugin, and all other users are using the correct 'caching_sha2_password' plugin.

These mysql routers are the only clients accessing the server, so I suspect it's complaining about how it's accessing the cluster.

Anyone know how to fix this warning?

Answer 1

Set mysql global variable default_authentication_plugin worked for me.

mysql> show variables like 'default_authentication_plugin';

| Variable_name                 | Value                 |
| default_authentication_plugin | mysql_native_password |

Run docker with the chaching_sha2_password plugin

#> docker run -it --rm mysql mysql -u root -p --default-authentication-plugin=caching_sha2_password

or /etc/mysql/conf.d/mysql.cnf

Answer 2

I had the same problem, that the warning was filling up my error log, and also none of the users in the mysql.user table was using the sha256_password. As explained in this blog post, the warning is misleading, the problem was an unregistered user that is trying to login to MySQL.

The reason we see the warning about the sha256_password, rather than an access denied error, is because:

when a user name is not found, MySQL assigns an authentication plugin randomly and proceed with authentication, to finally deny it

As described in the above linked blog post, you can use the connection control plugin to identify the unregistered user:

INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';

INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';

select * from information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;