Chinese Characters Problem

[Elrinth]

I installed east-asian languages and complicated writing in winxp. (reboot)

I go to http://news.sina.com.cn/ and copy some random chinese text and then I try to add it to my table cms_content.

The table cms_content has advanced property charset set to utf8 in sqlyog. the database is set to utf8.

Here's the line I'm trying.

INSERT INTO cms_content (co_key,co_content,co_pageid,co_language,co_modified,co_created) VALUES ('_', '深交所招聘博士后', '0', 'sv', '2009-01-01', '2009-01-01');

sqlyog says:

(1 row(s) affected, 1 warning(s))

Execution Time : 00:00:00:000

Transfer Time : 00:00:00:032

Total Time : 00:00:00:032

Warning Code : 1265

Data truncated for column 'co_content' at row 1

When I look in the table the chinese chars appear as ? (questionmarks)

What can be wrong?

I'm using latest version of sqlyog (8.2)

I'm using mysql server 5.0.24a-community-max-nt

I also tried the same on another mysql server 5.0.64-log

with same results but different message from sqlyog:

(1 row(s) affected, 1 warning(s))

Execution Time : 00:00:00:016

Transfer Time : 00:00:00:016

Total Time : 00:00:00:032

Warning Code : 1366

Incorrect string value: 'xE6xB7xB1xE4xBAxA4…' for column 'co_content' at row 1

Also in my ASP.NET where it's going to be used, whenever I add a chinese char I get Data is too long for column. Even tho the column is longtext. Perhaps I need to encode it somehow before I insert it into the table? HtmlEncode maybe isn't enough?

SHOW VARIABLES LIKE 'char%'; gives me:

character_set_client;utf8

character_set_connection;utf8

character_set_database;utf8

character_set_filesystem;binary

character_set_results;utf8

character_set_server;utf8

character_set_system;utf8

character_sets_dir;C:ProgramMySQLMySQL Server 5.0sharecharsets

Please help…

Is the problem my mysql version or am i doing wrong when I'm trying to insert?

[Mahesh]

We have tried with v 8.2 with chinese character works fine at our end. ( see the screen shot )

Are you sure that column level charset is Utf8 ?

[peterlaursen]

Please also paste SHOW CREATE TABLE for this table.

[Mahesh]

Oops! forgot to attach a screenshot

[peterlaursen]

That webpage does not seem to be utf8.  Use the option to show code in browser and you will see the meta-tag

refer to: http://en.wikipedia.org/wiki/GB_2312 for this charset.

This charset is not supported by MySQL currently.  It has been planned for years but there seems to be not much progress. If gb2312 uses  a byte sequence that is invalid in UTF8 truncation may occur.  I am not sure it is the problem though. 

[peterlaursen]

.. and actually what gets copied to clipboard may also depend on what browser you copy from. 

[peterlaursen]

sorry .. the charset is there in MySQL:

http://dev.mysql.com/doc/refman/5.1/en/charset-asian-sets.html

.. but when I copy from my browser here (Opera) it seems that Opera returns UTF8 to the clipboard. I did not try other browsers. You should try both uft8 and gb2312 for the column, I think.

[Elrinth]

'Mahesh' wrote on '15:

We have tried with v 8.2 with chinese character works fine at our end. ( see the screen shot )

Are you sure that column level charset is Utf8 ?

could you please tell me what version of mysql you are using?

[peterlaursen]

SQLyog works with any charset supported by server. Please read the FAQ: http://webyog.com/faq/content/34/148/en/do-i-need-to-set-names-to-display-characters-of-my-language.html

But the editor will not recognize anything but ANSI, UFT8 and 'Windows Unicode'. Do strings you copy from that website display OK in the SQLyog editor? Did you try different browsers?

If we shall help you have have to reply to our questions as well!

[Elrinth]

'peterlaursen' wrote on '15:

SQLyog works with any charset supported by server. Please read the FAQ: http://webyog.com/faq/content/34/148/en/do-i-need-to-set-names-to-display-characters-of-my-language.html

But the editor will not recognize anything but ANSI, UFT8 and 'Windows Unicode'. Do strings you copy from that website display OK in the SQLyog editor? Did you try different browsers?

If we shall help you have have to reply to our questions as well!

Here's what it looks like:

and like I said, I'd like to know which version of mysql you guys are using.. Because the mysql server might be the problem aswell.

[peterlaursen]

