Mysql latin1 client with utf8 server encoding issue

Problem:

I’ve a database with server collation of utf8 / utf8_bin. DB have arabic utf8 text with accent chars (zabar / kasar etc). I want to get text without accent chars.

For e.g. SELECT replace(field1,0x[CODE],”) — where [CODE] is the accent char.

REPLACE() is working fine when i write static text in place of field1. But it does not replace when it run at database field.

Solution:

After reading an article, i come to know there are 2 (client & server) encodings used with mysql. My server encoding (table charset/collation) was utf8, but client’s encoding was latin1 and the time of INSERT.

So data stored in DB is somewhat mixed, due to which REPLACE() was not working as expected.

FIX:

  1. i fetched the data using php,
  2. applied bin2hex() function on data,
  3. set client’s encoding to utf8 (set names utf8) and
  4. updated same record.

This fixed my client’s encoding to utf8 that matches with server encoding, now REPLACE() is removing that accent hex code and working as expected .

Following article really helped …
http://www.oreillynet.com/onlamp/blog/2006/01/turning_mysql_data_in_latin1_t.html

Share your thoughts & feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: