How to Fix MySQL 8.0 to 5.7 Collation Compatibility Issues During Upgrade

Introduction

Oracle has ended support for MySQL 5.7, prompting many companies to upgrade existing 5.7 clusters to MySQL 8.0. Production environments typically use a primary‑replica architecture; the upgrade path usually upgrades replicas first, switches roles, then upgrades the old primary.

Problem Scenario

After the primary‑replica switch, the old 5.7 primary is attached to the new 8.0 primary for data sync. Replicating from MySQL 8.0 to MySQL 5.7 can break because MySQL 8.0 introduces new collations, notably utf8mb4_0900_ai_ci (ID 255), while MySQL 5.7 defaults to ID 45.

SHOW COLLATION WHERE COLLATION IN('utf8mb4_general_ci','utf8mb4_0900_ai_ci');

When the binlog contains the new collation ID 255, the 5.7 slave aborts with an error such as:

Last_Error: Error 'Character set '#255' is not a compiled character set...'

Root Causes

DDL explicitly specifies utf8mb4_0900_ai_ci collation, e.g.:

CREATE TABLE t_0900(
  ID INT PRIMARY KEY
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Clients connect using the 255 collation and perform DML/DDL/DCL operations.

Solution

Prevent the 255 collation from appearing in DDL and binlog:

Educate developers not to use utf8mb4_0900_ai_ci in production SQL.

Configure SQL‑audit tools (e.g., goInception) to reject or whitelist collations.

Adjust server configuration before upgrading each node:

Add to my.cnf before upgrade: collation_server = utf8mb4_general_ci After the upgrade and before traffic switch, set the following variables on the MySQL instance:

SET PERSIST default_collation_for_utf8mb4 = utf8mb4_general_ci;

SET PERSIST init_connect = "SET SESSION collation_connection = IF(@@collation_connection='utf8mb4_0900_ai_ci','utf8mb4_general_ci',@@collation_connection); SET SESSION character_set_client = (SELECT ID FROM information_schema.collations WHERE collation_name = @@collation_connection LIMIT 1)";

Variable Analysis

collation_server : Global default collation defined in my.cnf. Setting it to a non‑255 value ensures new connections inherit a safe collation.

default_collation_for_utf8mb4 : New in 8.0; affects the session collation after SET NAMES utf8mb4 if no explicit COLLATE clause is used.

collation_connection and character_set_client : Record the client’s collation and charset. They must be forced to utf8mb4_general_ci (ID 45) for compatibility.

Client‑Side Considerations

Non‑SUPER accounts execute @@global.init_connect on login, which can set the collation. SUPER accounts skip this step; only the global variables affect them.

Common clients (MySQL client, JDBC, Go drivers) default to collation ID 255. To avoid this, either specify connectionCollation=utf8mb4_general_ci in the JDBC URL or set the appropriate session variables after connecting.

Comparison with Other Solutions

Two typical online solutions were compared: one that forces the old master to use the new collation, and another that rewrites binlog events. The approach presented here is more elegant and compatible, already proven in production.

Conclusion

By understanding the collation incompatibility and applying the configuration changes and SQL statements above, the upgrade from MySQL 5.7 to 8.0 can be performed without replication failures, ensuring a smooth migration path.