MySQL Bugs: #53350: MySQL & VB.NET (Visual Studio 2008) Hebrew Problem

Bug #53350	MySQL & VB.NET (Visual Studio 2008) Hebrew Problem
Submitted:	2 May 2010 5:15	Modified:	15 Oct 2011 13:07
Reporter:	Doron Yaary	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S1 (Critical)
Version:	5.1	OS:	Linux (Ubuntu Server 9.0)
Assigned to:		CPU Architecture:	Any
Tags:	hebrew

Description:
Server Information: Ubuntu Server 9.10 & MySQL Community Server 5.1.46
Client Information: Connector.NET (v6) on Visual Studio 2008.
-------------------------------------------------------------------------
Problem Summary:
I can not see my Hebrew values returned to me. I have a database which was created  with the character set of Hebrew and a collation of hebrew_general_ci. When ever I insert data, and pull it back (With a simple SELECT command) I always get question marks returned to me (Eg. I get ???? instead of שלום). I even checked to see if I have the Hebrew.xml in my /usr ... directory and it exists there. My server OS (Linux) has hebrew chars in command line. When ever I insert data using a GUI (from my client) and pull it back I get the right values !!

What am I doing wrong ? 

Please help me because I`m trying to migrate from MS-SQL and frankly, MS-SQL works and my Hebrew texts.

How to repeat:
I don`t understand the meaning of "How to repeat"....

Update: I had Ubuntu Server 8.x With MySQL 4.1 in the past and I had the same problem. I`ve even downloaded Ubuntu 8.x & MySQL 4.1 and installed it again and tested it. It gives me the same problem (as it gave in the past).
--------------------------------------------------------------------------------

Thank you for the report.

Please provide example of NET code demonstrating the problem.

Doron, see my explanation in Bug#36729.

Tonci,
Thank you for your comment. I do not use ASP pages. The difference between ASP applications and Windows (Rich Client) applications is that you have a special line in ASP (web) to define which charset to use (what Alon did`nt do). I am using a simple select command from VB.NET (Windows Forms application) like this:

Dim cmd as New MySQLCommand("SELECT * FROM USERS;",con)
Dim rs as MySQLDataReader = cmd.ExecuteReader()
rs.Read()
MsgBox(rs.GetString(0))
rs.Close()
...
...

The messegebox returns "????" instead of "שלום". I can NOT define my charset like ASP page can. More then that, I`m using "SET NAMES HEBREW" or "SET NAMES UTF8" etc.

Looking forward for your reply.

Doron.

Doron, you did *not* read my post in Bug#36729...

"First you should start with knowing which charset data is really in. The most
important in charsets is field encoding and session encoding, only if they mismatch, any conversions are done and, in most cases, the double encoding happens. In your case, I think, utf8 bytes are seen as hebrew (latin1...) chars and encoded into utf8 yet again. This is most common corruption and you eventually see A?A?A?A?A?, for example. Why are they seen as such? It's because you are treating data as UTF8 without marking it as such.

So, now you need to check all of your charset settings in MySQL server, your session encoding and stuff making sure they match. There is a lot of reading on this matter in BugsDB as well as in manual. I am confident that you'll find the answer and overcome your problem."

So you and we do not know what you actually have in your table... Further more, I remember seeing such problem (with message box) which was simply solved by setting message box to display Hebrew characters :) Of course, data was properly stored...

Tonci,

I have read your post and let me say (once again) that ASP.NET and VB.NET under visual studio are two different dev. evnironments. I can NOT set my local code page. I depend ONLY on MySQL !!. Plus, I`ve noticed that you mentioned Latin1 in your last reply. I've tryed that and failed again.

I've searched (again) for similar bugs and I found something that I have`nt thought about. Perhaps there are system variables that I should add/change/remove in my my.cnf file ?

Looking Forward,

Doron.

Doron, why are you telling me the differences in IDE (which I, of course know) while missing the real problems I hinted you on?
So, I repeat:
"First you should start with knowing which charset data is really in. The most
important in charsets is field encoding and session encoding, only if they mismatch, any conversions are done and, in most cases, the double encoding happens. In your case, I think, utf8 bytes are seen as hebrew (latin1...) chars and encoded into utf8 yet again. This is most common corruption and you eventually see A?A?A?A?A?, for example. Why are they seen as such? It's because you are treating data as UTF8 without marking it as such."

So, what do you actually have in tables? Please do not use VS, try SELECT HEX(Hebrew_text) or something in command line client and compare that to actual character codes that should have been there. I can not do much until I know what you did to your data.

