MySQL database collation and character set.

Question

I have a mySQL database that has collation and character sets as follows:

mysql> show variables like "character_set_database";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like "collation_database";
+--------------------+-----------------+
| Variable_name      | Value           |
+--------------------+-----------------+
| collation_database | utf8_unicode_ci |
+--------------------+-----------------+
1 row in set (0.00 sec)

I have a table of countries, which consists of an, id and name. When I try to import this country (as an example with 'foreign' characters)

São Tomé and Príncipe

I get the following error:

  Unable to execute INSERT statement. [wrapped: SQLSTATE[HY000]: General error:
1366 Incorrect string value: '\xE3\xA3o To...' for column 'name' at row 1]

I have imported this data in the past - Does anyone have any idea why I cant import this data (as yml fixtures)?. I have succesfully imported it in the past. I dont rememeber what has changed since though

Answer 1

\xE3\xA3o To...

Well it's right, that's not a valid byte sequence. ã in UTF-8 should be \xC3\xA3. Looks like somehow someone's loaded the import data as ISO-8859-1 and case-folded it to lowercase, transforming the C3 byte to E3. Naturally the results are no longer UTF-8.

Would need more detail about the import process to say more.

Answer 2

Put <?php header('Content-Type: text/plain; charset=utf-8'); ?> at the top of your fixtures.yml file.

Answer 3

I wouldn't use fixtures.yml to upload complex data like this. I've got a very similar situation/setup as you and I use MySQL's LOAD DATA INFILE without any problems.

LOAD DATA INFILE 'C:/development/cities.csv' INTO TABLE project.city CHARACTER SET utf8
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r';

... where the cities.csv is a UTF-8 export from OpenOffice Calc.