MySQL Bugs: #50868: Problem while inserting chinese text by using mysqldriver.cs

Bug #50868	Problem while inserting chinese text by using mysqldriver.cs
Submitted:	3 Feb 2010 8:34	Modified:	12 Feb 2010 18:32
Reporter:	khwaja moinuddin	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.1.25	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
I want to insert chinese text from windows form(c#) and i want to retrieve it in the grid.Here iam using mysqldriver.cs and iam appending charset-utf8 like this MySQLCon = new MySQLConnection(MySQLConString.AsString+";charset=utf8");

I created a table as login with first_name varch(64) with charset big5 and collation big5_chinese_ci, and last_name(64) with same charset and collation.

But when the data is inserted in table its not dispalying chinese characters. Its displaying as ????.. So any one please help me how to fix this problem...

I also tried with different charset's like big5,ucs2 and gbk...

Thanks...

How to repeat:
when entering chinese text and saving it into database by using mysqldrivercs..

Thank you for the report.

But version 5.0.45 is old and many bugs were fixed since.

Please upgrade to current version 5.0.90, try with it and if problem still exists provide following information:

- are you using Connector/NET?
- what is output of SHOW VARIABLES LIKE 'char%' and SHOW VARIABLES LIKE 'col%'?
- please provide complete test case showing the problem

HI Sveta Smirnova,

Thanks for the acknowledgment,

u asked me the following information

are you using Connector/NET?
- what is output of SHOW VARIABLES LIKE 'char%' and SHOW VARIABLES LIKE
'col%'?
- please provide complete test case showing the problem

Iam using mysqldriver.cs as a connector

show variables like 'char%'

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_set_dir --> C:\Program Files\MySQL\MySQL Server 5.0\share\charsets

SHOW VARIABLES LIKE 'col%'

collation_connection --> utf8_general_ci
collation_server --> utf8_general_ci
collation_database --> utf8_general_ci

 MySQLCon = new MySQLConnection(MySQLConString.AsString + ";charset=UTF8");

i also tried with big5,and gbk charset's.

thanks
khwaja Moinuddin

Thank you for the feedback.

Have you tried current version 5.0.90? If not please try.

Hi  Sveta Smirnova.

Now i tried with 5.1.25-rc-community.

I facing the same problem as chinese char displaying as ????

complete test case showing the problem

Iam using mysqldriver.cs as a connector

show variables like 'char%'

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_set_dir --> C:\Program Files\MySQL\MySQL Server
5.1\share\charsets

SHOW VARIABLES LIKE 'col%'

collation_connection --> utf8_general_ci
collation_server --> utf8_general_ci
collation_database --> utf8_general_ci

 MySQLCon = new MySQLConnection(MySQLConString.AsString +
";charset=UTF8");

i also tried with big5,and gbk charset's.

thanks
khwaja Moinuddin

Thank you for the feedback.

But version 5.1.25 is outdated too. Current 5.1 version is 5.1.43

Also 

>  MySQLCon = new MySQLConnection(MySQLConString.AsString + ";charset=UTF8");

is not complete test case.

I need:

Output of SHOW CREATE TABLE problem_table
Code you use to insert a string
Output of SELECT field, HEX(field) FROM problem_table
Code you used to get this output.

HI Sveta Smirnova

I really appreciate for the response u r giving. Thanks for that

connection string:

 MySQLCon = new MySQLConnection(MySQLConString.AsString +
";charset=UTF8");

i.e.,

Data Source=test;Password=gaian;User ID=root;Location=localhost;Port=3306;Extended Properties="";charset=utf8

output of SHOW CREATE TABLE details:

CREATE TABLE 'details'(
  
  `first_name` varchar(32) DEFAULT NULL,
  `last_name` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC               

code for inserting :

using (DBHandle dbMultiLang = new DBHandle())
                {
                    String strQry = "";
                    strQry = "insert into details (first_name,last_name) values('" + txtfirstname.Text.Trim() + "','" + txtlastname.Text.Trim() +"') ";
                    dbMultiLang.ExecuteNonQuery(strQry);
                }

output of: SELECT first_name, HEX(first_name) FROM details

海峽定義---->  6D775CFD5B9A7FA9

(If i entered manually, like if  insert into detials(first_name,last_name) values('海峽定義','chinese') its working fine  i.e, in sql yog ..)

????---->  003F003F003F003F  (Inserting by using textboxes from  winforms(c#))

code to get output in a datagrid:

try
            {
                using (DBHandle dbMultiLang = new DBHandle())
                {
                    String strQry = "";
                    DbDataReader dbreader;
                    strQry ="Select first_name,last_name from details ";
                    dbreader = dbMultiLang.ExecuteReader(strQry);
                    if (dbreader != null && dbreader.HasRows == true)
                    
                    {   langdatagrid.Rows.Clear();
                        while (dbreader.Read())
                        {
                            langdatagrid.Rows.Add(dbreader["first_name"].ToString(),dbreader["last_name"].ToString());
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

I also have the same problem. If I use SQL insert into mysql admin page, the SQL command as follows : 

CREATE TABLE IF NOT EXISTS `Category` ( `Categorycode` varchar( 50) COLLATE utf8_unicode_ci NOT NULL, `Desc1` varchar( 50) COLLATE utf8_unicode_ci NOT NULL, `Desc2` varchar( 50) COLLATE utf8_unicode_ci NOT NULL, `Desc3` varchar( 50) COLLATE utf8_unicode_ci NOT NULL, `Desc4` varchar( 50) COLLATE utf8_unicode_ci NOT NULL, `ISN` varchar( 20) COLLATE utf8_unicode_ci NOT NULL, `Ordering` varchar( 10) COLLATE utf8_unicode_ci NOT NULL, `StockNotes` varchar( 200) COLLATE utf8_unicode_ci NOT NULL, `Worth` float NOT NULL , `LastLog` varchar( 20) COLLATE utf8_unicode_ci NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO `Category` ( `Categorycode`, `Desc1`, `Desc2`, `Desc3`, `Desc4`, `ISN`, `Ordering`, `StockNotes`, `Worth`, `LastLog`) VALUES ( 'try1cat', 'BAMBOO CAN  竹筒', '-', '-', '-', '-', '-', '-', 0, '-')"

The chinese charaxter  '竹筒' displayed.

But if we start with VB2008, add reference for MYSQL Net Connector 6.2.2 dll and execute the same sql using : 

        Dim i As Integer
        Dim cmd As New MySqlCommand
        cmd.CommandText = sqlstr
        cmd.CommandType = Data.CommandType.Text
        cmd.Connection = New MySqlConnection(connectionString)
        cmd.Connection.Open()
        i = cmd.ExecuteNonQuery()

The display of the chinese character in the admin page appears as ???

Thanks for your re-direct, but there is no solution to the bug yet ?

In any case, I am using VB 2008

Thanks

Yes there is the no solution to the bug yet.

I am finding alternate ways. 

khwaja

Guys, main thing here is this:
海峽定義---->  6D775CFD5B9A7FA9

(If i entered manually, like if  insert into detials(first_name,last_name)
values('海峽定義','chinese') its working fine  i.e, in sql yog ..)

????---->  003F003F003F003F  (Inserting by using textboxes from  winforms(c#))

It is obvious some unwanted conversion has happened... Usually, this is due to your VB environment (boxes, lists, ...) not being set to UTF8! Please check properties of controls that display the text and get back to me.

One more question; which version of c/NET do you actually use?

Cai reported Bug#50933 for same thing.

Hi,

I used the following code to encode and decode the text of different lanugages.

        public static string unicode_iso8859(string src)
        {

            Encoding iso = Encoding.GetEncoding("iso-8859-1");
            Encoding unicode = Encoding.UTF7;
            byte[] unicodeBytes = unicode.GetBytes(src);
            return iso.GetString(unicodeBytes);
         
        }
        public static string iso8859_unicode(string src)
        {
            Encoding iso = Encoding.GetEncoding("iso-8859-1");
            //Encoding iso = Encoding.GetEncoding("GB18030");
            Encoding unicode = Encoding.UTF7;
            byte[] isoBytes = iso.GetBytes(src);
            return unicode.GetString(isoBytes);
        }

with this code i am able to retrieve the data which i am inserting but the problem here is in database the text is saved in some encoded format

ex: 

when iam inserting text like 

 "insert into details (first_name,last_name) values('" + unicode_iso8859(txtfirstname.Trim()) + "', '" + unicode_iso8859(txtlastname.Text.Trim()) + "') ";

txtfirstname.text='请选择列删除';
txtlastname.text='chinese/japanese/arabic';

when i am retrieving this data in grid it working fine .

But in table its saving as +i/eQCWLpUhdSIJZk- (some encoded format)

I am  using visual studio 2005 i.e, c# 2.0.. and the controls not having any property like charset.  

Thanks

Everything's correct

Attachment: Bug50868.jpg (image/jpeg, text), 11.63 KiB.

Khwaja, I took simplest approach:
            MySqlConnection conn = new MySqlConnection("DataSource=xx;Database=xx;UserID=xx;Password=xx;PORT=xx;logging=true;charset=utf8");
            conn.Open();
            MySqlCommand cmdsetup = new MySqlCommand("insert into details(first_name,last_name) values('海峽定義','chinese cNET')", conn);
            cmdsetup.ExecuteNonQuery();
            cmdsetup.Dispose();
            conn.Close();
            conn.Dispose();

Test:
  o Insert Chinese characters into table from command line client, connection is LATIN1
  o Insert Chinese characters into table from c/NET, connection is UTF-8
  o Insert Chinese characters into table from command line client, connection is UTF-8

Server version: 5.1.31-log MySQL Community Server (GPL)
mysql> select first_name, HEX(first_name) from details;
+--------------+--------------------------------------------------+
| first_name   | HEX(first_name)                                  |
+--------------+--------------------------------------------------+
| 海峽定義 | C3A6C2B5C2B7C3A5C2B3C2BDC3A5C2AEC5A1C3A7C2BEC2A9 | 
| ????         | E6B5B7E5B3BDE5AE9AE7BEA9                         | 
| ????         | E6B5B7E5B3BDE5AE9AE7BEA9                         | 
+--------------+--------------------------------------------------+

mysql> set names utf8;

mysql> select first_name, HEX(first_name) from details;
+--------------------------+--------------------------------------------------+
| first_name               | HEX(first_name)                                  |
+--------------------------+--------------------------------------------------+
| æµ·å³½å®šç¾© | C3A6C2B5C2B7C3A5C2B3C2BDC3A5C2AEC5A1C3A7C2BEC2A9 | 
| 海峽定義             | E6B5B7E5B3BDE5AE9AE7BEA9                         | 
| 海峽定義             | E6B5B7E5B3BDE5AE9AE7BEA9                         | 
+--------------------------+--------------------------------------------------+
3 rows in set (0.00 sec)

So, I suspect there is a problem on your side because as you can see, when command line client and c/NET are both set to UTF-8 the values entered in database are identical and VS grid shows correct Chinese characters (as in attached picture)...

just add "charset=utf8" into connection string.

     MySqlConnection conn = new MySqlConnection("DataSource=xx;Database=xx;UserID=xx;Password=xx;PORT=xx;logging=true;charset=utf8");

regards,
Dantha