mysql: error code [1267]; Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

Question

I want to make the password column of my User table to be case sensitive in mysql.

Following is the description of the table:

/*Table: mst_user*/

   FIELD          TYPE          COLLATION        
-------------  ------------  -----------------
user_id        VARCHAR(100)  latin1_swedish_ci
first_name     VARCHAR(25)   latin1_swedish_ci
last_name      VARCHAR(25)   latin1_swedish_ci
USER_PASSWORD  VARCHAR(50)   latin1_swedish_ci
user_status    INT(11)       (NULL)           
version_id     INT(11)       (NULL)           
active_status  INT(11)       (NULL)           
user_type      INT(11)       (NULL)    

To make the USER_PASSWORD field case sensitive I executed following query:

ALTER TABLE `mst_user` MODIFY `USER_PASSWORD` VARCHAR(50) COLLATE `latin1_general_cs`;

This worked and the field is now case sensitive.

But I have a store procedure which executes a SELECT query on this table to check if the user exists for the given credentials.

Stored Proc::

CREATE PROCEDURE `usp_password_verify`(ip_login_id         VARCHAR(200),
                                 ip_user_password    VARCHAR(200),
                                INOUT success     INT(1),
INOUT tbl_usr_password          VARCHAR(100),
INOUT  pkg_user_password         VARCHAR(100))
BEGIN
  SELECT COUNT(*)
    INTO success
    FROM mst_user
   WHERE UPPER (user_id) = UPPER (ip_login_id)
   AND USER_PASSWORD=ip_user_password;

   SET tbl_usr_password = '';
   SET pkg_user_password= '';
END$$

When I call this stored proc from my java code I am getting the following error:

**error code [1267]; Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='**

Can anyone help what is wrong with this? Something that works as a simple query gives error while executing it in a stored proc!?

Answer 1

I came here after facing the same error. After going through the solutions provided it became apparent that the COLLATION was the trigger for my error. My code was similar to the original question but my error was MySQL said: #1267 - Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='.

I was trying to collect the password and username too (currently no hashing) and I fixed it by explicitly adding the COLLATION for both parameters.

First code (the erratic code):

WHERE user.username = username
AND user.password = password

My current code (the working code):

WHERE user.username = username COLLATE utf8mb4_0900_ai_ci
AND user.password = password COLLATE utf8mb4_0900_ai_ci

Adding a collation only to the password to force a collation match to username or adding only to the username didn't work. Even though one parameter had the right collation to it already, it still had an error. So I added the same collation to both parameters and it worked.

Take note this is the actual COLLATION that is being used in my database already, but for some reason on procedure calls it wasn't consistent.

Please do remember to salt/hash your passwords when you are done, my code is still a work in progress.

Answer 2

So after struggling with this error:

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '='

I managed to fix this problem by changing the import database file from:

CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci NO SQL

This is if you are importing functions/procedures/triggers, which my database had a ton of all of these features... I changed that to:

CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

I really hope this helps someone. I know the above was helpful, but it still took me a few hours to turn that into a solution. Thanks

Answer 3

I know it's a little bit late but if this could save someone half a day of swearing it's still worth putting it down.

So, my setup was like this: 10.1.22-MariaDB, utf8mb4_general_ci. All good, I restored a dump of my database, all went OK.

The database was originally in utf8_general_ci, but for some reasons was restored as utf8_unicode_ci. Changed that back to utf8_general_ci and checked there were no artifacts in the database like columns or table definitions collated as utf8_unicode_ci instead of utf8_general_ci

Trying to update a specific table resulted in an illegal mix of collation without any apparent reasons.

It boiled down to be in fact not the table itself but the associated trigger.

In fact the trigger called a procedure that had no collation info in my database, but that had a utf8_unicode_ci collation in information_schema.ROUTINES.DATABASE_COLLATION.

Recreating the procedure in the context of the new database collation solved my issue.

Answer 4

As documented under Collation of Expressions:

MySQL assigns coercibility values as follows:

[ deletia ]

• The collation of a column or a stored routine parameter or local variable has a coercibility of 2.

[ deletia ]

MySQL uses coercibility values with the following rules to resolve ambiguities:

[ deletia ]

• If both sides have the same coercibility, then:

  • If both sides are Unicode, or both sides are not Unicode, it is an error.

You could add an explicit COLLATE clause in your expression to force one of the operands to have an explicit collation with a lower coercibility value:

USER_PASSWORD=ip_user_password COLLATE 'latin1_general_cs'

You might even want to consider latin1_bin in this case?

In any event, you should not be storing passwords in plaintext. Instead, store salted hashes of your users' passwords and simply verify that the hash matches that which is stored.