MySQL / MySQLDump / phpMyAdmin ignore character sets

Question

(Sorry about my poor english, i am french)

I work on a MySQL database witch is configured to use UTF-8 / utf8_general-ci at all levels specified by MySQL : Server, Database, Table and Columns.

When I write or read data through PHP, it perfectly work. All my data used UTF-8.

My problem :

If I try to read theses data with the mysql programs, phpMyAdmin interface or export with mysqldump, all strings appear with strange accents like "Ã§" (it is UTF-8 read like ISO-8859-1).

I try to set and configure all MySQL character set related vars to use UTF-8, but nothing change :

mysql> SHOW VARIABLES LIKE "character\_set\_%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| character_set_client     | utf8  |
| character_set_connection | utf8  |
| character_set_database   | utf8  |
| character_set_filesystem | utf8  |
| character_set_results    | utf8  |
| character_set_server     | utf8  |
| character_set_system     | utf8  |
+--------------------------+-------+
7 rows in set (0.00 sec)

I also try (after set all vars to UTF-8) to insert a new line with MySQL itself. This new line is read by MySQL without problem (accents are fine), but if I read these line with my PHP program, it seems to be in ISO-8859-1, so displaying in UTF-8 failed.

I read all MySQL documentation about character set and collation, but i found no explaination or answers about this problem.

So, Somebody can help me to understand why MySQL program seems to don't care about character set and how fix it ?

Have you specified the correct connection charset when using your mysql programs/phpmyadmin as well …? — CBroe

darkelfe darkelfe · Accepted Answer · 2013-11-21T08:59:47

I finally found the solution with another forum : http://fr.openclassrooms.com/forum/sujet/mysql-phpmyadmin-etc-ignorent-les-encodages (fr)

The problem was in PHP, at PDO configuration level : I didn't give the "charset" variable to the MySQL DSN, so when MySQL receive my data (in UTF-8), he thinks it was latin1 so to write in UTF-8, he translate data to UTF-8 again.

The solution is simple : just precise charset to UTF-8 in MySQL DSN (PDO) : http://fr2.php.net/manual/en/ref.pdo-mysql.connection.php.

Bye.

MySQL / MySQLDump / phpMyAdmin ignore character sets

1 Answers