Thread

This thread follows an separate issue described here.

After moving my database, which contains custom field labels, member custom field labels, member custom field data, and entries in multi-byte characters (Japanese), from local to remote server, some sections of the Control Panel, as well as on the site, display garbled text.

After having a look at the local database using phpMyAdmin, I could see that characters were saved in garbled text looking like this: è¿½åŠ æƒ…å ± (just an example). However, everything is shown correctly on my local Control Panel and site.

After moving the database to a remote server, the text appears as above on the site as well as the EE CP, in particular for member custom fields, its data, category custom field labels and template custom field labels. I have no problem with the Japanese language pack, however. Rewriting the labels with Japanese, followed by saving, yields “?????”.

Locally, tables are in latin1_swedish_ci, and the DB collation is utf8_general_ci. Same for remote server DB.

What can be done? I usually have no problems with the defaults database settings.
EE 1.6.8
MySQL 5.0.77
MySQL charset: utf8

Thanks!

Do you’ve got a link to the site, showing such a garbled section? I’d like to see this for myself.

Yes, I do (unfinished, btw): Here.
I can show screenshots of how things look in the CP, too.

Here’s a screenshot from the Control Panel, custom field settings.

Nicolas, thanks for posting this. Ingmar is our resident character set guru, he’ll be on shift later today. Thanks in advance for your patience.

Nicholas,

Were any of your members/entries imported, or were all of them entered in ExpressionEngine?

All manually entered in ExpressionEngine. BTW, if anyone knows of any good ways to automate the transfer of content from badly-coded static pages into EE, that would help me a lot! 😉

Nicolas, do you have access to another MySQL db to test against? As long as the default collation is set to utf-8… what I’m trying to see is if a clean install fixes the cp problem. We’ll worry about getting the entries and such back in if this works..

Unfortunately, I can’t create other or copy databases on that particular server. What I can try, though, is installing a clean version of EE (I still have my local DB backups, so that should be OK), then writing in some Japanese characters in custom fields and other problematic places, and see if they appear correctly. I’ll try this and get back to you.

Sounds good. We’ll be here!

There is a charset issue here. I am not too familiar with Japanese encodings I admit, but it appears that your content was originally encoded as “Shift_JIS” but is now displayed as UTF-8. In fact, forcing that encoding manually on your website makes it appear correctly. As I see it, you haw two choices here: convert your db from Shift_JIS to UTF-8, or use the former encoding in your templates (and Control Panel) as well. Makes sense?

Thank you for the feedback. I tried Shift_JIS in my browser, but text still appeared garbled.
I have just tried installing a fresh version of EE, and multibyte characters appear correctly! A closer look at the database shows that tables and database are in utf8_general_ci. Characters are saved as multibyte characters in the database, too.

I get the feeling I will have to convert my local database to UTF8 before uploading to the remote site, is that right? I heard that this wasn’t recommended: are there any drawbacks? Any problems when upgrading EE or with using some modules/extensions/plugins?
Thanks.

Nicolas,

I get the feeling I will have to convert my local database to UTF8 before uploading to the remote site, is that right? I heard that this wasn’t recommended: are there any drawbacks?

Make a backup of everything. Upgrade remote site to most recent build to match your local install and give it a shot. You can roll back your db fairly quickly if things don’t look right

Any problems when upgrading EE or with using some modules/extensions/plugins?

Your remote site is 1.6.8 too right? There shouldn’t be a problem

This is not working. I backed up and copied my local database just in case. Then, I ran the script I found here in the forums, which was the first step from this EE wiki article, and all tables look converted to utf8_general_ci.

Now, I have the same problem accessing the CP as on the remote server. I get an unstyled login page, and my username/password result in the “The username you submitted was not found in the database” message again. My username is still there and visible in exp_members.

What next?

More information: Table collation is utf8_general_ci, but columns are still latin1_swedish_ci.

I then tried the second option from the wiki article (link, step 2), but the command

$ iconv -f iso-8859-1 -t utf-8 mydb_060929 > mydb.sql

iconv: mydb_0609029: No such file or directory

. So I tried with absolute paths, and got

iconv: /Applications/XAMPP/xamppfiles/var/mysql/mydb_060929: I/O error