The mysterious error on sql: Illegal mix of collations
Okay, I am aware that I have called a mysterious error in the title and error description is too obvious.
When you read this article, your opinion may change. So.. I hope.
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation ‘=’
This means you want to compare the utf8_general_ci with the utf8_unicode_ci collations and sql does not allow it. (sometimes with ‘<>’ operation)
Like everyone else, my first step was to search Google. When I encountered this error before, I set in the parameter or in the where clause and solved it. I share a good explanation about this solution:
But this time I realized solution were just a click away.
It was difficult for me to find the point where it exploded by debugging the error because my query was long and complex. Every join and condition in where clause expressed an equals. For this reason, lets first find the answer to the question of where do these collations come from:
SHOW VARIABLES LIKE ‘%collat%’;
Warning: It may not be enough to just check the database where you run the procedure. You may also need to check all databases you use in your query. It is important that the collation_connection and collation_database fields are the same. If they are different, we run the following command:
Alter database ‘database_name’ collate = utf8mb4_unicode_ci;
We make a progress, this may be enough to solve your problem. But the problem still continues for me.
This time I reduce our search to the table level we use in query and check the collations of tables and again I equalize it all. I hope the solutions so far will work, this post also has additional info and this was the information that really excited me:
I use temporary table in my store procedure and learn that whatever you provide during installation is the temp table collation. So even if we equalize the collations of all the tables, there may be a point we missed !
