Thread

Hi there,

I’ve seen loads of posts about this kind of thing and have tried some of the things that I’ve seen mentioned (the ones that I could understand at least!) but so far have had no joy working it out.

I have EE up and running on a server where the DB tables are apparently utf8_unicode_ci.

I have exported out the data using phpMyAdmin as an SQL file, then reimported it into the new servers mysql using phpMyAdmin (again the tables are apparently set to utf8_unicode_ci. 

The problem is, on the new site some characters (apostrophes, £, dashes, etc.) are showing as black diamonds with question marks in them.  The problem goes away (i.e. they displays fine in the browser) if I change the default character set in Admin > System Preferences > General preferences to ISO-8859-1

You can see an example on the OLD server at http://www.bmrb.co.uk/index.php/news/article/consumer-confidence-tracker-december-2008/

You can see the same example on the NEW server at http://bmrblaa1.miniserver.com/~bmrb/index.php/news/article/consumer-confidence-tracker-december-2008/

So, I’m guessing that even though my DBs in both places are apparently in UTF8, I have either:

a) screwed up the export/import somewhere to use the ISO-8859-1 charset instead
b) one of the databases isn’t actually UTF8
c) or something else is affecting the data between mysql and EE and turning it into ISO-8859-1

Any ideas?

Thanks in advance!

—-

Expression Engine 1.6.6 Build:  20081114 (I’m going to upgrade 1.6.7 when this problem is sorted)

Note: I realise that I probably could just forget it and leave it as ISO-8859-1, but I’m slightly reluctant to given that I thought it was already in UTF8 and I’m not sure what problems I may come up against in future if there is any charset mismatching going on.

Hmmm… if I go to: Admin ›  Utilities ›  SQL Manager ›  System Variables there is latin1 all over the place!  Is this the problem?

character_set_client   latin1
character_set_connection   latin1
character_set_database   utf8
character_set_filesystem   binary
character_set_results   latin1
character_set_server   latin1
character_set_system   utf8
character_sets_dir   /usr/share/mysql/charsets/
collation_connection   latin1_swedish_ci
collation_database   utf8_unicode_ci
collation_server   latin1_swedish_ci

Paul, it looks like something might have, indeed, gone awry with the export. Your data appears to be in iso-8859-1 now, so you would have to add that encoding to your template. It’s perfectly safe to do so, actually. If you want to keep your data in utf-8 format, I would try to re-export it, paying particular attention to the encoding. Many applications, among them phpMyAdmin if I recall correctly, still assume a default of iso-8859-1.

You might also consider using some other program. (My personal favorite happens to be mysqldumper.)

Thanks Ingmar.  I’ll give mysqldumper a look before probably giving up and leaving it as ISO-8859-1.

One question in the meantime though, you don’t think that it could be anything to do with the MySQL system variables that I was finding in EE @ Admin ›  Utilities ›  SQL Manager ›  System Variables (that I mentioned in my second post)?

Thanks,
Paul.

It shouldn’t be a problem from what I can see (my settings are the same on a utf-8 install working normally).  If the tables and fields are showing utf8, my money is on the export.

What Robin says. No, the variables should be fine, I use the same values (and still “utf-8” in my websites). EE and MySQL should negotiate the correct encoding between them regardless.

Hi again Ingmar/Robin,

Thanks for your help with this.  I did try re-exporting the data using MySQLDumper using UTF8 and re-importing into the new server, but to no avail.  As I mentioned in my initial post, when I switch the character encoding to ISO-8859-1 in EE and the front-end templates everything displays correctly.  So, unless you see any problems leaving it as that going forward (i.e. when EE 2.0 arrives) I think I’m just going to leave it as it is now.

Thanks,
Paul.

No, I don’t see any problems there. Personally I tend to use Unicode aka utf-8 these days, which basically allows me to use all available characters (including a combination of some which would be in different charsets, otherwise) but there is nothing wrong with using Latin 1 (iso-8859-1), particularly if you run a mono-lingual (read: English) site, and only use a few additional “fancy” characters like quotes, ellipsis and dashes.

Thanks Ingmar.  I think that’s me sorted then (for the time being at least 😉 ) so please go ahead and close this topic.

Thanks,
Paul.

I can do that 😊 .

Glad Ingmar squared you away.  Please don’t hesitate to start a new thread if you need any further assistance.