Troubleshooting Illegal mix of collations error in mysql

Question

Am getting the below error when trying to do a select through a stored procedure in MySQL      Illegal mix of collations  latin1 general cs IMPLICIT  and  latin1 general ci IMPLICIT  for operation       Any idea on what might be going wrong here   The collation of the table is latin1 general ci and that of the column in the where clause is latin1 general cs

User · Answer

This code needs to be put inside Run SQL query/queries on database

SQL QUERY WINDOW

ALTER TABLE `table_name` CHANGE `column_name` `column_name`   VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;

Please replace table_name and column_name with appropriate name.

User · Answer

Below solution worked for me  CONVERT  Table1 FromColumn USING utf8        CONVERT Table2 ToColumn USING utf8

User · Answer

A possible solution is to convert the entire database to UTF8  see also this question

User · Answer

Sometimes it can be dangerous to convert charsets  specially on databases with huge amounts of data  I think the best option is to use the  binary  operator   e g   WHERE binary table1 column1   binary table2 column1

User · Answer

Solution if literals are involved   I am using Pentaho Data Integration and dont get to specify the sql syntax  Using a very simple DB lookup gave the error  Illegal mix of collations  cp850 general ci COERCIBLE  and  latin1 swedish ci COERCIBLE  for operation       The generated code was   SELECT DATA DATE AS latest DATA DATE FROM hr cc normalised data date v WHERE PSEUDO KEY       Cutting the story short the lookup was to a view and when I issued   mysql gt  show full columns from hr cc normalised data date v   ------------ ------------ ------------------- ------ -----    Field        Type         Collation           Null   Key    ------------ ------------ ------------------- ------ -----    PSEUDO KEY   varchar 1    cp850 general ci    NO             DATA DATE    varchar 8    latin1 general cs   YES           ------------ ------------ ------------------- ------ -----    which explains where the  cp850 general ci  comes from   The view was simply created with  SELECT  X          According to the manual literals like this should inherit their character set and collation from server settings which were correctly defined as  latin1  and  latin1 general cs  as this clearly did not happen I forced it in the creation of the view  CREATE OR REPLACE VIEW hr cc normalised data date v AS SELECT convert  X  using latin1  COLLATE latin1 general cs        AS PSEUDO KEY        DATA DATE FROM HR COSTCENTRE NORMALISED mV LIMIT 1    now it shows latin1 general cs for both columns and the error has gone away

User · Answer

