CODEWITHSUNDEEP
mysqlcollation
Michael42
Michael42

Reputation: 1051

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

Upvotes: 89

Views: 204231

Answers (9)

Moode Osman
Moode Osman

Reputation: 2013

if you are using trigger or you are able to drop the table that causing the error delete the trigger or the table and run: replace database_name with your database name and replace the Collations to the one you want to use

ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

then create the trigger again or the table that you are having a problem with

Upvotes: 0

Sooraj Thekkepatt
Sooraj Thekkepatt

Reputation: 99

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;

Upvotes: 2

Dhruv Patadia
Dhruv Patadia

Reputation: 162

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.

Upvotes: 0

FRANCO
FRANCO

Reputation: 191

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

Upvotes: 16

Really Nice Code
Really Nice Code

Reputation: 1334

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

Upvotes: 12

jprog
jprog

Reputation: 41

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

Upvotes: 4

nhinhi8062
nhinhi8062

Reputation: 91

Check connection with charset=utf8mb4

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

Upvotes: 9

MrApnea
MrApnea

Reputation: 1946

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.

Upvotes: 86

Prateek Bhuwania
Prateek Bhuwania

Reputation: 805

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.

Upvotes: 27

Related Questions