Essential MySQL 5.7 → 8.0 Upgrade Guide: Common Pitfalls & Solutions

Why Upgrade?

Moving from MySQL 5.7 to 8.0 is a jump from a relatively permissive era to a stricter, more modern one. Proper preparation can make the transition smooth.

Core Advice

Never run the upgrade directly on production. Follow a test‑first approach.

Pre‑Upgrade Checklist

Backup production database using mysqldump or Percona XtraBackup.

Build a test environment on an isolated server, install MySQL 8.0, and restore the backup.

Perform a test upgrade in the test environment.

Comprehensively test applications against the test instance (functionality, performance, stress).

Prepare a rollback plan to revert to 5.7 if needed.

Upgrade Roadmap

The official recommendation is to upgrade major versions incrementally. Direct 5.7→8.0 is possible but riskier. If you are on a version earlier than 5.7 (e.g., 5.6), upgrade to 5.7 first.

MySQL 5.7 → MySQL 8.0

Phase 1: Pre‑Upgrade Checks (on MySQL 5.7)

Use MySQL Shell’s util.checkForServerUpgrade() to generate a report that lists:

Deprecated feature usage

Syntax compatibility issues

Changes in default variable values

Schema objects that need attention

mysqlsh root@localhost:3307 --password=your_password --sql
util.checkForServerUpgrade('root@localhost:3307', {password:'your_password', targetVersion:'8.0.33'})

Key Findings to Address

Default storage engine : MyISAM is deprecated; tables will be converted to InnoDB.

Password authentication plugin : mysql_native_password is deprecated; caching_sha2_password is now default.

Removed items : PASSWORD(), old-passwords, ENCODE()/DECODE(), etc.

Charset & Collation

Default charset changes from latin1 to utf8mb4.

Default collation changes from latin1_swedish_ci to utf8mb4_0900_ai_ci. Pay special attention to index column collations.

Inconsistent collations can cause index failures.

SQL Mode Changes

MySQL 8.0 default SQL_MODE includes:

ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION

Set the same mode on the 5.7 test instance.

Verify that GROUP BY queries run correctly.

Other Compatibility Checks

Foreign‑key identifier length limited to 64 characters.

New reserved keywords (e.g., GROUPING, CUME_DIST) may require backticks.

Phase 2: Upgrade Execution

Method 1 – In‑Place Upgrade

Stop the MySQL 5.7 service.

Replace binary files with the 8.0 version (backup datadir first).

Run: mysqld_safe --upgrade=FORCE Check error logs for success.

Method 2 – Logical Upgrade (Recommended)

Dump data from 5.7 using mysqldump or mysqlpump.

Install MySQL 8.0 on a new server.

Import the dump file.

This approach is safer but takes longer.

Phase 3: Post‑Upgrade Pitfalls & Solutions

1. Client Authentication Failure

Symptom :

Authentication plugin 'caching_sha2_password' cannot be loaded

Cause : Old client drivers do not support the new default plugin.

Solutions :

Upgrade client libraries:

PHP – use mysqlnd and upgrade PHP.

Java – use Connector/J 8.0+.

Python – use mysql-connector-python 8.0+ or latest PyMySQL.

Temporarily switch the user back to mysql_native_password:

ALTER USER 'your_user'@'%' IDENTIFIED WITH mysql_native_password BY 'your_password';
FLUSH PRIVILEGES;

As a last resort, set the server default authentication plugin in my.cnf:

[mysqld]
default_authentication_plugin=mysql_native_password

2. SQL Syntax Compatibility

Symptom : Errors on GROUP BY and other statements.

Cause : ONLY_FULL_GROUP_BY is enabled by default.

Solutions :

Rewrite queries so selected columns are either aggregated or appear in the GROUP BY clause.

Temporarily disable the strict mode:

SET GLOBAL sql_mode = (SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

3. Performance Regression

Cause : Optimizer changes and new default parameters (e.g., innodb_flush_log_at_trx_commit).

Solutions :

Use EXPLAIN to compare execution plans before and after.

Adjust configuration parameters and indexes.

Consider the Query Rewrite Plugin for problematic queries.

4. Case‑Sensitivity & Collation Issues

Cause : Default collation utf8mb4_0900_ai_ci differs from previous settings.

Solution : Explicitly specify charset and collation for databases and tables.

Final Checklist

Fully back up the production database.

Set up a test environment and restore the backup.

Run util.checkForServerUpgrade() and fix reported issues.

Test application connectivity and resolve authentication problems.

Simulate the target SQL_MODE in the test instance.

Verify charset and collation settings.

Upgrade client drivers and libraries.

Prepare a rollback plan.

Choose an upgrade method (in‑place or logical) and execute it on production.

After upgrade, perform full functional verification and monitor performance.