About SRGTechnical ArticlesRecruitment Information
About SRGTechnical ArticlesRecruitment Information
HOME/Technical Articles/Replication from MySQL 8.0 to 5.7 stops with the error utf8mb4_0900_ai_ci. Cause and solution

Replication from MySQL 8.0 to 5.7 stops with the error utf8mb4_0900_ai_ci. Cause and solution

This is Oniumi (@fat47) from the Service Reliability Group (SRG) of the Technology Headquarters.
#SRG(Service Reliability Group) is a group that mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, and contributing to OSS.
This article is located in SRGDBWGWe will publish materials regarding the database provided by the DB Working Group for the entire company.
I hope this helps in some way.
 

overview

During the process of upgrading RDS from MySQL 5.7 to 8.0, we received a consultation from a client who wanted to create a MySQL 5.7 replica for a fallback environment, but a replication error occurred and the system stopped working.
The two articles below also introduce examples, but we will check to see if there are any other ways to avoid this.

phenomenon

Instance nameMySQL Versionrole
DB1MySQL8.0sauce
DB2MySQL5.7replica
The parameter you are changing
 
If you configure a configuration where the source is MySQL 8.0 and the replica is MySQL 5.7, the following error will occur and replication will stop.
 
utf8mb4_0900_ai_ci
 

Workaround

Specify skip_character_set_client_handshake

The above blog postHere is the method introduced here.
Enable the following in the DB1 (MySQL 8.0) parameter group:
 
utf8mb4_0900_ai_ci
skip_character_set_client_handshake
However, it is not enough to simply specify Collation; we will explain this below.
 

What didn't work and why

Specifying Collation when creating a schema or table

If the default setting is not good, you might think that specifying Collation will fix it.
What a no-no.
Let's try creating a schema with COLLATE specified.
 
When I checked the slave status on the DB2 side, I found that replication had stopped due to an error.
 
The schema on DB1 side is created with the COLLATE properly specified.
 

Specify default_collation_for_utf8mb4

default_collation_for_utf8mb4
This cannot be set in my.cnf and must be set using SET GLOBAL or SET PERSIST for parameter persistence.
[Updated October 2023] SET PERSIST may no longer be possible in the RDS environment below. Since I couldn't confirm this, I stored it.
In the case of SET PERSIST, the RDS administrator privileges are not sufficient to execute the command, so you must grant them separately.
As of October 2023, it is thought that it will not be available in RDS.
SET SESSION default_collation_for_utf8mb4=utf8mb4_general_ci;
 
This is true for both of these methods,
If you look at the contents of the binlog on DB1 (MySQL8.0), you will see the following:
 
Let's take a look at what happens before CREATE DATABASE is executed.
Here, ID 255, which 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
 
If you look closely before executing CREATE DATABASE, you will see that 255 is not specified, but 45.
 
utf8mb4_general_ci
 

Reference URL

 

Conclusion

We explained the traps you can fall into when configuring MySQL 5.7 as a replica for rollback purposes.
I think we will see more talk of upgrading to MySQL 8.0 as we approach the end of life of MySQL 5.7 in October 2023, so I hope this article will be useful.
 
SRG is looking for people to work with us. If you're interested, please contact us here.