Today I had a character encoding problem when importing some data from a mysqldump from a contractor we had hired to do some work. Everything about the dump was fine, the database, tables, and fields were all using utf8_unicode_ci utf8 collation, the dump file was encoded in utf8, and the database I was importing to was all utf8 as well. The contractor said he was able to re-import the dump into his server and local environment fine, but I was still unable to get the dump imported without all the utf8 characters getting garbled. As it turns out, his mysql and mysqldump versions were older than mine and it was messing everything up. Apparently, according to this mysql bug post:
“As of today (Feb 6, 2012) mysqldump 5.1.36 still exhibits the faulty behavior (unable to successfully dump a MySQL database in UTF-8)”. We were able to fix this issue by following these steps:
- add latin1 default character set to the mysql dump command from the older versioned database, like so:
mysqldump -u dbuser -p --default-character-set=latin1 my_database_name > db_dump.sql
You can also try adding a “-r” flag to this command which should allow non-latin characters to dump properly as well, but this did not work in our case.
- before importing into the newer version database, open the in the newly created dump file and change the line that reads:
/*!40101 SET NAMES latin1 */;
/*!40101 SET NAMES utf8 */;
then save the file
- import to the newer version database using:
mysql -u dbuser -p --default-character-set=utf8 my_database_name < db_dump.sql
Took a lot of emails back and forth to finally come to this solution. Hopefully this helps some one and thank god for the original bug post linked above!