Invalid combination of sorts (utf8_unicode_ci, IMPLICIT) and (utf8_general_ci, IMPLICIT) for operation '='

Question

Msg error on MySql: Invalid combination of sorts (utf8_unicode_ci, IMPLICIT) and (utf8_general_ci, IMPLICIT) for operation '='

I went through several other posts and could not 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 tested php, but the same error is given using SQLyog. I also tested the recreation of the entire database, but nothing good.

Any help would be greatly appreciated.

Answer 1

There are 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 WHERE :

 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 definition of the IN parameter:

 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 : change the field:

 ALTER TABLE users CHARACTER SET utf8 COLLATE utf8_general_ci; 

since the default sorting setting for stored procedure parameters is utf8_general_ci and you cannot mix sorts.

If you do not need to sort the data in Unicode order, I would suggest changing all your tables to use utf8_general_ci , since it does not require code changes, and utf8_general_ci will be fast.

UPDATE : utf8mb4 / utf8mb4_unicode_ci is now the preferred character typing / sorting method. utf8_general_ci is not recommended as the performance improvement is negligible. See https://stackoverflow.com/a/312960/

Answer 2

I spent half a day searching for answers to the identical error “Invalid collage mixing” with conflicts between utf8_unicode_ci and utf8_general_ci.

I found that some of the columns in my database were not specifically mapped to utf8_unicode_ci. Mysql seems to implicitly map these utf8_general_ci columns.

In particular, executing the query "SHOW CREATE TABLE table1" displays approximately 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 string 'col1' varchar (4) CHARACTER SET utf8 NOT NULL does not have the specified sort. Then I executed the following query:

ALTER TABLE table1 CHANGE col1 col1 VARCHAR(4) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

This solved my "Invalid Collage Mixing" error. Hope this helps someone else.

Answer 3

I had a similar problem, but it occurred to me inside the procedure when my query parameter was set using a variable, for example. SET @value='foo' .

What caused this was the collation_connection mismatch and database mapping. Collation_connection changed to match collation_database , and the problem disappeared. I think this is a more elegant approach than adding COLLATE after param / value.

To summarize: all mappings must match. Use SHOW VARIABLES and make sure that collation_connection and collation_database match (also check table sorting with SHOW TABLE STATUS [table_name] ).

Answer 4

A bit like @bpile's answer, my case was setting the record my.cnf collation-server = utf8_general_ci . After I realized that (and after trying everything above), I forcibly switched my database to utf8_general_ci instead of utf8_unicode_ci, and it was like this:

 ALTER DATABASE `db` CHARACTER SET utf8 COLLATE utf8_general_ci; 

Answer 5

In my case, I have the following error

Invalid combination of sorts (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 several weeks of searching on Google, I noticed that the two fields that I am comparing consists of a different sort name. The first, that is, the username refers to utf8_general_ci, and the second refers to 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.

Answer 6

Despite the fact that we found a huge number of questions on the same problem ( 1 , 2 , 3 , 4 ), I did not even find an answer that took into account the effectiveness, even here.

Although several working solutions have already been proposed, I would like to consider the issue of effectiveness.

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

Using options 1 and 2, as well as the COLLATE method, can lead to a potential bottleneck, because any index defined in the column will not be used, which will lead to a full scan .

Despite the fact that I have not tried option 3, I suspect that it will suffer the same consequences as options 1 and 2.

Finally, option 4 is the best option for very large tables when it is viable. I mean, there is no other use that relies on the original sort.

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 other joins. Of course, table1 and table2 have different comparisons. Using the sort operator to create this will result in indexes not being used.

See explanations in the figure below.

Visual Query Explanation when using COLLATE

Alternatively, option 4 can take advantage of a possible index and lead to quick queries.

In the figure below, you can see the same query that is launched after applying Option 4, also changing the sorting of the schema / table / column.

Visual Query Explanation after changing sorting and therefore without sorting

In conclusion, if performance is important and you can change the sorting of the table, go to option 4. If you need to act in a single column, you can use something like this:

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

Answer 7

This occurs when the column is explicitly given a different mapping or the default mapping is different in the requested table.

if you have many tables, you want to change the sorting options when this query is run:

 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 tables in order to use the correct collation for the column