"So, now you need to check all of your charset settings in MySQL server, your session encoding and stuff making sure they match. There is a lot of reading on this matter in BugsDB as well as in manual. I am confident that you'll find the answer and overcome your problem."

Sure, you should check all charset variables (SHOW VARIBALES LIKE "%char%") and check the manual. Also, do check and try to understand http://forge.mysql.com/wiki/How_to_Use_Charsets_and_Collations_Properly.

You say:
----------
""First you should start with knowing which charset data is really in. The most
important in charsets is field encoding and session encoding, only if they mismatch, any conversions are done and, in most cases, the double encoding happens."

I`m sorry to say again:
------------------------
I can NOT set my 'field' encoding, nor can I set my 'session' enconding. Only in ASP code can I set the following: "<%response.Charset="ISO-8859-1"%>". I`m depending ONLY on MySQL character set to work for me when I`m using VS2008 (Most  people are confusing between ASP.NET and VB.NET). For some reason, you keep repeating what you wrote in the past but it has nothing to do with my case. I have even set all of my "my.cnf" definitions to Hebrew/Latin1/Unicode/UTF8 or any other collation that you may think of.. but without any success to read Hebrew characters back.

Do you have a solution ? a REAL solution ? and not just a link to other bugs. I am sorry to say but MS-SQL works just fine !!! and I`m not making anything there exept of defining Hebrew as my character set for the entire database. I know that MySQL might be free (Community Server) but hey... MS-SQL works ! Do, unless you have a solution to give to this bug (and a big bug it is!), I think that my clients would be willing to pay some money for a product that works.

Anyway, please give me a solution. Regardless of that, I want to thank you for your efforts to help me.

Doron.

Well Doron, seems I'll repeat myself to death here... So what about:

> So, what do you actually have in tables? Please do not use VS, try SELECT HEX(Hebrew_text) or something in command line client and compare that to actual character codes that should have been there. I can not do much until I know what you did to your data.

> Sure, you should check all charset variables (SHOW VARIBALES LIKE "%char%") and check the manual. Also, do check and try to understand http://forge.mysql.com/wiki/How_to_Use_Charsets_and_Collations_Properly.

Now, arguments such as "my truck just goes" are of no relevance or use here. I might as well tell you that I first tried Hebrew chars in VB like 4 years ago. Was working then, works now.

Please understand, if I do not know what you have stuffed into your tables I can not help you. I have not even received SHOW VARIBALES LIKE "%char%" output from you which is like a first thing that should be made available in such cases and so on...

In the case you want me to write some code for you, that is not going to happen as this is BugsDB, not the place where you get working code. So, unless I see all the info requested and a full but compact test case attached here, I will have to close the report as a "Free support request".

Tonci,

I have wrote the command "SHOW VARIABLES LIKE '%char%';" and I recieved the following in return:

character_set_client	        hebrew
character_set_connection	hebrew
character_set_database	        hebrew
character_set_filesystem	hebrew
character_set_results	        hebrew
character_set_server	        hebrew
character_set_system	        utf8
character_sets_dir	        /usr/share/mysql/charsets/

