About SRGTechnical articleRecruitment Information
About SRGTechnical articleRecruitment Information
HOME/Technical article/Causes and solutions for MySQL 8.0 to 5.7 replication stopping due to a utf8mb4_0900_ai_ci error.

Causes and solutions for MySQL 8.0 to 5.7 replication stopping due to a utf8mb4_0900_ai_ci error.

This is Onikai (@fat47) from the Service Reliability Group (SRG) of the Technology Division.
#SRGThe Service Reliability Group primarily provides comprehensive support for the infrastructure surrounding our media services, focusing on improving existing services, launching new ones, and contributing to open-source software (OSS).
This article is located within SRG.DBWGThe DB Working Group is releasing database-related materials that it provides to the entire company.
I hope this is of some help.
 

overview

We received a request for help regarding a problem where, during the upgrade process from MySQL 5.7 to 8.0 on RDS, they wanted to create a replica of MySQL 5.7 for a rollback environment, but the replication was failing and stopping.
The following two articles also provide examples, but we will check if there are any other ways to avoid this.

phenomenon

Instance nameMySQL Versionrole
DB1MySQL8.0sauce
DB2MySQL5.7replica
Parameters being changed
 
When configuring a system where the source is MySQL 8.0 and the replica is MySQL 5.7, the following error occurs and replication stops.
 
utf8mb4_0900_ai_ci
 

Workaround

Specify skip _character_set_client_handshake.

The above blog postThe method introduced in [previous article] is as follows.
Enable the following in the DB1 (MySQL 8.0) parameter group.
 
utf8mb4_0900_ai_ci
skip_character_set_client_handshake
It's not simply a matter of specifying a Collation; I'll explain that below.
 

Methods that didn't work and why

Specify Collation when creating schemas and tables.

If the default setting is bad, you might think that specifying Collation would solve the problem, but
It just doesn't work.
Let's try creating a schema using COLLATE.
 
Checking the slave status on the DB2 side reveals that replication has stopped due to an error.
 
The schema on the DB1 side has been created correctly using the specified COLLATE command.
 

Specify default_collation_for_utf8mb4

default_collation_for_utf8mb4
This setting cannot be configured in my.cnf; it must be set using SET GLOBAL or SET PERSIST for parameter persistence.
[October 2023 Update] The SET PERSIST command may no longer work in the following RDS environment. Since I couldn't confirm this, I've removed it from the documentation.
In the case of SET PERSIST, the RDS administrator privileges are insufficient, so additional execution privileges must be granted.
As of October 2023, it appears that this is not available on RDS.
SET SESSION default_collation_for_utf8mb4=utf8mb4_general_ci;
 
This applies to both of these methods,
Looking at the contents of the binlog on the DB1 (MySQL 8.0) side, it looks like this:
 
Let's take a look at what happens before CREATE DATABASE is executed.
Here, an ID 255 that does not exist in MySQL 5.7 is specified, causing the replica to stop with an error.
 
utf8mb4_0900_ai_ci
 
skip_character_set_client_handshake
 
Upon closer inspection before executing CREATE DATABASE, it appears that 255 is not specified, but rather 45.
 
utf8mb4_general_ci
 

Reference URL

 

In conclusion

This article explains the pitfalls you can encounter when configuring a MySQL 5.7 replica for rollback purposes.
As we approach the end-of-life (EOL) of MySQL 5.7 in October 2023, I expect to see more discussions about upgrading to MySQL 8.0, so I hope this article will be helpful.
 
SRG is looking for new team members. If you are interested, please contact us here.