mysqldump 5.1.36 unable to successfully dump a MySQL database in UTF-8 character encoding issues

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:

http://bugs.mysql.com/bug.php?id=28969

“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:

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

  2. 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 */;

    to:

    /*!40101 SET NAMES utf8 */;

    then save the file

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

4 Comments to mysqldump 5.1.36 unable to successfully dump a MySQL database in UTF-8 character encoding issues

  1. kanchan's Gravatar kanchan
    October 22, 2012 at 4:50 am | Permalink

    It’s doesn’t work for me.

    I’m getting error when executing query in query ediotor like – “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax”

    My db name is ‘rwm’ can you send me a query

  2. December 7, 2012 at 2:57 pm | Permalink

    After pulling lots of hairs trying to move data between two mysql databases, this solution worked like a charm for me. The bit about editing the line is the life saver! No other solution worked.
    MySQL version 5.1.61

  3. Piotr's Gravatar Piotr
    March 30, 2013 at 6:58 am | Permalink

    I needed to do one more thing. Changed all instances of “CHARSET=latin1” to “CHARSET=utf8” in the dump file. Then all went smooth. Thanks for the information!

Leave a Reply

You can use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>