TL DR  Either change the collation of one  or both  of the strings so that they match  or else add a COLLATE clause to your expression      What is this  collation  stuff anyway   As documented under Character Sets and Collations in General      A character set is a set of symbols and encodings  A collation is a set of rules for comparing characters in a character set  Let s make the distinction clear with an example of an imaginary character set       Suppose that we have an alphabet with four letters     A        B        a        b     We give each letter a number     A      0     B      1     a      2     b      3  The letter    A    is a symbol  the number 0 is the encoding for    A     and the combination of all four letters and their encodings is a character set       Suppose that we want to compare two string values     A    and    B     The simplest way to do this is to look at the encodings  0 for    A    and 1 for    B     Because 0 is less than 1  we say    A    is less than    B     What we ve just done is apply a collation to our character set  The collation is a set of rules  only one rule in this case      compare the encodings     We call this simplest of all possible collations a binary collation       But what if we want to say that the lowercase and uppercase letters are equivalent  Then we would have at least two rules   1  treat the lowercase letters    a    and    b    as equivalent to    A    and    B      2  then compare the encodings  We call this a case-insensitive collation  It is a little more complex than a binary collation       In real life  most character sets have many characters  not just    A    and    B    but whole alphabets  sometimes multiple alphabets or eastern writing systems with thousands of characters  along with many special symbols and punctuation marks  Also in real life  most collations have many rules  not just for whether to distinguish lettercase  but also for whether to distinguish accents  an    accent    is a mark attached to a character as in German            and for multiple-character mappings  such as the rule that               OE    in one of the two German collations     Further examples are given under Examples of the Effect of Collation  Okay  but how does MySQL decide which collation to use for a given expression   As documented under Collation of Expressions      In the great majority of statements  it is obvious what collation MySQL uses to resolve a comparison operation  For example  in the following cases  it should be clear that the collation is the collation of column charset name   SELECT x FROM T ORDER BY x  SELECT x FROM T WHERE x   x  SELECT DISTINCT x FROM T        However  with multiple operands  there can be ambiguity  For example   SELECT x FROM T WHERE x    Y         Should the comparison use the collation of the column x  or of the string literal  Y   Both x and  Y  have collations  so which collation takes precedence       Standard SQL resolves such questions using what used to be called    coercibility    rules     deletia        MySQL uses coercibility values with the following rules to resolve ambiguities          Use the collation with the lowest coercibility value    If both sides have the same coercibility  then          If both sides are Unicode  or both sides are not Unicode  it is an error    If one of the sides has a Unicode character set  and another side has a non-Unicode character set  the side with Unicode character set wins  and automatic character set conversion is applied to the non-Unicode side  For example  the following statement does not return an error   SELECT CONCAT utf8 column  latin1 column  FROM t1        It returns a result that has a character set of utf8 and the same collation as utf8 column  Values of latin1 column are automatically converted to utf8 before concatenating    For an operation with operands from the same character set but that mix a  bin collation and a  ci or  cs collation  the  bin collation is used  This is similar to how operations that mix nonbinary and binary strings evaluate the operands as binary strings  except that it is for collations rather than data types         So what is an  illegal mix of collations    An  illegal mix of collations  occurs when an expression compares two strings of different collations but of equal coercibility and the coercibility rules cannot help to resolve the conflict   It is the situation described under the third bullet-point in the above quotation   The particular error given in the question  Illegal mix of collations  latin1 general cs IMPLICIT  and  latin1 general ci IMPLICIT  for operation      tells us that there was an equality comparison between two non-Unicode strings of equal coercibility   It furthermore tells us that the collations were not given explicitly in the statement but rather were implied from the strings  sources  such as column metadata   That s all very well  but how does one resolve such errors    As the manual extracts quoted above suggest  this problem can be resolved in a number of ways  of which two are sensible and to be recommended    Change the collation of one  or both  of the strings so that they match and there is no longer any ambiguity   How this can be done depends upon from where the string has come  Literal expressions take the collation specified in the collation connection system variable  values from tables take the collation specified in their column metadata  Force one string to not be coercible   I omitted the following quote from the above      MySQL assigns coercibility values as follows          An explicit COLLATE clause has a coercibility of 0   Not coercible at all     The concatenation of two strings with different collations has a coercibility of 1    The collation of a column or a stored routine parameter or local variable has a coercibility of 2    A    system constant     the string returned by functions such as USER   or VERSION    has a coercibility of 3    The collation of a literal has a coercibility of 4    NULL or an expression that is derived from NULL has a coercibility of 5       Thus simply adding a COLLATE clause to one of the strings used in the comparison will force use of that collation    Whilst the others would be terribly bad practice if they were deployed merely to resolve this error    Force one  or both  of the strings to have some other coercibility value so that one takes precedence   Use of CONCAT   or CONCAT WS   would result in a string with a coercibility of 1  and  if in a stored routine  use of parameters local variables would result in strings with a coercibility of 2  Change the encodings of one  or both  of the strings so that one is Unicode and the other is not   This could be done via transcoding with CONVERT expr USING transcoding name   or via changing the underlying character set of the data  e g  modifying the column  changing character set connection for literal values  or sending them from the client in a different encoding and changing character set client   adding a character set introducer   Note that changing encoding will lead to other problems if some desired characters cannot be encoded in the new character set  Change the encodings of one  or both  of the strings so that they are both the same and change one string to use the relevant  bin collation   Methods for changing encodings and collations have been detailed above   This approach would be of little use if one actually needs to apply more advanced collation rules than are offered by the  bin collation

User · Answer

I had a similar problem  was trying to use the FIND IN SET procedure with a string variable   SET  my var    string1 string2   SELECT   from my table WHERE FIND IN SET column name  my var     and was receiving the error      Error Code  1267  Illegal mix of collations  utf8 unicode ci IMPLICIT    and  utf8 general ci IMPLICIT  for operation  find in set    Short answer   No need to change any collation YYYY variables  just add the correct collation next to your variable declaration  i e   SET  my var    string1 string2  COLLATE utf8 unicode ci  SELECT   from my table WHERE FIND IN SET column name  my var     Long answer   I first checked the collation variables   mysql gt  SHOW VARIABLES LIKE  collation         ---------------------- -----------------        Variable name          Value                  ---------------------- -----------------        collation connection   utf8 general ci        ---------------------- -----------------        collation database     utf8 general ci        ---------------------- -----------------        collation server       utf8 general ci        ---------------------- -----------------    Then I checked the table collation   mysql gt  SHOW CREATE TABLE my table   CREATE TABLE  my table       id  int 11  NOT NULL AUTO INCREMENT     column name  varchar 40  COLLATE utf8 unicode ci DEFAULT NULL    PRIMARY KEY   id     ENGINE MyISAM AUTO INCREMENT 125 DEFAULT CHARSET utf8 COLLATE utf8 unicode ci    This means that my variable was configured with the default collation of utf8 general ci while my table was configured as utf8 unicode ci   By adding the COLLATE command next to the variable declaration  the variable collation matched the collation configured for the table

User · Answer

