About SRGTechnical articleRecruitment Information
About SRGTechnical articleRecruitment Information
⚠️⚠️⚠️ STG SRG Portal ⚠️⚠️⚠️/Technical article/After upgrading to Aurora MySQL Version 3 (MySQL 8.0), a specific query encountered a collation error, and here's how I solved it.

After upgrading to Aurora MySQL Version 3 (MySQL 8.0), a specific query encountered a collation error, and here's how I solved it.

This is Yuta Kikai (@fat47) from the Service Reliability Group (SRG) of the Media 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 summarizes an issue where, after upgrading to Aurora MySQL version 3, only specific queries were experiencing collation errors.
I hope this is of some help.
 

After upgrading to Aurora MySQL Version 3, only certain queries are failing.

After upgrading to Aurora MySQL Version 3, I started experiencing errors in a specific Go application.
The following error was output to the error log at that time:
Illegal mix of collations
It appears that an error is occurring because it is 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 user-defined SQL variable like the one below and performed a process that compared it with the value of a column.
(Sample query)
 
In the connection options for go-sql-driver/mysql, only charset=utf8mb4 was specified.
collation=utf8mb4_general_ci
charset=utf8mb4
This will be executed.
If only the character encoding is specified using SET NAMES, the system's default COLLATION will be set according to MySQL's specifications.
utf8mb4_general_ci
 
utf8mb4_0900_ai_ci
 
utf8mb4_0900_ai_ci
 
This discussion about the collation of go-sql-driver/mysql was presented in a lightning talk at the MySQL Users Group event held in August of this year.@mita2As mentioned by [Name], a detailed analysis can be found in the following blog post; please refer to it for more information.

Reproduction verification

Creating a test table

Insert 100 test data entries.

Reproduction code in Go using go-sql-driver

 
An error occurs when executed.

solution

This can be resolved by applying one of the fixes to the connection options for go-sql-driver/mysql.
  • Do not specify anything
  • charset=utf8mb4&collation=utf8mb4_general_ci
*If you are using a version of go-sql-driver/mysql lower than v1.8.0, specifying both may result in unintended collation being specified, so please upgrade to version 1.8.0 or higher. See above for details.@mita2's blogPlease refer to the following.

In conclusion

I panicked when the error suddenly occurred, but luckily I was able to quickly remember what I had heard at a lightning talk at the MySQL users' meeting.
This experience made me realize how important it is to actively participate in events and gather information!
 
SRG is looking for new team members. If you are interested, please contact us here.