MySQL Bugs: #43484: C#: I can't add hebrew strings into my DB using the MySQL.Data.MySqlClient

Bug #43484	C#: I can't add hebrew strings into my DB using the MySQL.Data.MySqlClient
Submitted:	8 Mar 2009 14:40	Modified:	23 Mar 2009 12:28
Reporter:	Nir Schwartz	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
It is not simple as it sounds.
My DB support hebrew, I can add hebrew strings using the My SQL query browser witouh any problem

The column charset is utf8
The column collage is utf8_general_ci

When i do it through the MySQL.Data.MySqlClient.MySqlCommand it doesnt work. No exception is thrown but it adds question marks to the DB instead of hebrew letters

thats the code i am using
MySqlCommand command = m_Connection.CreateCommand();
command.CommandText = "Insert into PLAYERS (USER_NAME, PASSWORD) VALUES('ניר','שוורץ')";
command.ExecuteNonQuery(); // doesnt work! it adds ??? ????? instead of ניר שוורץ

if i use the exact same sql statment through My SQL query browser, everything is perfect. only the .Net MySQL.Data.MySqlClient object is problematic

thanks in advance

Nir.

How to repeat:
This doesnt work:

MySqlCommand command = m_Connection.CreateCommand();
command.CommandText = "Insert into PLAYERS (USER_NAME, PASSWORD) VALUES('ניר','שוורץ')";
command.ExecuteNonQuery(); // doesnt work! it adds ??? ????? instead of ניר שוורץ

the same line will be ok thourgh My SQL query browser
Insert into PLAYERS (USER_NAME, PASSWORD) VALUES('ניר','שוורץ')

Hi Nir and thanks for your report.

Although you have *not* presented all the necessary info needed to repeat the problem (small but *complete* test case, output of show variables like "%char%", MySQL server & c/NET version ...) I rule this as not a bug and here's why.

> if i use the exact same sql statment through My SQL query browser, everything is perfect. only the .Net MySQL.Data.MySqlClient object is problematic

This is the usual case as QB is not governed through connection string as c/NET is. There is something wrong in your connection string in C# code or you change session encoding later via direct queries.

> it adds ??? ????? instead of ניר שוורץ

Of course. What is happening can be any or all of the following:
  o you are saving *single-byte* Hebrew stream to multi-byte UTF8 column reading again *multi-byte* UTF8 stream into single-byte Hebrew text box, web page...
  o your text box, web page ... is not set up to show Hebrew characters.
  o you are using functions (like CONCAT, (EN)(DE)CRYPT ...) which return result with BINARY flag set and do not use option to treat function results as character data.

So utf8 bytes are seen as Hebrew chars and encoded into utf8 yet again. This is the usual corruption. So eventually you see something like A?A?A?A?A (if first byte is representable in Hebrew). This is all probably due to setting session encoding with "SET NAMES ..." command which should be clear from your general query log on server.

Last time I've tested this was for c/NET 1.0.8 long ago and it was working as expected.

So please set up your environment and code so that no double conversion happens. When field encoding (UTF8 in your example) and session encoding (I guess you issued "SET NAMES hebrew" or similar) mismatch, conversion happens and you have garbage. All of connectors (and QB) by default issue "SET character_set_results=NULL" to avoid just this.

First of all - thank you for your quick reply

o you are saving *single-byte* Hebrew stream to multi-byte UTF8
column reading again *multi-byte* UTF8 stream into single-byte Hebrew
text box, web page... - Well i am using the .Net string object, which save every charcter as 2 byte lentgh, I beleive that the problem is laying here.

 o your text box, web page ... is not set up to show Hebrew
characters. The problem is not in the displaying controls, the text is not saved in the DB correctly

 o you are using functions (like CONCAT, (EN)(DE)CRYPT ...) which
return result with BINARY flag set and do not use option to treat
function results as character data. - no, i dont

About the connection string, this is what i use:
string MyConString = "SERVER=" + ServerName +";" +
    "DATABASE=" + DBName + ";" +
    "UID=" + UserName + ";" +
    "PASSWORD=" + Password + ";";

Maybe i am missing a point

Anyway, i found a way to get over this, a bit patchy but it works
I converted the problematic fields to BLOB fields and every time i read\write i am Serialzing/Deserializing the text
Patchy, but works

Nir, of course it will work with BLOB's but that does not mean your environment is set up wrongly for what you're trying to do.

sorry, hit [ENTER] too fast...

but without complete test case and output of show variables I can not say much more... Probably your data on server is corrupted and things will work, as you expect it to, only from BLOB's now.

Always when using utf8 characters in MySql using .Net Connector intialaize your connection string as 

server=xxx;user id=xxx; password=xxx; database=xxx; charset=utf8;

I hope it will resolve your problems

That fixed the problem

thanks a lot

I would like to thank you for the response on adding the encoding to the connection string!
BTW, it is a configuration issue on a computer, because to me this problem took place only on my production machine.
I added the charset=utf8; to the connection string and it did the trick.