Illegal Mix of Collations MySQL Error

Illegal mix of collations MySQL Error

SET collation_connection = 'utf8_general_ci';

then for your databases

ALTER DATABASE your_database_name CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

MySQL sneaks swedish in there sometimes for no sensible reason.

Illegal mix of collations error in MySql

Check the collation type of each table, and make sure that they have the same collation.

After that check also the collation type of each table field that you have use in operation.

I had encountered the same error, and that tricks works on me.

Illegal mix of collations - MySQL fake table

It seems like that your other (actual) table is using utf8_general_ci; while your database connection/server configuration is set for latin1_swedish_ci. It can be other way around too.

Nevertheless, we can use CONVERT() function, to change the "fake table" to use utf8 (if the other (actual) table is using utf8).

SELECT CONVERT('B' USING utf8) AS a_name UNION ALL  
SELECT CONVERT('A' USING utf8) AS a_name

Illegal mix of collations error in mysql query

Stick to one encoding/collation for your entire system. Right now you seem to be using UTF8 one place and latin1 in another place. Convert the latter to use UTF8 as well and you'll be good.

You can change the collation to UTF8 using

alter table  convert to character set utf8 collate utf8_general_ci;

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

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.

---

Related Topics