'Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

I got this error;

Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

I changed "Collations" to "utf8mb4_unicode_ci". Then tables were truncated and I re-import rows again. But still getting same error

mysqlcollation


Solution 1:[1]

I am guessing you have different collations on the tables you are joining. It says you are using an illegal mix of collations in operations =.

So you need to set collation. For example:

WHERE tableA.field COLLATE utf8mb4_general_ci = tableB.field

Then you have set the same collations on the = operation.

Since you have not provided more info about the tables this is the best pseudo code I can provide.

Solution 2:[2]

For Join Query I used this piece of query to resolve such error:

select * from contacts.employees INNER JOIN contacts.sme_info  
ON employees.login COLLATE utf8mb4_unicode_ci = sme_info.login

Earlier using the following query, I was getting the same error:

select * from contacts.employees LEFT OUTER JOIN contacts.sme_info  
ON employees.login = sme_info.login

Error: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

I don't know much about collations but seems like both tables follow different rules for character set. Hence, the equal to operator was not able to perform. So in the first query I specified a collation set to collect and combine.

Solution 3:[3]

After many hours i finally found a solution that worked for me (using phpMyAdmin).

Remember to first backup your database before performing these operations.

  1. Log into phpMyAdmin.
  2. Select your database from the list on the left.
  3. Click on "Operations" from the top set of tabs.
  4. In the Collation box (near the bottom of the page), choose your new collation from the dropdown menu.

I also checked
*Change all tables collations
*Change all tables columns collations

I don't think its 100% necessary, but its also a good idea to restart your mySQL/MariaDb service + Disconnect and reconnect to the database.

Additional note: I had to use utf8mb4_general_ci because the issue persisted when using utf8mb4_unicode_ci (which i originally wanted to use)

For additional information, command line queries and illustrated examples i recommend this article: https://mediatemple.net/community/products/dv/204403914/default-mysql-character-set-and-collation

Solution 4:[4]

Check connection with charset=utf8mb4

'dsn'       => 'mysql:dbname=DatabaseName;host=localhost;charset=utf8mb4';

Solution 5:[5]

Had the same problem and fixed it by updating the field's collation.

Even if you change the table's collation, the individual table fields still have the old collation. Try to alter the table and update those varchar fields

See example here

Solution 6:[6]

-- This worked for me

SET collation_connection = 'utf8mb4_general_ci';
ALTER DATABASE your_bd CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Solution 7:[7]

First, check your collation of table and database, make it similar if they are different.

If you are getting error in stored procedure, than first check the Collations of your DB and the column on which you are making equal operation, if they are different change the column collate to whatever is your DB and then you have to re-create that stored procedure by dropping it.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Alessio Cantarella
Solution 2 Yasin Patel
Solution 3 Really Nice Code
Solution 4 JoSSte
Solution 5 jprog
Solution 6 Community
Solution 7 Dhruv Patadia

Related Questions