Character encoding issue between different versions of PHP, Apache and MySQL.

Question

I recently converted an old MySQL database stored as latin1_swedish_ci to utf8_general_ci. I've now got the the HTTP header specifying UTF-8, the HTML tag on the page, and the data in the database is correctly encoded as utf8_general_ci.

It all works fine on my testing server, so I upload the updated HTML files and PHP scripts to the staging area on the live server. Then I replace the old staging database with an export from the test database.

And now, there are unicode entities instead of every apostrophe on the staging site.

Same database, same code, but it doesn't work there.

The difference between the test server and the staging area is that test server is the following:

Test:

• Apache 2.2.15
• PHP 5.3.2
• MySQL 5.1

Staging: (Yes, it's horribly outdated, but it's out of my control)

• Apache 1.3.37
• PHP 5.2.3
• MySQL 4.1.15

Any idea what is causing it not the work on the staging server?

Answer 1

The connection encoding is really important here. Make sure you use the same encoding on both servers. For example by issuing this as the first command when you connect:

SET NAMES 'utf8';

Answer 2

Make sure the following is set on each machine's my.cnf you're running mysql on:

[client]
default-character-set=utf8 

...

[mysqld]
default-character-set=utf8