Troubleshooting MySQL Authentication Warnings: Deprecated Plugins and Bug Analysis

Background​

A MySQL 8.0.27 test environment exhibited numerous warning logs despite applications running normally:

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'

‍

This article investigates the root cause and provides solutions.

​Key Questions​

1. Why does a non-existent user trigger deprecation warnings for sha256_password?
2. How can we suppress these warnings without compromising security?

​Initial Analysis​
The warning suggests clients use outdated authentication plugins. Common tools like mysql or phpMyAdmin might trigger this. However, testing with valid credentials failed to reproduce the issue, indicating the problem wasn’t client-driven.

​Further Investigation​
Enabling the general log revealed suspicious activity:

SET GLOBAL general_log = ON;
-- Analyze logs to identify IP/host generating warnings
SET GLOBAL general_log = OFF;

‍

Logs showed repeated connection attempts from 10.x.y.43 using a non-existent user dbuser2. Database checks confirmed no such user existed, and skip-grant-tables was disabled.

​Root Cause Analysis via Source Code​
Delving into MySQL 8.0.27’s source code (sql/authentication.cc) revealed:

1. ​Authentication Flow:
   • The server initializes caching_sha2_password by default.
   • For invalid users, a "decoy" user object is created with a ​randomly selected plugin​ (1/3 chance of sha256_password).
   • This triggers the deprecation warning during authentication attempts.
2. ​Critical Code Segments:

// Random plugin selection for non-existent users
uint plugin_num = (uint)(my_rnd(rand) * DECIMAL_SHIFT);
user->plugin = Cached_authentication_plugins::cached_plugins_names[plugin_num];

1. If sha256_password is chosen, the warning is logged even for failed logins.

​Solutions​

1. ​Correct Client Configurations: Update applications to use valid users (e.g., caching_sha2_password-enabled accounts).
2. ​Suppress Warnings Temporarily:

sql

复制

SET GLOBAL log_error_suppression_list = 'MY-013360';

1. (Note: This may hide legitimate sha256_password warnings.)
2. ​Patch MySQL Source Code: Address the random plugin selection flaw (Bug #109635 filed).

​Recommendations​

• Migrate to caching_sha2_password for all users to avoid deprecation issues.
• Implement strict access controls to prevent unauthorized connection attempts.
• Regularly audit error logs for anomalies like repetitive warnings.

​Conclusion​
This case highlights how deprecated plugin warnings can arise from unexpected sources, such as invalid user authentication attempts. By combining log analysis, source code inspection, and strategic patching, teams can resolve such issues while preparing for future MySQL upgrades.

‍