Loading UTF-8 encoded dump into MySQL

Question

I've been pulling my hear out over this problem for a few hours yesterday:

I've a database on MySQL 4.1.22 server with encoding set to "UTF-8 Unicode (utf8)" (as reported by phpMyAdmin). Tables in this database have default charset set to latin2. But, the web application (CMS Made Simple written in PHP) using it displays pages in utf8...

However screwed up this may be, it actually works. The web app displays characters correctly (mostly Czech and Polish are used).

I run: "mysqldump -u xxx -p -h yyy dbname > dump.sql". This gives me an SQL script which:

• looks perfect in any editor (like Notepad+) when displaying in UTF-8 - all characters display properly
• all tables in the script have default charset set to latin2
• it has "/*!40101 SET NAMES latin2 */;" line at the beginning (among other settings)

Now, I want to export this database to another server running on MySQL 5.0.67, also with server encoding set to "UTF-8 Unicode (utf8)". I copied the whole CMS Made Simple installation over, copied the dump.sql script and ran "mysql -h ddd -u zzz -p dbname < dump.sql". After that, all the characters are scrambled when displaying CMSMS web pages.

I tried setting:
SET character_set_client = utf8;
SET character_set_connection = latin2;

And all combinations (just to be safe, even if it doesn't make any sense to me): latin2/utf8, latin2/latin2, utf8/utf8, etc. - doesn't help. All characters still scrambled, however sometimes in a different way :).

I also tried replacing all latin2 settings with utf8 in the script (set names and default charsets for tables). Nothing.

Are there any MySQL experts here who could explain in just a few words (I'm sure it's simple after all) how this whole encoding stuff really works? I read 9.1.4. Connection Character Sets and Collations but found nothing helpful there.

Thanks, Matt

Answer 1

Did you try adding the --default-character-set=name option, like this:

mysql --default-character-set=utf8 -h ddd -u zzz -p dbname < dump.sql

I had that problem before and it worked after using that option.

Hope it helps!