Search code examples
mysqlcollationmysql-error-1267

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

Solution

  • 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.