While I was migrating this blog I found a very odd situation: apparently exporting from a UTF-8 MySQL instance to another UTF-8 MySQL instance… didn’t work.

I’m not sure what caused the problem, however thanks to a debugging hand from Gary Pendergast I managed to solve it. I’m sharing this information since this seems a common issue, and having it at hand can help.

The server I was trying to export had the following characteristics, with a some rather old versions:

• phpMyAdmin 3.3.10.4
• MySQL server 5.1.56-log
• MySQL encoding UTF-8 (utf8)
• MySQL connection collation utf8_unicode_ci
• Tables collation utf8_general_ci

The receiving end:

• phpMyAdmin 4.2.8.1
• MySQL server 5.6.23-72.1-log
• MySQL encoding UTF-8 (utf8)
• MySQL connection collation utf8mb4_unicode_ci
• Tables collation utf8_general_ci

Note that the receiving end didn’t seem to be the problem: when the encoding was correct in the exported SQL file, I was able to see correctly characters like”守” even just by opening it in Sublime Text. Such file imported correctly by selecting UTF-8 in the import dropdown format.

In short: I didn’t find a way to export from phpMyAdmin that didn’t screw up the encoding (which meant that a character like “守” showed up as “守”).

The fix meant using a SSH terminal connection:

1. mysqldump --default-character-set=latin1 --user=youruser --password --host=databaseaddress datbasename -r backup-latin1-r.sql
2. Open the exported file backup-latin1-r.sql and replace toward the beginning of the file this:
   /*!40101 SET NAMES latin1 */;
   with this:
   /*!40101 SET NAMES utf8 */;
3. Done. Import and it should work.

The explanation I found about this was that exporting as “latin1” is similar to a raw export in terms of encoding. However, since we know that the real encoding is really UTF-8 and not Latin1, we change the configuration line in the file, so it gets then imported correctly.

Note on MySQL Error 1064

Unfortunately in my case I had an extra issue: I had a truncated UTF-8 character which meant that when I tried to export the file as above I ended up with this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'some text string' at line 1

This was because the truncated character was a UTF-8 character that once truncated and read as UTF-8 “captured” the following character in the file, which was a closing quote that made the SQL query valid.

If you get a similar error, check just before the string (and yes, “line 1” is misleading) and you might be able to find the issue.

I hope this can help.