Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

Question

Error message on MySql:

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

I have gone through several other posts and was not able to solve this problem. The part affected is something similar to this:

CREATE TABLE users (
    userID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    firstName VARCHAR(24) NOT NULL,
    lastName VARCHAR(24) NOT NULL,
    username VARCHAR(24) NOT NULL,
    password VARCHAR(40) NOT NULL,
    PRIMARY KEY (userid)
) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE products (
    productID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(104) NOT NULL,
    picturePath VARCHAR(104) NULL,
    pictureThumb VARCHAR(104) NULL,
    creationDate DATE NOT NULL,
    closeDate DATE NULL,
    deleteDate DATE NULL,
    varPath VARCHAR(104) NULL,
    isPublic TINYINT(1) UNSIGNED NOT NULL DEFAULT '1',
    PRIMARY KEY (productID)
) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE productUsers (
    productID INT UNSIGNED NOT NULL,
    userID INT UNSIGNED NOT NULL,
    permission VARCHAR(16) NOT NULL,
    PRIMARY KEY (productID,userID),
    FOREIGN KEY (productID) REFERENCES products (productID) ON DELETE RESTRICT ON UPDATE NO ACTION,
    FOREIGN KEY (userID) REFERENCES users (userID) ON DELETE RESTRICT ON UPDATE NO ACTION
) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;

The stored procedure I'm using is this:

CREATE PROCEDURE updateProductUsers (IN rUsername VARCHAR(24),IN rProductID INT UNSIGNED,IN rPerm VARCHAR(16))
BEGIN
    UPDATE productUsers
        INNER JOIN users
        ON productUsers.userID = users.userID
        SET productUsers.permission = rPerm
        WHERE users.username = rUsername
        AND productUsers.productID = rProductID;
END

I was testing with php, but the same error is given with SQLyog. I have also tested recreating the entire DB but to no good.

Any help will be much appreciated.

user_2181576 · Answer

Answer is adding to @Sebas' answer - setting the collation of my local environment. Do not try this on production.

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Source of this solution

user_1132355 · Answer

In my own case I have the following error

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

$this->db->select("users.username as matric_no, CONCAT(users.surname, ' ', users.first_name, ' ', users.last_name) as fullname") ->join('users', 'users.username=classroom_students.matric_no', 'left') ->where('classroom_students.session_id', $session) ->where('classroom_students.level_id', $level) ->where('classroom_students.dept_id', $dept);

After weeks of google searching I noticed that the two fields I am comparing consists of different collation name. The first one i.e username is of utf8_general_ci while the second one is of utf8_unicode_ci so I went back to the structure of the second table and changed the second field (matric_no) to utf8_general_ci and it worked like a charm.

user_2795580 · Answer

Despite finding an enormous number of question about the same problem (1, 2, 3, 4) I have never found an answer that took performance into consideration, even here.

Although multiple working solutions has been already given I would like to do a performance consideration.

EDIT: Thanks to Manatax for pointing out that option 1 does not suffer of performance issues.

Using Option ~~1 and~~ 2, aka the COLLATE cast approach, can lead to potential bottleneck, cause any index defined on the column will not be used causing a full scan.

Even though I did not try out Option 3, my hunch is that it will suffer the same consequences of option ~~1 and~~ 2.

Lastly, Option 4 is the best option for very large tables when it is viable. I mean there are no other usage that rely on the original collation.

Consider this simplified query:

SELECT 
    *
FROM
    schema1.table1 AS T1
        LEFT JOIN
    schema2.table2 AS T2 ON T2.CUI = T1.CUI
WHERE
    T1.cui IN ('C0271662' , 'C2919021')
;

In my original example, I had many more joins. Of course, table1 and table2 have different collations. Using the collate operator to cast, it will lead to indexes not being used.

See sql explanation in the picture below.

Visual Query Explanation when using the COLLATE cast

On the other hand, option 4 can take advantages of possible index and led to fast queries.

In the picture below, you can see the same query being run after applied Option 4, aka altering the schema/table/column collation.

Visual Query Explanation after the collation has been changed, and therefore without the collate cast

In conclusion, if performance are important and you can alter the collation of the table, go for Option 4. If you have to act on a single column, you can use something like this:

ALTER TABLE schema1.table1 MODIFY `field` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

user_1143013 · Answer

This happens where a column is explicitly set to a different collation or the default collation is different in the table queried.

if you have many tables you want to change collation on run this query:

select concat('ALTER TABLE ', t.table_name , ' CONVERT TO CHARACTER 
SET utf8 COLLATE utf8_unicode_ci;') from (SELECT table_name FROM 
information_schema.tables where table_schema='SCHRMA') t;

this will output the queries needed to convert all the tables to use the correct collation per column

user_4335210 · Answer

I was also facing a problem while upload excels file in MySql Database form Laravel. In excel file some addresses contain characters like PeterÕs that Error was

SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation '=' (SQL: select count(*) as aggregate from `.....` where `e....` = kas@email.com and `first_name` = Gill and `surname` = Harries and `address` = 6 St.PeterÕs Close,,,Woodbridge,Suffolk,IP12 4EJ and `status` = 1 and `client`.`deleted_at` is null)

I try many solutions that is mentioned above but unfortunately, no one works for me. so I post the solution that works for me. so maybe it will not work in some situations. Exception can be different. so you have to find a solution based on that. please vote Up if some finds this is useful.

I try 2 types of solutions.

I putted my exceptional code into try{} Catch() block for handling. because it was giving an error while executing select() query. so that I use TRY{} for this. using that uploaded record stored in the database. But when charset issue comes on that place it is put ?. that sample image is given below.

you can detect the charset that gives an issue while operating and change the MySql configuration by this code

 \Config::set('database.connections.mysql.charset', 'latin1');
  \Config::set('database.connections.mysql.collation', 'latin1_bin');
  \DB::purge('mysql');

Both solution works for me.

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

9 Answers