About SRGArticlesRecruitment Information
About SRGArticlesRecruitment Information
HOME/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) mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, contributing to OSS, etc.
This article summarizes the issue where a collation error occurred in only certain 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 there is an error 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

Reasons for 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 only the character code is specified by SET NAMES, the system's default COLLATION will be set due to the MySQL specifications.
utf8mb4_general_ci
 
utf8mb4_0900_ai_ci
 
utf8mb4_0900_ai_ci
 
This talk on go-sql-driver/mysql collation was given at the MySQL Users Group event held in August this year.@mita2This was mentioned by Mr. Ikeda, and a detailed analysis is provided in the following blog, so please refer to it.

Reproduction verification

Creating a test table

INSERT 100 test data

Reproduced code in Go using go-sql-driver

 
When I run it I get an error

solution

This can be resolved by applying either of the following fixes to the go-sql-driver/mysql connection options:
  • Do not specify anything
  • charset=utf8mb4&collation=utf8mb4_general_ci
*If you specify both of go-sql-driver/mysql versions before v1.8.0, unintended collation will be specified, so please update to 1.8.0 or higher. For details, see the above.@mita2's blogSee .

Conclusion

I panicked when the error suddenly appeared, but luckily I was able to immediately remember something I had heard at a LT 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 are interested, please contact us here.