UTF8 problem with MySQL 5

Question

I'm migrating my WordPress blog and phpBB Forum into a new hosting server. I am using phpMyAdmin to import the SQL script from the database in the previous site.

When I open the .sql script with Kate, it says it uses UTF8 as encoding. When I import the sql in the new server, I have the option in phpMyAdmin to choose the encoding, where utf8 is selected by default.

Still, when I finish importing the database, I read the posts text directly in phpMyAdmin, and see characters such as "é", "ñ", etc. which haven't been "interpreted" and been replaced with weird characters insted.

I can see my WordPress installation is not working also. Apparently there's a problem with this encoding thing, but I think the problem is in the MySQL database or phpMyAdmin and not WordPress.

The versions of MySQL are practically the same, MySQL 5, but a different revision. Also, there was no problem when migrating the forum database, so this is even stranger...

I'm lost as to how to fix this... Any ideas are welcome.

Solution 1

Have you tried adding

SET NAMES 'utf8';

to your sql dump?

The thing with utf8 or encodings in general is that in order to be successfull, you have to make sure that:

• the file is encoded utf8 without signature
• the default encoding of the mysql server is set to utf8
• the connection is utf8 (that's why you put SET NAMES 'utf8' into your sql-file).
• all tables and columns have the right encoding and charset
• all your webfiles have to be utf8 encoded as well. And it doesn't work to just add the correct header. You have to open the file, check if the encoding is utf8, if not, cut everything, change the encoding to utf8 and paste everything back. It doesn't work, if you just change the encoding and save the file!

Solution 2

Thanks a lot for your answers. I found the solution, and I feel really dumb not to have realized it before. Everything was supposed to work fine:

-File was encoded with utf8 -Tables were declared with utf8:

SET character_set_client = utf8;

What I didn't realize in the first place is I was using a database backup from cPanel's backup wizard. As soon as I realized this, I used phpMyAdmin on the old server to export the database, imported it in the new server, and presto, problem fixed.

phpMyAdmin understands phpMyAdmin better than cPanel :P Apparently cPanel exports the script for it to be imported with cPanel itself. This would have most probably worked as well, but I trust phpMyAdmin better.

Thanks a lot for your answers though.

Solution 3

I just ran across the same problem, since I wrestled with it for about an hour and it broke many of my sites, I figured I'd look and see if anyone else was struggling with it and post a solution: The solution quite simply is to specify the import character set on import.

If you have SSH access to your host, first drop all your tables again in new database again, then run this command in the SSH session (assuming your old dump file is olddatabase.dump.sql):

$ mysql -h host -u username -p password --default-character-set=utf8 database < olddatabase.dump.sql (replace host, username, password, and database with appropriate values)

This is the simplest and most straightforward way to solve the problem.

Comments

[LandP]

This is a good summary of the requirements, but the last one is not really necessary if the files are Latin1 and you don't paste in non-Latin1 characters (i.e. encode them as &pound; instead). I have a lot of legacy Latin1 material which still generates as valid UTF8, because Latin1 is a subset of UTF8. So migrating to UTF8 is not really all that hard.