About SRGTechnical ArticlesRecruitment Information
About SRGTechnical ArticlesRecruitment Information
HOME/Technical Articles/After upgrading to Aurora MySQL Version 3 (MySQL 8.0), a collation error occurred in a specific query and its solution

After upgrading to Aurora MySQL Version 3 (MySQL 8.0), a collation error occurred in a specific query and its solution

This is Onkai Yuta (@fat47) from the Service Reliability Group (SRG) of the Media 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 summarizes the issue of a collation error occurring in specific queries after upgrading to Aurora MySQL version 3.
I hope this helps in some way.
 

After upgrading to Aurora MySQL Version 3, errors occurred only in certain queries

After upgrading to Aurora MySQL Version 3, an error started occurring only in a specific Go application.
The following error was output to the error log:
Illegal mix of collations
You can see that an error occurs when trying to compare utf8mb4_general_ci and utf8mb4_0900_ai_ci.

Collation and MySQL 8.0

utf8mb4_general_ci
utf8mb4_0900_ai_ci
 
utf8mb4_general_ci
 
utf8mb4_0900_ai_ci

Cause of the error

In this Go code:go-sql-driver/mysqlI was using v1.8.1 to connect to Aurora (MySQL).
The SQL query in the code that caused the error used a SQL user-defined variable like the one below to compare it with a column value.
(Query sample)
 
The only connection option specified for go-sql-driver/mysql was charset=utf8mb4.
collation=utf8mb4_general_ci
charset=utf8mb4
will be executed.
If you only specify the character code using SET NAMES, the system's default COLLATION will be set due to MySQL specifications.
utf8mb4_general_ci
 
utf8mb4_0900_ai_ci
 
utf8mb4_0900_ai_ci
 
This talk about go-sql-driver/mysql collation was given at the LT of the MySQL Users Group event held in August this year.@mita2This was mentioned by Mr. and a detailed investigation is provided in the blog below, so please refer to it.

Reproduction verification

Creating a test table

Insert 100 test data

Go reproduction code using go-sql-driver

 
When I run it I get an error

solution

This can be resolved by applying either of the fixes in the go-sql-driver/mysql connection options.
  • Do not specify anything
  • charset=utf8mb4&collation=utf8mb4_general_ci
*If you are using a version of go-sql-driver/mysql that is less than v1.8.0, specifying both will result in an unintended collation being specified, so please upgrade to 1.8.0 or higher. For details, see the above@mita2's blogSee

Conclusion

I was a bit worried when the error suddenly appeared, but luckily I was able to immediately remember something I had heard at a lightning talk at the MySQL Users Group.
This incident made me realize how important it is to actively participate in events and gather information!
 
SRG is looking for people to work with us. If you're interested, please contact us here.