Faulty restore of the database, encoding issue

Question

I messed up the settings with W3 Total Cache (tried to import all the media to my library, didn't work out well, broke all my links to every picture). So I took my latest backup of the database, copy/paste the _post and _postmeta tables inside my phpmyadmin. It brought back the links and pictures as expected, but now all the french characters (à,é,è etc) are not displayed properly.

I took the backup from the plugin WP-DBManager, which doesn't seem to handle UTF-8 properly. What's the fastest way to correct the issue ?

Thanks

Edited for more details: The SQL backup header is

DROP TABLE IF EXISTS `hojd_posts`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;

However I have those characters badly encoded coming directly in my sql commands (eg: français for "français")...

Answer 1

You might be able to solve this if you have a text editor with good encoding support. That way, you could switch between the Latin 1 and the UTF-8 encoding until you have the right combination. I use SubEthaEdit which can convert but also reinterpret a file when you change the encoding.

The ç should be encoded as c3 a7 in UTF-8 when you view them as bytes. What could be happening here is that the file was interpreted as Latin 1 first, where c3 a7 means ç, and then saved as UTF-8, where ç is saved as c3 83 c2 a7. You want the c3 a7 version.

The way to get back to a nice ç is to open the file as UTF-8, save it as Latin-1, and then open it again as if it was UTF-8.

Once you did this, you can import the file into MySQL, but specify it is UTF-8, otherwise MySQL might try to interpret it as Latin 1 and you will still have the ç characters.

Answer 2

I had a similiar problem with german umlauts. The problem also occurred on a db export with phpmyadmin. My solution was to export the database with the the command line tool mysql and the parameter "--default-character-set="utf8".

Another approach, if you haven't access to a linux shell: Install and use MySQLDumper

Answer 3

A common problem with older WordPress databases and even newer ones is that the database tables get set as latin-1 but the contents are actually encoded as UTF-8. If you try to export as UTF-8 MySQL will attempt to convert the (supposedly) Latin-1 data to UTF-8 resulting in double encoded characters since the data was already UTF-8.

The solution is to export the tables as latin-1. Since MySQL thinks they are already latin-1 it will do a straight export.

1. Change the character set from ‘latin1′ to ‘utf8′.

Since the dumped data was not converted during the export process, it’s actually UTF-8 encoded data.

1. Create your new table as UTF-8 If your CREATE TABLE command is in your SQL dump file, change the character set from ‘latin1′ to ‘utf8′.

2. Import your data normally. Since you’ve got UTF-8 encoded data in your dump file, the declared character set in the dump file is now UTF-8, and the table you’re importing into is UTF-8, everything will go smoothly.