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 ffffd -u zzz -p dbname < dump.sql

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

Hope it helps!

Answer 2

Ugh... ok, seems I found a solution.

MySQL isn't the culprit here. I did a simple dump and load now, with no changes to the dump.sql script - meaning I left "set names latin2" and tables charsets as they were. Then I switched my original CMSMS installation over to the new database and... it worked correctly. So actually encoding in the database is ok, or at least it works fine with CMSMS installation I had at my old hosting provider (CMSMS apparently does funny things with characters encoding).

To make it work on my new hosting provider, I actually had to add this line to lib/adodb/drivers/adodb-mysql.inc.php in CMSMS installation:

mysql_query('set names latin2',$this->_connectionID);

This is a slightly modified solution from this post. You can find the exact line there as well. So it looks like mysql client configuration issue.

Answer 3

SOLUTION for me:

set this option in your php file, after mysql_connect (or after mysql_select_db)..

mysql_query("SET NAMES 'utf8'");