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 · Accepted Answer

The default collation for stored procedure parameters is utf8_general_ci and you can't mix collations, so you have four options:

Option 1: add COLLATE to your input variable:

SET @rUsername = ‘aname’ COLLATE utf8_unicode_ci; -- COLLATE added
CALL updateProductUsers(@rUsername, @rProductID, @rPerm);

Option 2: add COLLATE to the WHERE clause:

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 COLLATE utf8_unicode_ci -- COLLATE added
        AND productUsers.productID = rProductID;
END

Option 3: add it to the IN parameter definition:

CREATE PROCEDURE updateProductUsers(
    IN rUsername VARCHAR(24) COLLATE utf8_unicode_ci, -- COLLATE added
    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

Option 4: alter the field itself:

ALTER TABLE users CHARACTER SET utf8 COLLATE utf8_general_ci;

~~Unless you need to sort data in Unicode order, I would suggest altering all your tables to use utf8_general_ci collation, as it requires no code changes, and will speed sorts up slightly.~~

UPDATE: utf8mb4/utf8mb4_unicode_ci is now the preferred character set/collation method. utf8_general_ci is advised against, as the performance improvement is negligible. See https://stackoverflow.com/a/766996/1432614

User · 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 · 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 · 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 · 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 · Answer

I spent half a day searching for answers to an identical  Illegal mix of collations  error with conflicts between utf8 unicode ci and utf8 general ci   I found that some columns in my database were not specifically collated utf8 unicode ci  It seems mysql implicitly collated these columns utf8 general ci   Specifically  running a  SHOW CREATE TABLE table1  query outputted something like the following      table1   CREATE TABLE  table1     id  int 11  NOT NULL   col1  varchar 4  CHARACTER SET utf8 NOT NULL   col2  int 11  NOT NULL  PRIMARY KEY   col1   col2     ENGINE InnoDB DEFAULT CHARSET utf8 COLLATE utf8 unicode ci     Note the line  col1  varchar 4  CHARACTER SET utf8 NOT NULL does not have a collation specified  I then ran the following query   ALTER TABLE table1 CHANGE col1 col1 VARCHAR 4  CHARACTER SET utf8  COLLATE utf8 unicode ci NOT NULL   This solved my  Illegal mix of collations  error  Hope this might help someone else out there

User · Answer

A bit similar to  bpile answer  my case was a my cnf entry setting collation-server   utf8 general ci  After I realized that  and after trying everything above   I forcefully switched my database to utf8 general ci instead of utf8 unicode ci and that was it   ALTER DATABASE  db  CHARACTER SET utf8 COLLATE utf8 general ci

User · Answer

I had a similar problem  but it occurred to me inside procedure  when my query param was set using variable e g  SET  value  foo    What was causing this was mismatched collation connection and Database collation  Changed collation connection to match collation database and problem went away  I think this is more elegant approach than adding COLLATE after param value   To sum up  all collations must match  Use SHOW VARIABLES and make sure collation connection and collation database match  also check table collation using SHOW TABLE STATUS  table name

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

Examples related to mysql

Examples related to stored-procedures