This morning, a user asked in the MySQL Community Slack if somebody had an idea why the error log was filled up continuously with a warning messages like this one:
2020-07-24T06:54:00.877128Z 46 [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'
The context was that the user upgraded from MySQL 5.7 to MySQL 8.0 and changed the authentication plugin of all users to
As the warning message was written to the error log almost twice per second, I suspected a monitoring tool. The user said, that no application, no monitoring was using this server yet.
Of course we verified that all users had the plugin changed.
This is important when helping people (friends, colleagues, customers, users) to never trust them and ask for evidences. And sometimes just by asking evidences and let people tell the full process will help them and/or you to figure out what is the problem.
So, in this case all users had the new authentication plugin and the default (
default_authentication_plugin) was also set to
We also tried to block connections to the IP and 127.0.0.1 for the mysql port but the log was constantly filled with the same warning message.
I checked with the security developers when this deprecated warning was thrown:
- user creation
- connection attempt
So we were back to the connection attempts. As the IP and 127.0.0.1 were not used that could have been the socket.
In fact, when a user name is not found, MySQL assigns an authentication plugin randomly and proceed with authentication, to finally deny it.
So how could we resolve this issue ?
In MySQL there is a very nice but still unknown plugin that is the perfect solution to prove my point: Connection-Control Plugins !
We started by installing it:
mysql> INSTALL PLUGIN CONNECTION_CONTROL -> SONAME 'connection_control.so'; Query OK, 0 rows affected (0.15 sec) mysql> INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS -> SONAME 'connection_control.so'; Query OK, 0 rows affected (0.04 sec)
And we could immediately see what was the problem:
The user used that server to test PMM and installed the pmm-agent. Then forgot about it 😉
Aaaaah the monitoring tool was indeed the culprit !
Of course the credentials used by pmm were not present in the new installed database. The problem was also happening on 5.7 but without warning message about the deprecated authentication plugin.