MySQL Bugs: #109635: Invalid Warning error logs: Plugin sha256

Bug #109635	Invalid Warning error logs: Plugin sha256_password reported information
Submitted:	14 Jan 2023 17:42	Modified:	16 Jan 2023 8:35
Reporter:	xichao li (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Logging	Severity:	S3 (Non-critical)
Version:	8.0, 8.0.31	OS:	Any
Assigned to:		CPU Architecture:	Any

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

Hello xichao li,

Thank you for the report and feedback.

regards,
Umesh

Posted by developer:
 
Thank you for your bug report. I have changed the category to Pluggable Auth so the experts in the Sever General team can evaluate it.

Note however, that there are strict policies mandating that older and less secure authentication mechanisms must be deprecated,
and also that deprecation warnings must be emitted when deprecated features are used.