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

The encoding of the .sql text file has nothing to do with the database encoding. You need to tell MySQL to create the tables explicitly with a UTF-8 collation, by default it uses iso-8859-1.

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