It’s like a recurring nightmare for me…

I’m moving servers, and the database engines are slightly different:

Old setup (info from PHPmyadmin)

MySQL 4.1.12
MySQL charset:  UTF-8 Unicode (utf8)
MySQL connection collation: utf8_general_ci
Table collation: latin1_swedish_ci

New setup (info from PHPmyadmin)

MySQL 5.0.32
MySQL charset: UTF-8 Unicode (utf8)
MySQL connection collation: utf8_general_ci
Table collation: latin1_swedish_ci

The story:

After encountering database encoding issues when dumping the DB, I used the database dump utility in the CP. The database dumped fine. I imported it on the new server, via the command line, with—default_character_encoding set to UTF-8, and it imported correctly. I can view the DB fine in PHPmyadmin, all the character encoding is correct. But when viewed on the front end, the High Ascii characters are corrupted.

So my guess is that the actual database connection between the CMS and the database is at fault. The difference between the MySQL connection collation and the table collation is worrying, but I don’t understand why it works on the old server.

Please help!

just to add that converting table collation to utf8_general_ci did not help.

Richard, I’m by no means an expert, but are you certain you’re using charset=“utf-8” or the variable {charset} on the front end of the site?

yep

I’d like to know the exact connection string that Expression Engine uses to connect to the database; what character set and collation it uses to connect to the database. This may help with my troubleshooting.

Richard, you can find that information in the file called db.mysql.php found in the /system/db folder.

EE reads the contents of config.php to grab the login info for MySQL. There is no assumed character set and collation as far as I know.

EE doesn’t make the MySQL database. That’s done beforehand.

The following thread may help: http://expressionengine.com/forums/viewthread/81472/

Thank you, Keith. Richard, are you still facing this issue?

Hi

Finally got to fixing this. Thanks Keith, that was the answer. For anyone else who may have experienced the same problem, this is what worked for me:

1. Make a database dump. Open the SQL file in a text editor and search and replace “DEFAULT CHARSET=latin1” with “DEFAULT CHARSET=utf8”.

2. Import DB via the command line (SSH). I find this the most effective, although you could probably do it via PHPmyadmin.

The default character set parameter probably isn’t required, but I did it anyway.

3. Open system/db/db.mysql.php and find function db_connect (around line 173). After

ADD:

Read this for more background: http://expressionengine.com/forums/viewthread/81472/
Remember to backup!

The MySQL documentation suggests that “SET NAMES ‘utf8’” alone would be sufficient,
and also suggests that it is necessary “to execute SET NAMES every time you start up
only if you want to use a character set that is different from the default”, and suggests that—
if you want to use a character set that is different from the default—it is preferable to
“add the—default-character-set option setting to your mysql statement line, or in your
option file (see docs)” rather than execute SET NAMES as a separate query.

Thanks for laying out what you did, Richard.  And thanks to KeithW for elaborating.  (I suspect I’ll be using this myself.)  Glad all is now squared away w/the charset issues!