Description:
In the absence of --skip-grant-table, the database creates an acl_user object through the function decoy_user() when a non-existent user is logged into the database.
When this object is created, its plugin property is randomly selected from cached_plugins_enum. This makes it possible to select the PLUGIN_SHA256_PASSWORD plug-in.
But at the entrance to sha256_password_authenticate(), a Warning message is generated indicating that PLUGIN_SHA256_PASSWORD is deprecated:
2023-01-10T01:07:23.035479Z 13 [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'
When invalid database access occurs due to objective reasons, a large number of Warning logs are generated in the database error log. As a result, alarms and log files are too large.
How to repeat:
1. Use the following configuration to start the mysql instance
[mysqld]
core-file
max_connections=3000
innodb_buffer_pool_size=1G
innodb_buffer_pool_instances=4
skip-slave-start=1
slow_query_log=1
general_log=1
log_error_verbosity=3
datadir=/mysql/data
socket=/tmp/mysql.sock
log-error=/opt/logs/mysql_error.log
binlog-format=ROW
log_bin=3306 # test is comment
innodb_file_per_table=1
enforce_gtid_consistency=ON
gtid_mode=ON
log-slave-updates=ON
plugin_load_add='group_replication.so'
innodb_flush_method='O_DIRECT'
innodb_use_native_aio=1
2. Use the script to simulate invalid user login to the database
for i in `seq 1 10000`
do
mysql -uuser$i -h127.0.0.1 -p123456 -e "" 2>/dev/null
done
3. Check the error log file
tail -300f /opt/logs/mysql_error.log
Suggested fix:
the decoy_user() function removes PLUGIN_SHA256_PASSWORD to fix the problem without affecting other functions.
Repair see: https://github.com/mysql/mysql-server/pull/439