Illegal mix of collations MySQL Error

Question

I m getting this strange error while processing a large number of data     Error Number  1267  Illegal mix of collations  latin1 swedish ci IMPLICIT  and  utf8 general ci COERCIBLE  for operation      SELECT COUNT    as num from keywords WHERE campaignId  12  AND LCASE keyword   hello again                                                      What can I do to resolve this  Can I escape the string somehow so this error wouldn t occur  or do I need to change my table encoding somehow  and if so  what should I change it to

User · Accepted Answer

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

User · Answer

I had my table originally created with CHARSET latin1  After table conversion to utf8 some columns were not converted  however that was not really obvious   You can try to run SHOW CREATE TABLE my table  and see which column was not converted or just fix incorrect character set on problematic column with query below  change varchar length and CHARSET and COLLATE according to your needs     ALTER TABLE  my table  CHANGE  my column   my column  VARCHAR 10  CHARSET utf8   COLLATE utf8 general ci NULL

User · Answer

Change the character set of the table to utf8     ALTER TABLE your table name CONVERT TO CHARACTER SET utf8

User · Answer

Use following statement for error   be careful about your data take backup if data have in table    ALTER TABLE your table name CONVERT TO CHARACTER SET utf8 COLLATE utf8 general ci

User · Answer

I found that using cast   was the best solution for me   cast Format amount   Standard   AS CHAR CHARACTER SET utf8  AS Amount   There is also a convert   function  More details on it here  Another resource here

User · Answer

After making your corrections listed in the top answer  change the default settings of your server   In your   etc my cnf d server cnf  or where ever it s located add the defaults to the  mysqld  section so it looks like this    mysqld  character-set-server utf8 collation-server utf8 general ci   Source  https   dev mysql com doc refman 5 7 en charset-applications html

User · Answer

My user account did not have the permissions to alter the database and table  as suggested in this solution  If  like me  you don t care about the character collation  you are using the     operator   you can apply the reverse fix  Run this before your SELECT  SET collation connection    latin1 swedish ci

User · Answer

You should set both your table encoding and connection encoding to UTF-8   ALTER TABLE keywords CHARACTER SET UTF8  -- run once   and  SET NAMES  UTF8   SET CHARACTER SET  UTF8

User · Answer

In general the best way is to Change the table collation  However I have an old application and are not really able to estimate the outcome whether this has side effects  Therefore I tried somehow to convert the string into some other format that solved the collation problem  What I found working is to do the string compare by converting the strings into a hexadecimal representation of it s characters  On the database this is done with HEX column   For PHP you may use this function   public static function strToHex  string         hex           for   i 0   i lt strlen  string    i              ord   ord  string  i             hexCode   dechex  ord            hex    substr  0   hexCode  -2             return strToUpper  hex       When doing the database query  your original UTF8 string must be converted first into an iso string  e g  using utf8 decode   in PHP  before using it in the DB  Because of the collation type the database cannot have UTF8 characters inside so the comparism should work event though this changes the original string  converting UTF8 characters that are not existend in the ISO charset result in a   or these are removed entirely   Just make sure that when you write data into the database  that you use the same UTF8 to ISO conversion

User · Answer

CONVERT column1 USING utf8    Solves my problem  Where column1 is the column which gives me this error

[mysql] Illegal mix of collations MySQL Error

Examples related to mysql

Examples related to sql

Examples related to mysql-error-1267