Foreign Languages Displayed as HTML Entities

Question

So we've just completed a transition from EE1 to EE2 for a client with a very large database of publications. Some of the publications are available in multiple languages, with Chinese, Korean, and Vietnamese being some of the more popular translated languages, behind Spanish.

On the front-end, the languages are displaying fine, but in the Control Panel they are displaying as HTML entities. Now database wise, this is the same. All the languages were always stored as html entities in MySQL, but before, when the record was pulled up in the control panel, it was readable as that languages.

This is a Korean Publication in EE1.

This is the same Korean Publication in EE2

Now if I go back from the Control Panel and re-enter the data as Chinese, Korean, or Vietnamese the data then displays in that language.

Obviously, we don't want them to have to re-enter hundreds or more publications just to view the text in the proper language, but I can't seem to figure out why EE2 isn't converting the html entities back into the specified languages.

The database is set for UTF-8, charset is also UTF-8, language is English. We didn't have any foreign language packs installed, since the translations only apply to the documents, not the control panel. We still have an EE1 version of the database with Control Panel ONLY access and can compare the databases, and with the exception of table name changes, they look the same.

I'd assume this is or at least was a setting somewhere, but not in the normal config.php settings. Does anyone know where or what I can troubleshoot to get this conversion working properly again?

Obviously, the text needs to be readable for the client to edit and update the publications.

Answer 1

I thought I'd provide the response that Ellis Lab gave me for this issue, for others who may need it in the future. Unfortunately, it doesn't solve my issue, since rolling back the database at this point really can't be done. The client gets hundreds of new entries into EE2 every couple of days and its been almost a month since the transition. That said here are the details from Ellis Lab…

The html entities are the result of double-encoded data. As part of the upgrade process, ExpressionEngine converts your database to UTF-8. However, if any data is already set to UTF-8, it will be re-encoded and you will see the bizarre characters.

Essentially, you need to roll back to the pre-upgrade state. Once you are there follow these steps.

Edit out this bit in /system/expressionengine/installer/updates/ud_200.php

Find: foreach ($tables as $table) { $progress = "Converting Database Table {$table}: %s"; $count = $this->EE->db->count_all($table); $offset = 0;

It should be around line 415. Now comment out the code beginning with:

/*
if ($count > 0) //around line 421

... all the way through ...
$offset = $offset + $batch; // around line 465
}
*/ 

Then upload this to your server and perform the upgrade per the instructions.

What this will do is convert your tables to UTF-8 collation but not the data in those tables. This will be safe through future updates too. This simple edit keeps the installer from double-encoding the data.

So now that I know the process or issue is one of double encoding, I'm trying to find a method that will decode content of a query. Considering the size of the clients database (over 45,000 records), the Asian language records make up a very small percentage (around 300 records).

Decoding them one by one has been relatively easy, but is obviously time consuming. A scripted decode solution would be great. I've tried with MySQL alter statements

ALTER TABLE exp_weblog_data MODIFY field_id_1 text character set binary;
ALTER TABLE exp_weblog_data MODIFY field_id_1 text character set utf8;

as well as testing against some conversion statements, such as

SELECT field_id_1 
CONVERT(CAST(field_id_1 AS BINARY) USING utf8) 
FROM exp_channel_data 
WHERE channel_id="6";

I tried the ALTER on the old database table, to see if it would work, since the data seems to be encode in that one as well (though displays fine in the control panel). I didn't want to deal with any unexpected repercussions of ALTERing the live database.

I did find a few great resources on MySQL conversion issues, which may help others in the future:

Nic Jansma wrote up an awesome article on various conversion issues, how to test, and how to convert, see: http://nicj.net/mysql-converting-an-incorrect-latin1-column-to-utf8/

Mattias Gunneras got me started on the testing of ALTER table commands, which may work for many, though didn't for me, see: http://www.bothernomore.com/2008/12/16/character-encoding-hell/

The GeekGuide provided some more insight into how to properly use CONVERT, CAST, and BINARY, see: http://www.geeksengine.com/database/single-row-functions/conversion-functions.php

Ultimately, it maybe quicker for me to convert the records by hand, just copying and pasting from the old control panel to the new control panel. But since I expect this maybe an issue again in the future, I'm still working on finding a scripted process for dealing with the issue.

My goal is to figure out how to run a UTF8 Decode (like: http://www.utf8converter.com ) on select records in a database, store in a temporary table, then UPDATE the database with the converted data.

Updated. 12/17/2012. Ellis Lab wrote a quick extension, 'EllisLab High ASCII Decode' that converts for the control panel, making Asian Languages fully readable and editable. Apparently a leftover setting from EE 1 did an 'auto_convert_high_ascii' and that was the issue. So this is now totally resolved.

Answer 2

Have you tried changing the custom field "Default Text Formatting" to 'None' in the custom field settings?