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

Question

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

Answer 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.

Answer 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.

Answer 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

Answer 4

Check connection with charset=utf8mb4

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

Answer 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

Answer 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;