CODEWITHSUNDEEP
phpmysqlcharacter-encoding
user264230
user264230

Reputation: 650

Illegal mix of collations PHP MYSQL, latin1_swedish_ci and utf8_general_ci

I get the following coallation problem in my application when I try to select something where two strings are equal: 

SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

In the stacktrace I can see the parameter Lamellt\xE4ckning which means Lamelltäckning and I think my parameter implicitly invokes the latin1_swedish_ci coallation.

my whole database uses this:

DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci

When I insert Strings from PHP I just make a simple insert:

$name = "Lamelltäcke";
$db->update("insert into....");

The data I am now trying to use comes from a CSV file and I do not know if I can solve this just by setting coallation i some way or if I need to convert the String in some way

What is the problem here? And how can I solve it?

It seams to be a problem when I insert data from PHP. I made the pdo connection like this:

$db = new \PDO($dsn, $config->db_user, $config->db_pass, array(\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

When I define a string like 

$str = "åuuäuuö";

In PHP and insert it there is no problem. But when I receive the string from a post request I can echo it out just fine 

 "åuuäuuö"

In the database however it now gets inserted like 

"?uu?uu?"

mb_detect_encoding($str);

gives: UTF-8

The problem was the encoding of the string itself. My database uses UTF-8 but the encoding was ISO-8859-x. To make everything worse my Java client also had another encoding which made this hard to debug. It is called "Quoted String".

What finally helped me solve the problem was this piece of PHP code which takes a String and converts it from all possible encodings to UTF-8 and prints it. Look for a row where your string is printed correctly and there is your encoding of the string. Then when you obtain the correct encoding, rencode your string using mb_convert_encoding.

$str = "String of unknown encoding with chars like äåö or something else";

foreach(mb_list_encodings() as $chr){
            echo mb_convert_encoding($str, 'UTF-8', $chr)." : ".$chr."\r\n";
        }

A note is to make sure that the client also uses the right encoding. In my case this was a Java program, in a normal case this would be your webapp/browser and .

Upvotes: 0

Views: 4778

Answers (1)

Laurent W.
Laurent W.

Reputation: 3783

  • Try to convert your PHP file to UTF8
  • Right after database connection, make a query with SET NAMES 'utf8'

Also, check that your field charset is UTF8.

Upvotes: 1

Related Questions