[mysql] Illegal mix of collations error in MySql

Just got this answer from a previous question and it works a treat!

SELECT username, (SUM(rating)/COUNT(*)) as TheAverage, Count(*) as TheCount 
FROM ratings WHERE month='Aug' GROUP BY username HAVING TheCount > 4
ORDER BY TheAverage DESC, TheCount DESC

But when I stick this extra bit in it gives this error:

Documentation #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '='

SELECT username, (SUM(rating)/COUNT(*)) as TheAverage, Count(*) as TheCount FROM 
ratings WHERE month='Aug' 
**AND username IN (SELECT username FROM users WHERE gender =1)**
GROUP BY username HAVING TheCount > 4 ORDER BY TheAverage DESC, TheCount DESC

The table is:

id, username, rating, month

SELECT  username, AVG(rating) as TheAverage, COUNT(*) as TheCount
FROM    ratings
        WHERE month='Aug'
        AND username COLLATE latin1_general_ci IN
        (
        SELECT  username
        FROM    users
        WHERE   gender = 1
        )
GROUP BY
        username
HAVING
        TheCount > 4
ORDER BY
        TheAverage DESC, TheCount DESC;

Was getting Illegal mix of collations while creating a category in Bagisto. Running these commands (thank you @Quy Le) solved the issue for me:

--set utf8 for connection

SET collation_connection = 'utf8_general_ci'

--change CHARACTER SET of DB to utf8

ALTER DATABASE dbName CHARACTER SET utf8 COLLATE utf8_general_ci

--change category tables

ALTER TABLE categories CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

ALTER TABLE category_translations CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

• Check that your users.gender column is an INTEGER.
• Try: alter table users convert to character set latin1 collate latin1_swedish_ci;

I also got same error, but in my case main problem was in where condition the parameter that i'm checking was having some unknown hidden character (+%A0)

When A0 convert I got 160 but 160 was out of the range of the character that db knows, that's why database cannot recognize it as character other thing is my table column is varchar

• the solution that I did was I checked there is some characters like that and remove those before run the sql command

• ex:- preg_replace('/\D/', '', $myParameter);

Make sure your version of MySQL supports subqueries (4.1+). Next, you could try rewriting your query to something like this:

SELECT ratings.username, (SUM(rating)/COUNT(*)) as TheAverage, Count(*) as TheCount FROM ratings, users 
WHERE ratings.month='Aug' and ratings.username = users.username
AND users.gender = 1
GROUP BY ratings.username
HAVING TheCount > 4 ORDER BY TheAverage DESC, TheCount DESC

HAvING TheCount > 4 AND username IN (SELECT username FROM users WHERE gender=1)

but why i am answering, you dont voted me as right answer :)

If you want to avoid changing syntax to solve this problem, try this:

Update your MySQL to version 5.5 or greater.

This resolved the problem for me.

[MySQL]

In these (very rare) cases:

• two tables that really need different collation types

• values not coming from a table, but from an explicit enumeration, for instance:

  SELECT 1 AS numbers UNION ALL SELECT 2 UNION ALL SELECT 3

you can compare the values between the different tables by using CAST or CONVERT:

CAST('my text' AS CHAR CHARACTER SET utf8)

CONVERT('my text' USING utf8)

See CONVERT and CAST documentation on MySQL website.

I got this same error inside a stored procedure, in the where clause. i discovered that the problem ocurred with a local declared variable, previously loaded by the same table/column.

I resolved it casting the data to single char type.

You need to change each column Collation from latin1_general_ci to latin1_swedish_ci

Use ascii_bin where ever possible, it will match up with almost any collation. A username seldom accepts special characters anyway.

I had this problem not because I'm storing in different collations, but because my column type is JSON, which is binary.

Fixed it like this:

select table.field COLLATE utf8mb4_0900_ai_ci AS fieldName

You need to set 'utf8' for all parameters in each Function. It's my case:

enter image description here

Here's how to check which columns are the wrong collation:

SELECT table_schema, table_name, column_name, character_set_name, collation_name

FROM information_schema.columns

WHERE collation_name = 'latin1_general_ci'

ORDER BY table_schema, table_name,ordinal_position; 

And here's the query to fix it:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET latin1 COLLATE 'latin1_swedish_ci';

Link

The problem here mainly, just Cast the field like this cast(field as varchar) or cast(fields as date)

In short, this error is caused by MySQL trying to do an operation on two things which have different collation settings. If you make the settings match, the error will go away. Of course, you need to choose the right setting for your database, depending on what it is going to be used for.

Here's some good advice on choosing between two very common utf8 collations: What's the difference between utf8_general_ci and utf8_unicode_ci

If you are using phpMyAdmin you can do this systematically by working through the tables mentioned in your error message, and checking the collation type for each column. First you should check which is the overall collation setting for your database - phpMyAdmin can tell you this and change it if necessary. But each column in each table can have its own setting. Normally you will want all these to match.

In a small database this is easy enough to do by hand, and in any case if you read the error message in full it will usually point you to the right place. Don't forget to look at the 'structure' settings for columns with subtables in as well. When you find a collation that does not match you can change it using phpMyAdmin directly, no need to use the query window. Then try your operation again. If the error persists, keep looking!

I have the same problem with collection warning for a field that is set from 0 to 1. All columns collections was the same. We try to change collections again but nothing fix this issue.

At the end we update the field to NULL and after that we update to 1 and this overcomes the collection problem.

I think you should convert to utf8

--set utf8 for connection
SET collation_connection = 'utf8_general_ci'
--change CHARACTER SET of DB to utf8
ALTER DATABASE dbName CHARACTER SET utf8 COLLATE utf8_general_ci
--change CHARACTER SET of table to utf8
ALTER TABLE tableName CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

I was getting this same error on PhpMyadmin and did the solution indicated here which worked for me

ALTER TABLE table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

Illegal mix of collations MySQL Error Also I would recommend going with General instead of swedish since that one is default and not to use the language unless your application is using Swedish.