Illegal mix of collations error in MySql

user_110087 0
Viewed 142774

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

mysql-error-1267 collation mysql
20 Answers

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);

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

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
user_13255750

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

enter image description here

user_60019860

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
user_129458840

In my case it was something strange. I read an api key from a file and then I send it to the server where a SQL query is made. The problem was the BOM character that the Windows notepad left, it was causing the error that says:

SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

I just removed it and everything worked like a charm

user_50325500 
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;
user_551590

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
user_1495030
Related