Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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")...

share|improve this question
    
Please, anyone ? –  Jonathan Mar 22 '11 at 8:09
    
This might not be directly helpful, but I recommend that you post the question over on Database Administrators, which is like this site but specifically for database questions (which this really is). People over there are much more likely to have an answer for this. Good luck! –  anu Mar 22 '11 at 9:06

3 Answers 3

up vote 0 down vote accepted

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.

share|improve this answer
    
I'm using Notepad++ and PSPad, I tried changing the encoding (using convert and all possible combinations), didn't change anything. –  Jonathan Mar 23 '11 at 5:48
    
Thanks Jan - I actually converted manually (search&replace in Notepad++) using the most common codes (ç and so on). Not clean, but it worked. –  Jonathan Apr 26 '11 at 7:23
    
@Jonathan: Good that you found a solution yourself. You should add it as an answer here and mark it as accepted: this way the system knows the question is no longer "unanswered". –  Jan Fabry Apr 26 '11 at 7:25

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.