8

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?

codeturner
  • 993
  • 1
  • 8
  • 20

2 Answers2

7

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;
izzy
  • 358
  • 4
  • 7
  • Very valid reason. In my case that was exactly the reason because INFO log message just before this warning tells about unknown user trying to log in with password. – Denis The Menace Nov 23 '21 at 06:58
0

ALTER USER can change the authentication plugin:

ALTER USER usernmae@remoteip
IDENTIFIED WITH 'caching_sha2_password'
   RETAIN CURRENT PASSWORD
danblack
  • 12,130
  • 2
  • 22
  • 41
  • All of my users already have that set. It's the auto-generated users for mysql router that are using the 'mysql_native_password' that I suspect is the problem. – codeturner May 15 '20 at 15:29
  • The plugin being used according to the error is `sha256_password` which came before `caching_sha2_password` – danblack May 16 '20 at 01:10
  • Yes, I know that, but I have no users using 'sha256_password'. That's what's baffling to me. – codeturner May 16 '20 at 20:02
  • (Guess), the router's doing `sha256_password` and the server somehow has a fallback. I agree. quite odd. – danblack May 17 '20 at 01:14
  • 2
    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 'RETAIN CURRENT PASSWORD' – 0x1stvan Oct 21 '22 at 11:33
  • 2
    `RETAIN CURRENT PASSWORD` doesn't seem to work as you would initially think. Docs say it retains the current password as a secondary. You need to provide the password with the `BY ` clause. You can't convert from one hash algorithm to another without knowing the original. – Brandon Aug 18 '23 at 20:23