I cannot tell what MySQL server Mahesh was using.  He left office for today.  But any MySQL server from 4.1.x with any SQLyog from 6.0 will handle this. We will normally work with most recent stable server or the ones before that.   

Our specific server recommendations are http://www.webyog.com/faq/content/1/178/en/sqlyog-is-a-client-for-the-mysql-server-_-but-what-server-versions-are-supported.html

(only I will soon raise 5.0 recommendation to 5.0.67 or higher – but that has another reason)

Can't you please provide the table in an *importable form*  – ie. as a CREATE TABLE .. SQL-statement?  You can copy it from info/messages tab. Then I can try from here.

[peterlaursen]

no problem here with this example:

CREATE TABLE `chinese` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`data` TEXT,

PRIMARY KEY (`id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `testchinese`.`chinese`(`id`,`data`,`browser`) VALUES ( NULL,'新司法解释规定恶意透支信用卡最高可判无期','opera');

INSERT INTO `testchinese`.`chinese`(`id`,`data`,`browser`) VALUES ( NULL,'中纪委集训全国纪检监察干部 考核成绩记入档案','firefox');

INSERT INTO `testchinese`.`chinese`(`id`,`data`,`browser`) VALUES ( NULL,'扫黄打非办公布八起手机涉黄案 7家网站积极清理 ','ie8');

— It works equally well no matter if I use GRID or editor:

SELECT * FROM chinese; 

/*returns

Code:

id data browser
—— —————————————————————— ——-
1 新司法解释规定恶意透支信用卡最高可判无期 opera
2 中纪委集训全国纪检监察干部 考核成绩记入档案 firefox
3 扫黄打非办公布八起手机涉黄案 7家网站积极清理 ie8
4 新司法解释规定恶意透支信用卡最高可判无期 opera
5 中纪委集训全国纪检监察干部 考核成绩记入档案 firefox
 6 扫黄打非办公布八起手机涉黄案 7家网站积极清理 ie8

.. the alignment issue with unicode data is a known problem

*/

Are you perfectly sure that it is not another column causing problems?

[peterlaursen]

This was with MySQl 5.1.41 .. but not important.  However I do not think you told the exact SQLyog version?  Please try with 8.2 i you did not already.

[Elrinth]

Yes I'm using 8.2. When I created the table like you said it worked.

Is there some way to set default charset for a table after it's been created?

I did:

ALTER TABLE cms_content CONVERT TO CHARACTER SET utf8;

and after it worked to insert chinese chars into it.

Why doesn't sqlyog have an option to do this anywhere? 🙂

[peterlaursen]

It has!  table menu/context menu .. alter table .. advanced properties.

[Elrinth]

'peterlaursen' wrote on '18:

It has!  table menu/context menu .. alter table .. advanced properties.

If you look in my previous post, there is a screenshot where I set this you are speaking of. That does NOT solve the problem.

I had to do a query:

ALTER TABLE cms_content CONVERT TO CHARACTER SET utf8;

to solve the problem.

If it's meant to do the same as my query, then there's a bug in webyog 8.2.

[navyashree.r]

Quote:

I had to do a query:

ALTER TABLE cms_content CONVERT TO CHARACTER SET utf8;

to solve the problem.

If it's meant to do the same as my query, then there's a bug in webyog 8.2.

Hey Elrinth,

This is not a bug in our SQLyog.

When we change the table charset from “Table menu/context menu … Alter Table … Advanced Properties”, we are executing query:

Quote:

ALTER TABLE `chinese`.`cms_content` ENGINE=MYISAM AUTO_INCREMENT=5 COMMENT='' ROW_FORMAT=DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ;

Note: This just changes the table Character Set but does not change the Column charset.

Its our limitation as we are not supporting this query “ALTER TABLE … CONVERT TO CHARACTER SET …” in our GUI.

Thank you for this valid point. We will discuss regarding this.

BTW,

You can change the column “co_content” charset to 'UTF8' by selecting the column in Alter table. This will solve the 'Chinese Characters Problem'. See screenshot “image.PNG”.

Regards,

Navya

[navyashree.r]

Sorry, image is here.

Regards,

Navya

[Elrinth]

'navyashree.r' wrote on '12:

Sorry, image is here.

Regards,

Navya

Ofcourse how stupid! I had looked everywhere for that. I thought that feature was actually removed from this version. (I had seen it by default on older versions)

Anyways. Remember to untick the default box: “Hide Language Options”

Thank you very much!

[Author]

Posts