MySQL Forums :: Character Sets, Collation, Unicode :: hebrew in mysql

Advanced Search

Re: hebrew in mysql
Posted by: Nahum Kovalski ()
Date: January 21, 2005 12:54AM

I am posting this everywhere in the hope of solving this quickly

First let me say: Thank you for your time and I will appreciate any help as I am quite desperate

This problem relates specifically to using hebrew in MySQL 4.1

I developed a pure Access application (i.e. both front and back end in Access 2002) and implemented it across the clinics in our organization. The Access system has been in place for about a year and is thankfully working very well.

I decided to convert the backend over to MySQL (4.1.7, on Windows 2000 Server, MySQL ODBC Driver 3.51) as I was worried that some of the clinics were getting to the point of overloading Access's capabilities (file size was around 500 meg in one of the clinics and there were the rare corruptions).

Using DBManager Professional ( to convert over the data structures of the tables and my own software to do the actual data transfer from Access to the new MySQL tables (basically justa series of INSERT statements), I created a test version of my database. The front end continued to be in Access 2002 and used linked tables through the ODBC driver to access the data.

The first hebrew related issue that came up was a collation sequence problem when I tried to UNION two tables. The problem was with the following query:

SELECT Patient_Name, Patient_Type FROM tbl_Regular_Patients
SELECT Patient_Name, "[a string in hebrew appears here]" FROM tbl_Special_Patients

This example would crash out with an error message related to incompatible collation sequences. I fixed it by wrapping the field in a CSTR function call (in the Access frontend):

SELECT Patient_Name, Cstr(Patient_Type) FROM tbl_Regular_Patients
SELECT Patient_Name, "[a string in hebrew appears here]" FROM tbl_Special_Patients

So, I realized that the hebrew strings as stored in MySQL were as such that Access could not work with them. But once I used the CSTR function, Access was able to convert the strings and fields being UNIONed into compatible formats.

I have checked the regional settings on my server and they all appear to be appropriate. I am able to use Notepad and Microsoft Office in hebrew without a problem.

The character set for the MySQL database is utf8. Actually though, this is also strange and perhaps part of the problem. Even after making changes in the my.ini file to get the default characer set and collation to be utf8 (and utf8_collation_gi), if I then run a "show variables" query, a number of the character set variables are still in latin (as in one of the character collations). I assume that this is all inter-related

Anyways, I thought this "trick" with the Cstr call solved everything and after further tests, I finally installed the first MySQL based system at one of our clinics. It has been in use a couple of days now and today I discovered a new problem. I had failed to check during the testing phase some of the lookup tables I use. I have one table that is very simple. It is a conversion table of the hebrew alphabet to the numerical equivalent of each letter. So,

|HLetter | HNumValue | ts (timestamp field)|

HLetter is a unique indexed field and HNumValue is the primary key.

When I start entering data (using DB Manager Professional, NOT Access), I get a problem when I enter the second line.

The first line is the hebrew letter aleph for HLetter and then 1 for HNumValue
The second line is the hebrew letter beit and then 2. But when I try to move to the next line after this 2nd line, I get the following error message: "Duplicate entry [some unintelligible characters] for key 3."

I played with this and realized that many of the hebrew letters that I tried entering in the HLetter field were being read as identical (in terms of the primary index refusing to accept them).

I then tried a simple SELECT statement for doing a sort (again through DB Manager Professional):

SELECT Last_Name FROM tbl_Patients ORDER By Last_Name.

The result of this was a confused sort order. When I went into Access and ran the same query, I also got the confused sort until I changed the SQL to:

SELECT Last_Name FROM tbl_Patients ORDER By CSTR(Last_Name) - my old friend CSTR worked again!. SO I know that my data in the MySQL is not corrupted but that there is a fundamental problem with the indexing and sort order (which are the same problem?)

I am quite desperate as the MySQL version of the system is running live. Fortunately, these lookup table issues are NOT crashing the system but they do make certain functions impossible (or unreliable).

Options: ReplyQuote

Subject Views Written By Posted
hebrew in mysql 16801 Nir Or 11/22/2004 10:27AM
Re: hebrew in mysql 7488 Mike Lischke 11/25/2004 02:48AM
Re: hebrew in mysql 6838 Arik Kfir 11/29/2004 04:43PM
Re: hebrew in mysql 6994 Arik Kfir 11/29/2004 04:45PM
Re: hebrew in mysql 5671 Arik Kfir 12/03/2004 05:26AM
Re: hebrew in mysql 9018 Nahum Kovalski 01/21/2005 12:54AM
Re: hebrew in mysql 4512 Mike Lischke 01/21/2005 01:30AM
Re: hebrew in mysql 4160 Nahum Kovalski 01/22/2005 02:55PM
Re: hebrew in mysql 4052 David Ziants 01/23/2006 06:50AM
Re: hebrew in mysql 3819 Tal Rotbart 01/29/2006 05:38AM
Re: hebrew in mysql [ Found solution ] 7964 Tal Rotbart 02/09/2006 11:04AM
Re: hebrew in mysql 4423 Alexander Barkov 01/24/2005 02:38AM
Re: hebrew in mysql 4580 alon zvi 05/26/2005 03:03PM
a very simple solution 4883 Amir Habib 08/20/2005 02:03PM
Re: Hebrew language support 8956 David Ziants 01/23/2006 04:54AM
Re: Hebrew language support 4896 David Ziants 01/23/2006 05:26AM
Re: Hebrew language support 3798 Avi Cohen 12/27/2006 04:06AM
Re: Hebrew language support 4373 Baruch F 02/07/2007 12:56PM
Re: Hebrew language support 3833 Eli Neria 06/21/2009 10:18AM
Re: Hebrew language support 3492 Rick James 06/21/2009 11:43AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.