Fix MySQL 5.7 to 8 Upgrade Errors and Breaking Changes (2026)

The MySQL upgrade from 5.7 to 8.0 failed because the authentication plugin changed, and existing users were not migrated correctly.

Authentication Plugin Incompatibility

What broke: The mysql_native_password authentication plugin, common in MySQL 5.7, is deprecated in 8.0 and has been replaced by caching_sha2_password. When the upgrade process completes, users authenticated with the old plugin can no longer log in.

Common Causes & Fixes:

caching_sha2_password is the default:
- Diagnosis: Check user authentication methods:
```
SELECT user, host, plugin FROM mysql.user WHERE plugin != 'caching_sha2_password';
```
- Fix: For each user not using caching_sha2_password, update their authentication plugin.
```
ALTER USER 'your_user'@'your_host' IDENTIFIED WITH caching_sha2_password BY 'your_password';
FLUSH PRIVILEGES;
```
- Why it works: This explicitly sets the user’s authentication method to the new default, allowing them to connect.
Client library incompatibility: Older clients (e.g., certain versions of PHP’s mysqlnd or older Java connectors) may not support caching_sha2_password.
- Diagnosis: Check your application’s client library version. If it’s significantly old, it’s a likely culprit. Look for error messages like "The authentication plugin 'caching_sha2_password' is not supported."
- Fix: Update your client libraries. For PHP’s mysqlnd, ensure it’s at least version 5.0.12. For Java, use Connector/J 8.0.13 or later.
```
# Example for updating PHP's mysqlnd via PECL (version might differ)
pecl install mysqlnd
# Or via package manager:
sudo apt-get update && sudo apt-get install php-mysql
```
- Why it works: Newer client libraries are built to understand and negotiate the caching_sha2_password protocol.
Temporary workaround: Revert to mysql_native_password: If updating clients is not immediately feasible, you can temporarily allow mysql_native_password for specific users or globally.
- Diagnosis: Same as cause 1, but you’re looking for users who should be using the old plugin if you intend to revert.
- Fix: Alter the user to use the old plugin.
```
ALTER USER 'your_user'@'your_host' IDENTIFIED WITH mysql_native_password BY 'your_password';
FLUSH PRIVILEGES;
```
  To set it as the default for new users (not recommended long-term):
```
# In my.cnf or my.ini
[mysqld]
default_authentication_plugin=mysql_native_password
```
  Restart MySQL after changing my.cnf.
- Why it works: This forces MySQL to accept the older, less secure authentication method, bridging the gap while you update clients or migrate users.
Server configuration issue (default_authentication_plugin not set): Sometimes, the server might not have caching_sha2_password explicitly set as the default if it wasn’t there before.
- Diagnosis: Check your my.cnf or my.ini file for the default_authentication_plugin setting.
- Fix: Ensure the setting is present and correct in your MySQL configuration file.
```
# In my.cnf or my.ini
[mysqld]
default_authentication_plugin=caching_sha2_password
```
  Restart the MySQL server after modifying the configuration file.
- Why it works: This explicitly tells the MySQL server which authentication plugin to use by default for newly created users or when an existing user’s plugin isn’t specified.
sql_mode incompatibility: Certain sql_mode settings from 5.7 might be incompatible with 8.0, preventing the upgrade from completing cleanly or causing unexpected behavior during the upgrade process itself.
- Diagnosis: Check the sql_mode variable before the upgrade:
```
SELECT @@global.sql_mode;
```
  Look for modes that might be deprecated or removed in 8.0.
- Fix: Remove problematic sql_mode values. For example, NO_AUTO_CREATE_USER was removed in 8.0.
```
SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
FLUSH PRIVILEGES;
```
  Or, more commonly, set it in my.cnf:
```
# In my.cnf or my.ini
[mysqld]
sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
```
  Restart MySQL.
- Why it works: Ensures the server’s operational modes are compatible with the new MySQL version, preventing errors during startup or operation.
Reserved Keywords: MySQL 8.0 introduced new reserved keywords, which can conflict with existing table or column names.
- Diagnosis: The error message will typically indicate a syntax error or an "unknown column" when trying to run queries, often related to a specific keyword.
```
# Example error: ERROR 1064 (42000): 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 'rank' at line 1
```
- Fix: Rename the conflicting table or column, or quote the identifier using backticks.
```
-- Rename table
RENAME TABLE `rank` TO `rank_table`;
-- Or quote the identifier in queries
SELECT `rank` FROM your_table;
```
- Why it works: Backticks tell MySQL to treat the identifier literally, bypassing keyword parsing. Renaming is a more robust solution.

After resolving authentication issues, you will likely encounter ERROR 1051 (42S02): Unknown table 'performance_schema.events_statements_summary_by_digest' if performance_schema was not correctly initialized or if certain tables were dropped during an incomplete upgrade.