{"id":171,"date":"2012-02-24T20:53:44","date_gmt":"2012-02-25T01:53:44","guid":{"rendered":"http:\/\/jamesroberts.name\/blog\/?p=171"},"modified":"2012-02-24T21:08:00","modified_gmt":"2012-02-25T02:08:00","slug":"mysqldump-5-1-36-unable-to-successfully-dump-a-mysql-database-in-utf-8-character-encoding-issues","status":"publish","type":"post","link":"https:\/\/jamesroberts.name\/blog\/2012\/02\/24\/mysqldump-5-1-36-unable-to-successfully-dump-a-mysql-database-in-utf-8-character-encoding-issues\/","title":{"rendered":"mysqldump 5.1.36 unable to successfully dump a MySQL database in UTF-8 character encoding issues"},"content":{"rendered":"

Today I had a character encoding problem when importing some data from a mysqldump from a contractor we had hired to do some work.\u00a0 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.\u00a0 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.\u00a0 As it turns out, his mysql and mysqldump versions were older than mine and it was messing everything up.\u00a0 Apparently, according to this mysql bug post:<\/p>\n

http:\/\/bugs.mysql.com\/bug.php?id=28969<\/a><\/p>\n

“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)”.\u00a0 We were able to fix this issue by following these steps:<\/p>\n

    \n
  1. add latin1 default character set to the mysql dump command from the older versioned database, like so:\n
    mysqldump -u dbuser -p --default-character-set=latin1 my_database_name > db_dump.sql<\/pre>\n
    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.\n<\/li>\n
  2. before importing into the newer version database, open the in the newly created dump file and change the line that reads:\n
    \/*!40101 SET NAMES latin1 *\/;<\/pre>\n
     to: <\/p>\n
    \/*!40101 SET NAMES utf8 *\/;<\/pre>\n
     then save the file<\/li>\n
  3. import to the newer version database using:\n
    mysql -u dbuser -p --default-character-set=utf8 my_database_name < db_dump.sql<\/pre>\n<\/li>\n<\/ol>\n
    Took a lot of emails back and forth to finally come to this solution.\u00a0 Hopefully this helps some one and thank god for the original bug post linked above!<\/p>\n","protected":false},"excerpt":{"rendered":"
    Today I had a character encoding problem when importing some data from a mysqldump from a contractor we had hired to do some work.\u00a0 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[33],"tags":[38,65,40],"_links":{"self":[{"href":"https:\/\/jamesroberts.name\/blog\/wp-json\/wp\/v2\/posts\/171"}],"collection":[{"href":"https:\/\/jamesroberts.name\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jamesroberts.name\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jamesroberts.name\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jamesroberts.name\/blog\/wp-json\/wp\/v2\/comments?post=171"}],"version-history":[{"count":12,"href":"https:\/\/jamesroberts.name\/blog\/wp-json\/wp\/v2\/posts\/171\/revisions"}],"predecessor-version":[{"id":173,"href":"https:\/\/jamesroberts.name\/blog\/wp-json\/wp\/v2\/posts\/171\/revisions\/173"}],"wp:attachment":[{"href":"https:\/\/jamesroberts.name\/blog\/wp-json\/wp\/v2\/media?parent=171"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jamesroberts.name\/blog\/wp-json\/wp\/v2\/categories?post=171"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jamesroberts.name\/blog\/wp-json\/wp\/v2\/tags?post=171"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}