php upgrade to 5.3.x creates utf8 issue wrt mysql communication

[Per Jessen]

I've recently had to upgrade PHP on a long-running server (openSUSE
11.0). Yes, there are reasons why this hasn't been upgraded. I
upgraded PHP to 5.3.0 which at first seemed to work just fine.
However, one of the few, rarely used, web-apps left on this box is now
having an encoding issue wrt mysql data and I simply cannot find the
right setting to tweak.

Everything should be in UTF8 - apache outputs utf8 etc., but when the
data comes from mysql - when fetched with e.g. mysqli_fetch_assoc() -
UTF8 characters are interpreted literally instead of as UTF8.

My php.ini has

default_charset = "UTF-8"
mbstring.internal_encoding = "UTF-8"

The database is in utf8:

mysql> show create database db_example;

db_example | CREATE DATABASE `db_example` /*!40100 DEFAULT CHARACTER SET
utf8 COLLATE utf8_unicode_ci */ |

The apache config has:

AddDefaultCharset UTF-8

I've now moved this web-app to a 2nd apache server, also running php
5.3 - the issue remains the same.

I've tried adding mysqli_set_charset() to the code, no effect.

[Cristian Rodríguez]

Post by Per Jessen
mysql> show create database db_example;
db_example | CREATE DATABASE `db_example` /*!40100 DEFAULT CHARACTER SET
utf8 COLLATE utf8_unicode_ci */ |

What is the mysql *client* character set ? it is set with
mysql(i)_set_charset from the web app.

[Administrator Beckspaced.com]

Post by Per Jessen
I've recently had to upgrade PHP on a long-running server (openSUSE
11.0). Yes, there are reasons why this hasn't been upgraded. I
upgraded PHP to 5.3.0 which at first seemed to work just fine.
However, one of the few, rarely used, web-apps left on this box is now
having an encoding issue wrt mysql data and I simply cannot find the
right setting to tweak.
Everything should be in UTF8 - apache outputs utf8 etc., but when the
data comes from mysql - when fetched with e.g. mysqli_fetch_assoc() -
UTF8 characters are interpreted literally instead of as UTF8.
My php.ini has
default_charset = "UTF-8"
mbstring.internal_encoding = "UTF-8"
mysql> show create database db_example;
db_example | CREATE DATABASE `db_example` /*!40100 DEFAULT CHARACTER SET
utf8 COLLATE utf8_unicode_ci */ |
AddDefaultCharset UTF-8
I've now moved this web-app to a 2nd apache server, also running php
5.3 - the issue remains the same.
I've tried adding mysqli_set_charset() to the code, no effect.

i had something similar a while back. all settings looked just fine but
still problems ...

the main keywords, which helped me fix that issue ->

"get me out of mysql character set hell"

https://www.google.de/search?q=get+me+out+of+mysql+character+set+hell

point was, even all UTF-8 settings were right, the data inside mysql was
still in another character encoding, causing all those problems.

at the end i remember copying the mysql data to a temporary table,
delete original data and then re-import from the temporary table with
the right character encoding ....

the google link above might send you on the right track ...

best of luck & greetings
becki

[Per Jessen]

Post by Administrator Beckspaced.com

Post by Per Jessen
I've recently had to upgrade PHP on a long-running server (openSUSE
11.0). Yes, there are reasons why this hasn't been upgraded. I
upgraded PHP to 5.3.0 which at first seemed to work just fine.
However, one of the few, rarely used, web-apps left on this box is
now having an encoding issue wrt mysql data and I simply cannot find
the right setting to tweak.
Everything should be in UTF8 - apache outputs utf8 etc., but when the
data comes from mysql - when fetched with e.g. mysqli_fetch_assoc() -
UTF8 characters are interpreted literally instead of as UTF8.
My php.ini has
default_charset = "UTF-8"
mbstring.internal_encoding = "UTF-8"
mysql> show create database db_example;
db_example | CREATE DATABASE `db_example` /*!40100 DEFAULT CHARACTER
SET utf8 COLLATE utf8_unicode_ci */ |
AddDefaultCharset UTF-8
I've now moved this web-app to a 2nd apache server, also running php
5.3 - the issue remains the same.
I've tried adding mysqli_set_charset() to the code, no effect.

i had something similar a while back. all settings looked just fine
but still problems ...
the main keywords, which helped me fix that issue ->
"get me out of mysql character set hell"
https://www.google.de/search?q=get+me+out+of+mysql+character+set+hell
point was, even all UTF-8 settings were right, the data inside mysql
was still in another character encoding, causing all those problems.

After a lot of pulling hairs, that is exactly where I ended up yesterday
too. The data in the database was in fact written as ISO-8859-1, not
UTF8. A 10 year old mistake, amazing.