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

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.

Solution 1:

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 (pre-MySQL 5.7):

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

Solution 2:

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.

Solution 3:

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]).