Illegal mix of collations error in MySql

Question

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  gt  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  gt  4 ORDER BY TheAverage DESC  TheCount DESC   The table is   id  username  rating  month

User · Answer

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  gt  4 ORDER BY         TheAverage DESC  TheCount DESC

User · Answer

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

User · Answer

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

User · Answer

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

User · 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

User · Answer

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  gt  4 ORDER BY TheAverage DESC  TheCount DESC

User · Answer

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 · Answer

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

User · Answer

HAvING TheCount  gt  4 AND username IN  SELECT username FROM users WHERE gender 1    but why i am answering  you dont voted me as right answer

User · Answer

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 · Answer

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 · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You need to change each column Collation from latin1 general ci to latin1 swedish ci

User · Answer

Check that your users gender column is an INTEGER  Try  alter table users convert to character set latin1 collate latin1 swedish ci

User · Answer

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

User · Answer

Use ascii bin where ever possible  it will match up with almost any collation  A username seldom accepts special characters anyway

User · Answer

You need to set  utf8  for all parameters in each Function  It s my case   enter image description here

User · Answer

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  39 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

[mysql] Illegal mix of collations error in MySql

Examples related to mysql

Examples related to collation

Examples related to mysql-error-1267