Character Encoding Unicode/utf-8 Problem

[Bill Nguyen]

I connected to mySQL database via SQLyog Enterprise 6.0

I cannot read the text compiled by Unicode except those in English because they are encoded somehow (see below).

Is there a way to decode them to view in SQLYOG?

Thanks

Billn

Chiếc giày kim cương của em

Ðừng viết những bài thÆ¡ về chuyện Ä‘ang diá»…n ra.

Bọn giết và bọn lừa, cÆ¡n mÆ¡ và cÆ¡n muốn,

lúc nào chúng cÅ©ng Ä‘ang diá»…n ra.

Hãy bỏ chúng bên ngoài bài thÆ¡.

Ðừng tả căn nhà mùa hạ mắt buồn của em

hay căn nhà mùa Ä‘ông mắt tròn.

Ðừng viết về ná»—i không nhà

hay chốn thân quen xa nhà.

Ðừng viết về chiến tranh,

dù em bênh hay chống,

vẫn là chiến tranh chết tiệt.

Ðừng nói về ngôn ngữ,

đừng nói về mất mát.

Ðừng đề cập đến sá»± thật hay cái đẹp

hay nắm xÆ°Æ¡ng tàn ông ná»™i.

Không ai muốn biết

làm sao cha/anh/người yêu của em

trừ Ä‘i chính mình. Dao cạo, dây thừng hay súng,

có gì khác Ä‘âu?

Ðừng thì thầm Ä‘iều gì về tuyết

trên quảng trường Contrescarpe,

Ä‘iều gì về bÆ°á»›m Ä‘êm, những vòng cung chấp chá»›i,

hay những ngọn tháp – cách nào ta trông chúng đổ.

Ðừng viết gì cả.

––––––––––––––

Nguồn: Michael Palmer, Company of Moths (New York: New Directions, 2005)

Your Diamond Shoe

Don’t write poems about what’s going on.

Murderers and liars, dreams and desires,

they’re always going on.

Leave them outside the poem.

Don’t describe your sad-eyed summer home

or wide-eyed winter home.

Don’t write about being homeles

or your home-away-from-home.

Don’t write about war,

whether you’re against or for,

it’s the same fucking war.

Don’t talk about language,

don’t talk about loss.

Don’t mention truth or beauty

or your grandpa’s bones.

No one wants to know

how your father/brother/lover

deducted himself. Razor, rope or gun,

what’s the difference?

Whisper nothing of the snow

on the Contrescarpe,

nothing of moths, the fluttering arcs,

or the towers—how we watched them fall.

Don’t write at all.

[peterlaursen]

Please tell:

1) It it Vietnamese?

2) MySQL version?

3) If you ever tried version 5.x could you read it correctly with this one?

4) What program would you normally use to view the data?

5) Please connect to the database and execute “SHOW VARIABLES LIKE '%character%';” and paste the result here

6) Please create a small table with such characters, export(dump) it using utf8 encoding (the SQLyog 'export' does), zip and attach here.

“because they are encoded somehow”.  “SHOW VARIABLES LIKE '%character%';” will tell how it is encoded in the database.

[Bill Nguyen]

1) It it Vietnamese?

Yes, it's Vietnamese

2) MySQL version?

Server version: 5.0.27-standard-log

3) If you ever tried version 5.x could you read it correctly with this one?

I got same problem with 5.x

4) What program would you normally use to view the data?

SQLyog and phpMyAdmin

5) Please connect to the database and execute “SHOW VARIABLES LIKE '%character%';” and paste the result here

character set client utf8

(Global value) latin1

character set connection utf8

(Global value) latin1

character set database latin1

character set filesystem binary

character set results utf8

(Global value) latin1

character set server latin1

character set system utf8

character sets dir /usr/share/mysql/charsets/

collation connection utf8_unicode_ci

(Global value) latin1_swedish_ci

collation database latin1_swedish_ci

collation server latin1_swedish_ci

6) Please create a small table with such characters, export(dump) it using utf8 encoding (the SQLyog 'export' does), zip and attach here.

jos_content_dump.zip

Thanks

Bill

[peterlaursen]

please try open the dump in Notepad yourself.

all strings are stored in BLOB fields.  BLOB is a binary filed type, for strings you should use (var)char or text types.

Also this: ” ..values('4','Lấp lánh há»â€œn thÆ¡ Viá»â€¡t trên sân ga Tokyo chiá»ï¿½u cuá»â€˜i nÄÆ’m','','ký —”  clearly tells that data are NOT stored as Vietnamese in the database.  Looks like it is stored with som web-encoding.  I don't know exactly what it is, because I do not know Vietnamese, but this is NOT unicode!

There may be thousand ways to implement 'client side encoding'.  We do not support this.  SQLyog displays data as they are stored in the database.

[DonQuichote]

peterlaursen wrote on Jun 8 2007, 09:48 AM:

There may be thousand ways to implement 'client side encoding'. We do not support this. SQLyog displays data as they are stored in the database.

How did the texts get in the database? If it is from an self-written application (a PHP page, for example), make sure it sends a “SET NAMES utf8” (or whatever other encoding you work with) command directly after connecting. If you do not do this, MySQL defaults to latin1, even if you set a different default in the config file of the MySQL server.

Hope this helps.

[peterlaursen]

@don

he uses BLOB fields for storing of strings.  SET NAMES has no effect on binary data (like ((var)binary and BLO:cool:.

He is storing some web based encoding as binary data, I believe!  His has a client program of some kind that does the encoding/decoding.

This is not MySQL unicode and we do not support it – whatever it is!

[Bill Nguyen]

peterlaursen wrote on Jun 8 2007, 11:17 AM:

@don

He is storing some web based encoding as binary data, I believe!  His has a client program of some kind that does the encoding/decoding.

Peter;

The “binary” part is probably true for the article body. However, for article titles such as Nhà ăn lúc chiều tối và má»™t hồ bÆ¡i trong mÆ°a, are they also in binary?

You're correct in “web-based encoding” since we use an HTML editor to upload and maintain articles.

If I can send text directly (via sql) using SQLyog, what charset do I need to use?

Thanks

Bill

[peterlaursen]

OK .. I missed that the 'title' filed was a varchar.  As long as you only save single-byte characters that does not make much difference.

Try to create a table where every column (or the table itself) is UTF8. Ensure tht there are varchar or text columsn(s) in it.  From SQLyog simply enter Vietnamese characters from a Vietnamese keyboard.  Now try dump that table and you will see the Vietnamese characters directly displayed in the dump file (in this situation also the file itself will be encoded with utf8).

It may be problematic to change!  Probably quite a a lot a changes are required in the web front-end if data stored as Unicode in MySQL shall be displayed correctly as it seems that this web-frontend uses quite another concept!  But give it a try (and ensure that also uft8 encoding is specified in META tags for the web pages)

[Author]

Posts