About SRGArticlesRecruitment Information
About SRGArticlesRecruitment Information
HOME/Articles/Cause and solution for replication from MySQL 8.0 to 5.7 stopping due to utf8mb4_0900_ai_ci error

Cause and solution for replication from MySQL 8.0 to 5.7 stopping due to utf8mb4_0900_ai_ci error

This is Oniumi (@fat47) from the Service Reliability Group (SRG) of the Technology Headquarters.
#SRG(Service Reliability Group) mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, contributing to OSS, etc.
This article is located in SRGDBWGWe will publish materials regarding the database that the (DB Working Group) provides to 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 process stopped.
The two articles below also introduce examples of this issue, 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 the source is MySQL 8.0 and the replica is MySQL 5.7, the following error will appear 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 parameter group for DB1 (MySQL 8.0).
 
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 settings are not correct, you might think that specifying Collation will fix the problem.
What a no-no.
Let's try creating a schema by specifying COLLATE.
 
When I checked the slave status on the DB2 side, I found that replication had stopped due to an error.
 
The schema on the DB1 side has been 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 with SET GLOBAL or SET PERSIST to make parameters persistent.
[Updated October 2023] SET PERSIST may no longer be possible in the following RDS environment at some point. I have stored it because I cannot confirm this.
In the case of SET PERSIST, the RDS administrator privileges do not provide sufficient execution privileges, so you must grant them separately.
As of October 2023, it is not expected to be available on 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 (MySQL 8.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, which causes 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 pitfalls you can fall into when configuring MySQL 5.7 as a replica for fallback purposes.
I think we will see more talk about upgrading to MySQL 8.0 as we approach October 2023, when MySQL 5.7 will reach its EOL date, so I hope this article will be useful.
 
SRG is looking for people to work with us. If you are interested, please contact us here.