Adding my 2c to the discussion for future googlers   I was investigating a similar issue where I got the following error when using custom functions that recieved a varchar parameter   Illegal mix of collations  utf8 unicode ci IMPLICIT  and   utf8 general ci IMPLICIT  for operation       Using the following query   mysql gt  show variables like  collation database        -------------------- -----------------        Variable name        Value                  -------------------- -----------------        collation database   utf8 general ci        -------------------- -----------------    I was able to tell that the DB was using utf8 general ci  while the tables were defined using utf8 unicode ci   mysql gt  show table status       -------------- -----------------        Name           Collation              -------------- -----------------        my view        NULL                    my table       utf8 unicode ci             Notice that the views have NULL collation  It appears that views and functions have collation definitions even though this query shows null for one view  The collation used is the DB collation that was defined when the view function were created   The sad solution was to both change the db collation and recreate the views functions to force them to use the current collation    Changing the db s collation   ALTER DATABASE mydb DEFAULT COLLATE utf8 unicode ci   Changing the table collation   ALTER TABLE mydb CONVERT TO CHARACTER SET utf8 COLLATE utf8 unicode ci     I hope this will help someone

User · Answer

I personnaly had this problem in a procedure  If you dont want to alter table you can try to convert your parameter into the procedure   I ve try sevral use of collate  with a set into the select  but none works for me  CONVERT my param USING utf32   did the trick

User · Answer

You can try this script  that converts all of your databases and tables to utf8

User · Answer

I used ALTER DATABASE mydb DEFAULT COLLATE utf8 unicode ci   but didn t work   In this query   Select   from table1  table2 where table1 field   date format table2 field   H      This work for me   Select   from table1  table2 where concat table1 field    date format table2 field   H      Yes  only a concat

User · Answer

This is generally caused by comparing two strings of incompatible collation or by attempting to select data of different collation into a combined column   The clause COLLATE allows you to specify the collation used in the query   For example  the following WHERE clause will always give the error you posted   WHERE  A  COLLATE latin1 general ci    A  COLLATE latin1 general cs   Your solution is to specify a shared collation for the two columns within the query  Here is an example that uses the COLLATE clause   SELECT   FROM table ORDER BY key COLLATE latin1 general ci    Another option is to use the BINARY operator      BINARY str is the shorthand for CAST str AS BINARY     Your solution might look something like this   SELECT   FROM table WHERE BINARY a   BINARY b    or   SELECT   FROM table ORDER BY BINARY a

User · Answer

MySQL really dislikes mixing collations unless it can coerce them to the same one  which clearly is not feasible in your case    Can t you just force the same collation to be used via a COLLATE clause    or the simpler BINARY shortcut if applicable

User · Answer

If the columns that you are having trouble with are  hashes   then consider the following     If the  hash  is a binary string  you should really use BINARY      datatype   If the  hash  is a hex string  you do not need utf8  and should avoid such because of character checks  etc   For example  MySQL s MD5      yields a fixed-length 32-byte hex string   SHA1      gives a 40-byte hex string   This could be stored into CHAR 32  CHARACTER SET ascii  or 40 for sha1    Or  better yet  store UNHEX MD5       into BINARY 16    This cuts in half the size of the column    It does  however  make it rather unprintable    SELECT HEX hash      if you want it readable   Comparing two BINARY columns has no collation issues

User · Answer

If you have phpMyAdmin installed  you can follow the instructions given in the following link  https   mediatemple net community products dv 204403914 default-mysql-character-set-and-collation You have to match the collate of the database with that of all the tables  as well as the fields of the tables and then recompile all the stored procedures and functions  With that everything should work again

User · Answer

One another source of the issue with collations is mysql proc table  Check collations of your storage procedures and functions   SELECT   p db  p db collation  p type  COUNT    cnt FROM mysql proc p GROUP BY p db  p db collation  p type    Also pay attention to mysql proc collation connection and mysql proc character set client columns

User · Answer

Very interesting     Now  be ready   I looked at all of the  add collate  solutions and to me  those are band aid fixes   The reality is the database design was  bad    Yes  standard changes and new things gets added  blah blah  but it does not change the bad database design fact   I refuse to go with the route of adding  collate  all over the SQL statements just to get my query to work   The only solution that works for me and will virtually eliminate the need to tweak my code in the future is to re-design the database tables to match the character set that I will live with and embrace for the long term future   In this case  I choose to go with the character set  utf8mb4    So the solution here when you encounter that  illegal  error message is to re-design your database and tables   It is much easier and quicker then it sounds   Exporting your data and re-importing it from a CSV may not even be required   Change the character set of the database and make sure all the character set of your tables matches   Use these commands to guide you   SHOW VARIABLES LIKE  collation database   SHOW TABLE STATUS    Now  if you enjoy adding  collate  here and there and beef up your code with forces fulls  overrides   be my guess

[mysql] Troubleshooting "Illegal mix of collations" error in mysql

Examples related to mysql

Examples related to collation