147

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?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Ali
  • 261,656
  • 265
  • 575
  • 769

10 Answers10

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

FreshPow
  • 6,782
  • 1
  • 15
  • 17
Ben Hughes
  • 14,075
  • 1
  • 41
  • 34
  • 28
    @Ben: It was initially developed by a Swedish company... That is the reason behind the annoying latin1_swedish_ci initial setting.. :( – Vajk Hermecz Sep 29 '14 at 07:45
  • 1
    I didn't have permissions to do the first statement but it worked just doing the table – Rob Sedgwick Mar 04 '15 at 14:04
  • It looks like this works for lots of people, but unfortunately I still have this problem even after trying all of the device in this thread. My database default collation stubbornly refuses to change from 'ucs2_bin' so even tried changing all the tables and the connection collation to 'usc2_bin' but I still get the error "SQL Error (1267): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (ucs2_bin,IMPLICIT) for operation '='". – bikeman868 Oct 15 '16 at 01:12
  • 1
    Is it really safe to do this operation on a production database full of data? – andyhasit Mar 20 '18 at 18:20
  • 2
    No, not really, in fact it's quite a bad idea. The application which connects probably sets a character set, and might output the data directly for example on the web with a charset header which now won't match the data. These commands are pretty dangerous on production, and should coincide with an audit of the application connecting to the database for any potential issues. – maaarghk Apr 04 '18 at 18:46
  • Thanks for this - I ended up here looking for something else, but I've been having trouble with phpMyAdmin creating new DBs with `latin1_swedish_ci` all the time, and it was getting annoying! Glad to have a solution :) – Rich Court Oct 23 '18 at 15:43
  • @Ben How would I implement this in docker-compose mysql using command? – Rajesh Samui Dec 09 '19 at 11:34
22
CONVERT(column1 USING utf8)

Solves my problem. Where column1 is the column which gives me this error.

Binaya Shrestha
  • 440
  • 3
  • 12
15

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';
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • Are both of these needed, or can i just do one of them? – Ali Jun 17 '09 at 18:23
  • ALTER DATABASE `myDb` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin . Would that work? This is done so it would affect all of my tables, not just one of them – Ali Jun 17 '09 at 18:32
  • 1
    ALTER DATABASE won't change your current table settings, only newly created ones. It though won't hurt to alter default charset for database too. – Quassnoi Jun 17 '09 at 19:03
  • SET NAMES and SET CHARACTER SET will change your connection encoding. You need to issue these commands each time your connect. You client library may support more elegant method to do it (php::mysqli does, php::mysql does not). – Quassnoi Jun 17 '09 at 19:04
  • Seems to work for now, i'll accept after some more testing. Do the second queries need to be run once, or at the start of each script? – Ali Jun 17 '09 at 19:06
  • Still not working, man. 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 昔 ã‹ã‚‰ ã‚ã‚‹ å ´æ‰€' – Ali Jun 17 '09 at 20:02
  • Could you please run the following: SHOW CREATE TABLE keyword; and post the result here? – Quassnoi Jun 17 '09 at 20:07
4

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;
vpgodara
  • 329
  • 2
  • 2
2

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

Stephan
  • 41
  • 2
2

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;
A Kunin
  • 42,385
  • 1
  • 17
  • 13
2

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

Nitin Nanda
  • 805
  • 2
  • 11
  • 27
1

Change the character set of the table to utf8

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8

Jamsheer Mohammed
  • 11
  • 2
1

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';
pyb
  • 4,813
  • 2
  • 27
  • 45
  • 1
    Thanks for posting. My db is already set to utf8, as well as my session character sets and collations are all either utf8 and binary. This is the only fix that worked without digging too far. – yg-dba May 28 '21 at 14:51
0

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

ITI
  • 59
  • 1
  • 3