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

The answer is somewhere in `SHOW VARIABLES LIKE 'char%';` and `SHOW CREATE TABLE`. Let's see both of those. (Don't bother truncating or reimporting or recreating the table.) — Rick James, May 18 '17 at 04:23
Did you get your answer? If so it would be nice if you accepted the answer. — MrApnea, Jun 07 '20 at 08:02

score 72 · Answer 1 · edited Aug 17 '18 at 14:41

72

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.

edited Aug 17 '18 at 14:41

Alessio Cantarella

5,077
3
27
34

answered May 17 '17 at 15:10

MrApnea

1,776
1
9
17

1

thanks. I changed tables collations but didnt help. as last thing, I re-create that tables :( – Michael42 May 17 '17 at 15:19

score 25 · Answer 2 · edited Apr 16 '18 at 09:17

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.

score 13 · Answer 3 · edited Mar 07 '21 at 18:59

13

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

edited Mar 07 '21 at 18:59

Community

1
1

answered Jan 07 '21 at 01:13

FRANCO

161
1
2

2

Stack Overflow is an English language site. – Blastfurnace Jan 07 '21 at 04:31
1

Safe my life with SET collation_connection = 'utf8mb4_general_ci'; adding to the top of my store procedures – vaduc Jun 08 '23 at 12:05

score 10 · Answer 4 · answered Jul 21 '20 at 19:51

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

Remember to first backup your database before performing these operations.

Log into phpMyAdmin.
Select your database from the list on the left.
Click on "Operations" from the top set of tabs.
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

I got the error working with bagisto, following this answer worked for me. I did not have to restart mysql though — F KIng, Feb 07 '23 at 13:55

score 9 · Answer 5 · edited Mar 23 '20 at 10:40

9

Check connection with charset=utf8mb4

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

edited Mar 23 '20 at 10:40

JoSSte

2,953
6
34
54

answered Mar 23 '20 at 09:04

nhinhi8062

91
1
2

This little ignored answer should get a little bit more attention :) – John Nov 13 '20 at 23:47

score 3 · Answer 6 · answered May 26 '20 at 09:25

3

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

answered May 26 '20 at 09:25

jprog

31
1

score 1 · Answer 7 · answered Jul 15 '22 at 13:38

1

The issue is both tables have the same column, to overcome this issue we need to ALTER both the tables and database

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE tabel1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE tabel2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

answered Jul 15 '22 at 13:38

Sooraj Thekkepatt

79
9

Just like Franco said. :-) – Steve Stilson Nov 18 '22 at 20:00

score 0 · Answer 8 · answered Mar 10 '22 at 11:48

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.

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

8 Answers8

Linked