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.

Answer 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!

Answer 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;

-MySQL server was configured with utf8 Etc.

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.