More then that, I eve checked to see if I have the "Hebrew.xml" file in the 'sets_dir' directry. I don`t know if I mentioned it before but I tryed either to use "SET NAMES Hebrew" with my .NET connection and tryed without using it at all. No change at all.

Do you need any other information from me to handle the problem ?

Doron.

Doron,

I need output from SELECT col, hex(col) FROM tab WHERE ...;

I just need a single row here.

If col will only return question marks I need the hebrew word what you inserted.

Susanne,

This is a normal SELECT statement from a table called USERS:

SELECT user_name, user_fullname FROM USERS WHERE user_name = 'test';

I recieved this:

user_name  user_fullname
test	   ?????

Now, This is what I got when executing this:

SELECT user_name, HEX(user_fullname) FROM USERS WHERE user_name = 'test';

I recieved this:

user_name  user_fullname
test	   3F3F3F3F3F

Do you need anything else ? I realy realy (REALY) hope I will be able to solve this problem.

Thanks.

Susanne,

I forgot to add the real Hebrew characters instead of the question marks:

The Hebrew word is: "דורון" (Which is my name in Hebrew)

Thanks !

Doron.

Susanne,

After cheking I found out that the ASCII character for '3F' is a question mark (?)
Link: http://www.simotime.com/asc2ebc1.htm#D3263

And that`s what I`m always getting back from MySQL. I even tryed to install Ubuntu 9.10 Desktop (with GUI) and in Hebrew interface and I got the same back.

Doron.

Doron, you need to forget about the old data entered in MySQL database as it's obviously garbage. You have tried to push into table illegal characters for the charset/collation used and ended up with question marks. Please start from the beginning (forming database/tables ...) and try putting correct characters inside and only then read them back.

Tonci,

I have recreated my database as followed:

CREATE DATABASE SampleDB COLLATE Hebrew CHARACTER SET hebrew_general_ci;
CREATE TABLE SampleTable (myKey int, myValue VARCHAR(30));
INSERT INTO SampleTable VALUES (1,'דורון');
SELECT * FROM SampleTable;

The value returned from the field "myValue" is QUESTION MARKS !!!!!!!!!!!!

if you can not provide a solution (instead of saying "reformat your database" which I tryed to do here, or "everything else is garbage") please tell me so I could move forward with another database engine (such as... ummm... MS-SQL which works!). I understand that after all support costs money, but the lack of ability to display Hebrew characters is beyond! It is the total lack of usability with MySQL.

Doron.

Doron, you got me wrong... I am *not* ignoring you. I'm just trying to explain what happens... Now, the problem is obviously in the *client* doing the insert. Client is obviously presenting itself to have different encoding that Hebrew (which is what you want) either on INSERT or on FETCH. You can confirm this by looking into HEX values in the table. If SELECT HEX(col_with_your_name_in_Hebrew) returns wrong codes, problem is in INSERT. If it returns correct codes, it's in SELECT (fetch).
So, how did you inserted your name (ie. are you sure there are actually correct values stored)? For example, if you tried this from command line on windows, it would most likely fail as windows console is not very keen on anything non-ascii. What says the general query log from server? And so on...
I am sorry if this appears as we could have done more to make it easier but actually we can not... We do not make OS nor your IDE which present major part of problem here.

Doron, let's start over... Please attach small but complete solution with all the DML/DDL statements inside (ie. mysqlcommand.CommandText = "DROP DATABASE IF EXISTS bug53350" and so on).
I'll check on your code then.

Pic 1

Attachment: Bug53350-1.png (image/png, text), 23.95 KiB.

Pic2

Attachment: Bug53350-2.png (image/png, text), 40.62 KiB.

I had enough... Here, I'll do your job:

        Dim strcon, strSQL As String
        Dim objConnector As MySqlConnection
        Dim objCommand As MySqlCommand
        Dim objDR As MySqlDataAdapter
        Dim objDT As DataTable
        strSQL = "SELECT name FROM names"
        'Connecting to MySQL 
        strcon = "User ID=root;Password=***;Host=***;Database=test;Port=3306;charset=hebrew;Allow Zero Datetime=true"

        objConnector = New MySqlConnection(strcon)
        objConnector.Open()

        objCommand = New MySqlCommand("DROP DATABASE IF EXISTS `encodingtest`", objConnector)
        objCommand.ExecuteNonQuery()
        objCommand.CommandText = "CREATE DATABASE `encodingtest` DEFAULT CHARSET hebrew"
        objCommand.ExecuteNonQuery()
        objCommand.CommandText = "USE `encodingtest`"
        objCommand.ExecuteNonQuery()
        objCommand.CommandText = "CREATE TABLE `names` (`Name` varchar(50) default NULL) ENGINE=MyISAM DEFAULT CHARSET hebrew"
        objCommand.ExecuteNonQuery()
        objCommand.CommandText = "INSERT INTO `names` VALUES ('דורון')"
        objCommand.ExecuteNonQuery()

        objDR = New MySqlDataAdapter(strSQL, objConnector)
        objDT = New System.Data.DataTable()
        objDR.AcceptChangesDuringFill = False
        objDR.Fill(objDT)
        lblResult.Text &= vbNewLine & objDT.Rows(0).Item("Name")
        'Disposing objects
        objDR.Dispose()
        objDR = Nothing
        objDT.Dispose()
        objDT = Nothing
        objConnector.Dispose()
        objConnector = Nothing

MySQL server is 5.1.31 x64 on remote OpenSolaris box.

Hi,

Well... it will sound pretty strange but I absolutly MUST write it down. First of all, I would like to thank you for your help. It seems that all of our commnunication throw this bug chat was unnecessary. Instead of telling me: "Here... I`ll do you job" you could simply say "add the charset=Hebrew" in your connection string.

I know it does not matter right now but I had to publish this after so long. By the way, my ERP project is complete. Thank you both MySQL and Ubuntu.

Doron, you are right and an apology is in order... So, sorry for my wording, must have had a bad day or something.

Thanks for your interest in MySQL.