Thursday, October 19, 2023

How to fix the MySQL error: Illegal mix of collations for operation union

Well, you probably have different collations in some MySQL views or stored procedures, try to force collation like this:
SET character_set_client = 'utf8mb4'; 
SET character_set_connection = 'utf8mb4'; 
SET collation_connection = 'utf8mb4_unicode_ci'; 
drop view if exists myviewwithproblem; 
create view myviewwithproblem as  da da etc etc
Another solution might be:
I ran into this recently as well. In my case the relevant columns were utf8mb4_unicode_ci, but I found out that the session was utf8mb4_general_ci. You can see this from the collation_connection variable: CODE: SELECT ALL

SHOW VARIABLES LIKE '%collat%';

I found that this came from the connection string including "Character Set=utf8mb4". This caused a "SET NAMES UTF8MB4;" query to be run on every connection, which causes MySQL to take the default collation for this character set, which is utf8mb4_general_ci.

Updating the server's character set and collation to match the data (setting character-set-server and collation-server in the server config) and then using "Character Set=Auto" caused the connection to have the correct collation, and the issue was fixed. Manually running something like "SET NAMES UTF8MB4 COLLATE utf8mb4_unicode_ci" should fix it, too.

No comments:

Post a Comment