10

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.

EE1 Control Panel View- Korean Pub

This is the same Korean Publication in EE2

EE2 Control Panel View- Korean Pub

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.

Chinese Language Re-Entered in Mandarin

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.

  • I've run into something similar, but I don't think it is the same issue. You hadn't applied any hacks to core in EE1 to get it to write UTF8 to the database properlyy had you? If you did I can probably go back and find the answer for you. – UltraBob Dec 13 '12 at 22:18
  • I had the same problem writing Korean on my blog when using EE1. I don't do much Korean writing anymore, but with EE2 haven't noticed this problem. Is upgrading an option? – CreateSean Dec 14 '12 at 0:05
  • I'm already in EE2 2.5.3. This happened after the upgrade. Before the upgrade, Korean, Chinese, Vietnamese, etc. displayed in the control panel and the website. Now, the website displays the proper language, but the control panel displays image #2. – nonprofit_tech Dec 14 '12 at 0:29
3

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.

| improve this answer | |
  • 1
    Glad you found the answer. It sounds like you had exactly the same issue I had. Fortunately I knew to look for it because I knew I had EE1 set to write to the db in utf-8. For anyone looking to update from ee1 to ee2 that have also fixed the db weirdness in EE1, you need to comment out the bit mentioned in this answer in upd_200.php to stop the updater from converting your already utf-8 data into utf-8 again. – UltraBob Dec 18 '12 at 7:35
1

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

| improve this answer | |
  • 1
    Hi Jeff, please consider adding a screenshot or some details on how to actually change the "Default Text Formatting." As is your answer is lacking information which could be helpful for new users, thanks :) – Natetronn Dec 13 '12 at 19:47
  • Default Text Formatting is already set to None for all fields, since the publications require a lot of custom HTML to display properly. Different table types, table of contents, callouts, etc. I did go back and make sure they were set to 'none' and triggered the update all records to 'none' just in case, but nadda. – nonprofit_tech Dec 13 '12 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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