Turkish Characters

[Nick]

Hi there,

I'm having trouble running queries that contain Turkish characters. I'm connecting to 4.0.23a mySQL db using a trial version of SQLyog Enterprise using the http tunnelling function.

Here is an example of the query that is causing trouble:

INSERT INTO `book` VALUES (23,'YeÅŸaya','','YeÅŸ',66,61);

You'll notice two strange chars, the i with no dot and the s with a cedilla. I'm getting messages like:

Error Code : 1064

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''YeÄ°IË ÉË ÖwRrÃ#BÃc”' at line 1

(0 ms taken)

I've been using an earlier version of the free SQLyog with no probs to date but needed the tunnelling feature so I'm trying out the Enterprise version.

If anyone would be able to offer help on this I would be very grateful.

Nick

[peterlaursen]

hmmm.

Does it happen only with HTTP-tunnel? Do you have the chance to try out Direct Connection or SSH.

Did you try more ore only one host ? I wonder if webserver or php configuration could play a role. For instance an Apache config file has a list of supported languages. But don't know …

This host that you are connecting to, in that in Turkey or some other place. If it is somewhere else (like US) a config issue is not unlikely.

If you only have a chance to try one place of your own I can give you temporary access to a few other places also running MySQL 4.0.x.

— Sorry for all the questions without answers

BTW: there is no i w/o a dot on my screen !!!

I wander what this displays like around the world:

Quote:

a æ u å æ ø i æ å.

That is local dialect here for 'I am on the island in the stream' . SQLyog handles it perfectly!

[Nick]

Hi Peter,

Thanks for your comments… THe host in question is a Turkish host based in Istanbul: http://www.kriweb.com. I too am in Turkey.

I've just tried it on my own local mysql and I can run a query fine connecting normally via localhost/3306 but if I http tunnel, I get the error.

The other thing I noticed is that if I paste Turkish direct from notepad into sqlYog it transforms all my turkish chatacters into the closest English char. i.e. a s with cedila decome an s, an i with not dot gets a dot…

here are some Turkish chars: iİ ıI şŞ ğĞ öÖ çÇ üÜ

btw SQLyog is very cool – esp being free. I normaly do english language projects, but I'm working on this Turkish one at the mo…

Also, in my regional settings, Language for Non unicode programs is set to Turkish.

Thanks

Nick

[peterlaursen]

Well.. you may have noticed that a total 'overhaul' of the program charset-management is planned for version 5.2. http://www.webyog.com/faq/33_20_en.html

I just asked those questions, because they would need to answered anyway.

But I have a very strange observartion too. See attached. SQLyog 4.1 and 5.02 are different! When I copy your Turkish characters from the browser here into SQLyog 4.1, they display correctly. So do the three Danish characters æ, ø and å (lowercase as well as uppercase). However in 5.02 most non-english characters become cyrillic in the SQL-editor. Both connect to the same server!??????

It is the same with MySQL 4.0, 4.1, 5.0 and 5.1! This must be a bug with the editor! All builds from 4.2 betas to 5.02 do this!

But the failure to display Turkish characters in RESULT-pane is different I think. Actually it works OK independently of the editor as 2nd picture shows. It finds 'ø' as it should no matter that is is displayed in editor as some cyrillic glyph.

Do you have any experience with PHP and Turkish characters? Actually PHP does not support unicode. It is planned for PHP-6. First alpha will be demonstrated for the public at Zend conference in about a month!

[peterlaursen]

That “YeÅŸaya” becomes “YeÄ°IË ÉË ÖwRrÃ#BÃc” undoubtedly is a unicode conversion issue.

since the server is 4.0.x I think the charset is UTF-7 ??

http://en.wikipedia.org/wiki/UTF-7 says: UTF-7 (7-bit Unicode Transformation Format) is a variable-length character encoding that was proposed for representing Unicode-encoded text using a stream of ASCII characters

The characters that are not in ASCII-charset are represented as a stream of ASCII characters. Somewhere in the chain MySQL <--> webserver <--> php <--> tunneller <--> SQLyog this is not being understood – and plain ASCII is transmitted!

Can you work with Turkish characters on your local server? If yes, is that MySQL 4.0 too? If it is a higher version, what charset is used for Turkish then – and what works and what does not ??

The editor thing is another issue. It is a bug that was introduced in the 4.2 development tree (that ended up being named 5.0.x)

[peterlaursen]

more fun!!

see attached!

I have 2 connections open to a 4.0.26 server running on my local. One direct connection, one HTTP-tunnel. With direct connection I can insert all danish characters (though they display as cyrillic in editor). With HTTP-tunnel I get the error much like yours.

'øøø' becomes ASCII values F8-F8-09-0D-0A-42 (and maybe a few more!) with HTTP…. it goes fine with direct connection.

And as pic2 shows all uppercases ÆØÅ and lowercases æå are OK also with HTTP.

But I have a problem with Danish 'ø' and HTTP. Just like you have with a few Turkish characters.

This is tested with MySQL 4.0 Latin1 (Swedish) and Latin5 (Turkish) charset. It makes no difference!

[peterlaursen]

More crazy …

Wtih MySQL 5.1.5 and HTTP-tunnel I can insert uppercases Æ-Ø-Å and lowercase ø but not lowercases æ-å. Get similar error messages.

The query in SQLyog HISTORY tab of course is correct.

The errer is a SERVER error.

But another strange thing: The statements when YOG start sending to the server when opening connection are fewer with HTTP-tunnel then with direct connection.

It is as-it-should-be or some kind of bug?

[peterlaursen]

A little more research:

1) the problem that Dansih characters are substitued with cyrillic.

I have two PC's. It only happens on one of them! They have same locale settings! I even use a KVM-switch so it is the same keyboard. A standard Microsoft keeybord with a cable. Nothing fancy. The standard WinXP-driver.

However if I boot LINUX on the machine/hardware where this substitution does not happen on Windows, it DOES on LINUX/WINE.

But still it only happens with SQLyog 4,2-5.x. Not SQLyog 4.1 and not other programs.

2) Turkish characters.

They seem to be a problem everywhere. Quite a lot reports on that on the Internet. Even MySQL 4.1 UTF8 charset/collation has problems with it. MySQL recommend using UCS for Turkish!

3) Tunnelling problems with Danish letter 'ø'.

I can insert 'ø' and 'øøøøøøøøøøøø'. But not 'øøø' or 'øøøøøøøøø'.

But they are saved as an ASCII-sequence. See attached ??????

With phpMyAdmin there is no problems.

hmmmm ……

Anybody having any clew ??

[Nick]

Thanks for taking the time to look into this problem,

Quote:

Can you work with Turkish characters on your local server?

Yes, I don't seem to have any problems locally. My local mySQL is 4.1.16

Quote:

Do you have any experience with PHP and Turkish characters?

Actually, this is my first PHP project. I normally use ASP. I have a copy of this current project running locally PHP on IIS and it is running ok. To get the Turkish display properly, I do have to run:

Code:

$sql    = 'SET NAMES latin5;';
mysql_query($sql, $link);

Also if get Turkish into the db hosted with my ISP, using the the phpMyAdmin tool they provide Turkish is retrieved from the DB ok:

http://www.bursakilisesi.com/test.php

So it would seem that the db is working ok.

Nick

[peterlaursen]

Quote:

I do have to run:

Code:

$sql    = 'SET NAMES latin5;';
mysql_query($sql, $link);

From where will you have to run that?

From your own script(s)? Do I understand correctly?

Is this correct syntax:

Code:

SET NAMES latin5;

If you execute from SQlyog the server returns:

Error Code : 1193

Unknown system variable 'NAME'

[peterlaursen]

I also tried to edit the tunnelling script, like

Code:

/* connect to the mysql server */
WriteLog ( “Trying to connect” );
$mysql = mysql_connect ( “$host:$port”, $username, $pwd );
$sql    = 'SET NAMES latin5;';  – – or latin1 or latin2
mysql_query($sql, $mysql);

but it seems to have no effect.

[Nick]

peterlaursen wrote on Jan 26 2006, 11:47 PM:

From where will you have to run that?

From your own script(s)? Do I understand correctly?

I run it at the top of the php page that I have written. SET NAMES does seem to work ok on both my local setup and on the live site:

Code:

ob_start();

dl(“mysql.dll”);

$link = mysql_connect('hostname', 'user', 'pw');

if (!$link) {
die('Sorry, could not connect to DB: ' . mysql_error());
}
$db_selected = mysql_select_db('db name', $link);
if (!$db_selected) {
  die ('Can't use foo : ' . mysql_error());
}  
$sql    = 'SET NAMES latin5;';
mysql_query($sql, $link);

?>

etc…

I think the command sets the character set and collation for the current connection:

http://dev.mysql.com/doc/refman/5.0/en/cha…connection.html

Whether or not this is the best way to do things or not I'm not sure, seemd to work for me though…

Nick

[peterlaursen]

Another observation ….

If I enter strings like 'øøø' and 'øøøøøøøøø' with direct connection, I get the data returned correctly (that is not transformed to an ASCII-sequence) when reading them from a HTTP-tunnel. So it is a WRITE-problem only – not a READ-problem.

[peterlaursen]

I don't think this

Quote:

I think the command sets the character set and collation for the current connection:

http://dev.mysql.com/doc/refman/5.0/en/cha…connection.html

is correct. All 'collation'-talk is irrelevant to MySQL 4.0.x

I do not understand why the SET NAMES returns a Server Error from SQLyog if it works from PHP.

[Nick]

Quote:

I do not understand why the SET NAMES returns a Server Error from SQLyog if it works from PHP.

Me neither… But on my local server if I don't run the SET NAMES command I get

Yarat?l??

M?s?r'dan Ç?k??

…

instead of

Yaratılış

Mısır'dan Çıkış

…

(I know that I could set the default character set to latin 5 of course…)

Also, I can confirm that I too can read the Turkish ok but it is only when I am trying to write that I have a problem, either running a command in the query window, by editing in the table view or by trying to transfer the the table from my local DB to the online DB…

Nick

[peterlaursen]

All explained!

Set NAMES=latin1;

(equal to)

Set character_set_connection=latin1;

Set character_set_results=latin1;

Set character_set_client=latin1;

… are supported from MySQL4.1

(NB: edited)

Neither is supported with MySQL 4.0

But it also does not have any effect to edit the tunnelling file when running MySQL 5.0.

[peterlaursen]

And problem is the same with sja.exe and SQLyogEnt.exe.

A DATA-sync with HTTP raises the same error.

And now this (attached) is funny. I can insert a lot of Danish words with 'ø' – except the last one. The error message is the same i get if I try to insert 'øø'. But most often it is the 'Syntax' error.

@ritesh: a problem with your 'home-made' HTTP-implementation ?? 😛 I do believe it must be some protocol issue.

[Ritesh]

Looks like its a problem with the newly introduced SetNames function in the tunneler. We will take a look into it and probably fix it in v5.1.

[peterlaursen]

@ritesh:

let me repeat:

There are two issues with national characters:

1) The WRITE problem with HTTP is also in SQLyog 4.1. It is identical or almost identical as of now.

2) The display problem with the editor was introduced with 4.2. But editor is also new code (multitabbed + delimiter).

[peterlaursen]

to explain the editor issue:

I can use Danish characters in CREATE TABLE and ALTER TABLE and they show correctly in Object Browser. When I doubble-click them into editor the become cyrillic. It also haappens when I type it. Like that on onw Windows machine. On another machine that is dual boot Windows/Linux it happens like that on Linux/Wine, not on Windows.

It is a PURE display thing. I can use those cyrillic names in queries and they return what they should (Danish) in RESULT pane.

[peterlaursen]

similar HTTP-WRITE problems with 'umlaut' (german/swedish (and more))characters ä,ö,ü,ë . Like Danish letters its is only LOWERCASES that causes trouble.

A consideration:

But since it READS all sorts of characters correctly it must read and treat the webserver reponse to the GET request (or whatever request type is used). But how to 'adjust' the charset between server and client 'the other way around' – when WRITING?

I don't think this is related to MySQL or 'SET NAMES'. It is a HTTP-server / HTTP-client(and that is HTTP-implementation in SQLyog) -communication issue.

BTW: the charset info that my local Apache sends on a GET request is

Code:

Accept-Charset: windows-1252, utf-8, utf-16, iso-8859-1;q=0.6, *;q=0.1

so it should surely be able to write all those characters correctly if it applies to WRITE too!

[peterlaursen]

Also 'umlaut' characters display as cyrillic in editor like Danish characters.

[peterlaursen]

Also Spanish ñ display as cyrillic

(the glyph c represents the 's' sound in cyrillic writing)

And what is this button …

[peterlaursen]

I can add that one of the problems reported here – the wrong display of certain national characters in the editor – seems to be related to a corrupted SQLyog.ini -file. With a fresh install to an empty directory – and without copying the SQLyog.ini from the old installation – that problem is solved here.

But this does not solve the HTTP-tunnel write of certain character squences with national characters.

I still believe that is two non-related issues!

[peterlaursen]

I came across this report at mysql.bugs:

http://bugs.mysql.com/bug.php?id=17473

It does not explain all of of it (and not at all the HTTP WRITE issue I think) – but there ARE lots of problems with Turkish characters!

[peterlaursen]

Now this is very very interesting:

http://bugs.mysql.com/bug.php?id=17458

However my HTTP-write problem occurs also with MySQL 4.0 and tested same with php 442 and 512.

But no matter what this thread is worth bookmarking!

[peterlaursen]

I can insert Danish characters into a BLOB column using HTTP with no problem.

But not into a TEXT or VARCHAR. This is true for PHP 4.4.2 and 5.1.2 on my locals and for 4.4.2 on my own webhost.

But on a server running PHP 4.3.2 (you know which one!) I can HTTP-write æøåÆØÅäöüëéñ etc in BLOB, TEXT and VARCHAR columns as well.

@ritesh

doesn't that (that BLOB write and TEXT and VARCHARS don't) more or less prove that it is not an issue with SQLyog?

I really start to suspect that the latest PHP builds are buggy – or the connectors distributed by MySQL!

[peterlaursen]

Now that other issues that confused (a SQLyog 5.1 BETA bug, an issue with sql_mode) are sorted out:

It is VERY CERTAIN that I can write special characters ON THE SAME (Apache + MySQL) server and PHP 4.3.x. With latest versions (5.1.2 and 4.4.2) I can't. I can't find anything in PHP changelogs that relates to this!

added: This has now been verified on more MySQL versions

[peterlaursen]

one more Turkish character issue at bugs.mysql

http://bugs.mysql.com/bug.php?id=17976

[peterlaursen]

To support national characters with HTTP-tunnelling tunnelling file must start with:

[peterlaursen]

A few observations more:

My MySQL 4.0 has the same charset for user table as the default charset (latin1). That explains that I can create user 'ølhund' (and it displays correctly) and 'write 'ølhund' and connect withe user 'ølhund' with HTTP without modifying the tunnel file on that mysql. Because the MySQL Latin1 is very similar to Western European ANSI that SQLyog uses on my system. For some strange reason I cannot create PW 'ølhund' – but that could be a hashing issue.

also check the PHP mb_convert_encoding() function!

[peterlaursen]

With SQLyog beta 4 just available, my HTTP-write porblems with Dansih characters æøåÆØÅ are fixed.

Could you try with Turkish?

[Nick]

peterlaursen wrote on Mar 20 2006, 06:08 PM:

Could you try with Turkish?

That works perfect 🙂

Great job!!

Nick

[peterlaursen]

😀

[Nick]

Hi Peter,

I still seem to be having some issues with SQLyog and Turkish. This time it is with SQLyog connecting normally to a local mysql on the same PC: mysql 4.1.16-nt & sqlyog 5.1 beta 4.

I connect to a latin5 database selecting latin5 in the new dropdown on the connection page and once connected if I run SHOW VARIABLES LIKE '%character%'; I get:

“character_set_client” “latin1”

“character_set_connection” “latin1”

“character_set_database” “latin5”

e”character_set_results” “latin5”

“character_set_server” “latin1”

“character_set_system” “utf8”

If I navigate to a table view and try and change any value in a text field, I get this error:

Error No 1267 – Illegal mix of collations (latin5_turkish_ci, IMPLICIT) and (latin1_sweedish_ci, COERCIBLE) for operation '='

After this error appears I am forced to quit the program as I cannot get the focus away from the record that I tried to edit.

I found though that before editting if I run either:

SET NAMES latin5

or

Set character_set_connection=latin5

Set character_set_client=latin5

before trying to make any changes I can edit without trouble. After running either of these queries I get the following from SHOW VARIABLES LIKE '%character%';

“character_set_client” “latin5”

“character_set_connection” “latin5”

“character_set_database” “latin5”

“character_set_results” “latin5”

“character_set_server” “latin1”

“character_set_system” “utf8”

Also, I have also had trouble when clicking on the header bar at the top of the table view to sort the table. Sometimes it's ok, sometimes the program just crashes/quits without an error. I don't seem to be able to replicate any pattern. Though I think it may have always been after setting the client and connection vars to latin5.

Once more, any help would be appriciated.

Thanks in advance for your help.

Nick Mott

[Nick]

A further anomaly,

I just ran a SELECT query on the table I've been editing and it NULLED out each field of about 5 of 30 records. Then after I tried to return to the table view, it quit/crashed with no error.

Again this was after the client and connection vars were set to latin5.

Nick

[peterlaursen]

Quote:

Then after I tried to return to the table view, it quit/crashed with no error.

There are some issue with the data buffer of the DATA pane with this BETA

What do you mean by 'NULLED OUT' ?

[Nick]

What were values become (NULL)…

I've tried to replicate the problem by everything seems to be ok at the mo…

NIck

[peterlaursen]

Hmm …

I have no comments at the moment.

I can get NULLs if I mismatch utf8 and latin charsets in server and client configuration and proces srings outside the ASCII subrange (ssuch as accented characters).

[Ritesh]

I think I understood the problem. As per your comments, your server default for character_set_client is latin1. Now if a client does not issue any other “Set character_set_” query, the MySQL server expects the data in Latin1.

You will notice that after connection, SQLyog only sends the query: set character_set_result={charset selected from dropdown}. This allows SQLyog to correctly get the data and display it.

For some reason, MySQL is not able to convert data from latin1 to latin5 when the query is sent to the the server. I will take a look into it on Monday.

BTW, any chance of getting some sample data so that we can work on them at our end?

Also, the latest release in v4.1 is v4.1.18. Can you try out that version? I remember MySQL developers telling me that there was a bug in one of the releases which caused the ILLEGAL COLLATION error.

The crash problem seems to be very strange. We are not able to reproduce it at our end. Any chance of getting some sample data? Looks like the crash is data specific.

[peterlaursen]

@ritesh

regarding the crash: compare ticket #98

[Nick]

Hi,

I managed to work out how to get the crash every time:

1. Connect to any database

2. Navigate to a table in the tree veiw

3. Click on the 3 Table Data tab

4. click into a any field of the new record at the bottom of the table view

5. type some text but don't press return

6. click directly on the header bar at the top of that column to initiate a sort

SQLyog dissapears for me more or less everytime what ever db I use and whereever I connect to. Sometimes I need to do steps 4-6 a few times.

Hope this helps

Nick

[Nick]

Still getting the ILLEGAL COLLATION error with mySQL 4.1.18

Nick

[peterlaursen]

Exactly as you describe it here I cannot reproduce a crash.

But I've had crashes with 5.1 BETA when clicking the DATA tab or when clicking a table in Object Browser while DATA tab was active.

I think there is a buffer issue of some kind. But it might depend on hardware configuration and system settings how this materializes.

[Nick]

p.s. I send some test data to Ritesh direct by email,

Nick

[Ritesh]

Nick wrote on Mar 24 2006, 08:06 PM:

p.s. I send some test data to Ritesh direct by email,

Nick

I have received the data. Will work on it on Monday.

[Nick]

Hi again,

Just to let you know that I got the SQLyog to crash on a non Turkish DB:

1. Connect to DB (the one in question has around 100 tables)

2. click on the “3 table data” tab

3. Expand the tree view to show all the tables

4. click on the first table

5. now use the up and down arrow keys (a key press at a time or holding down the key till it auto-repeats) to move up and down the list of tables (the table data shows the first 50 rows of each table as the table name on the right is highlighted)

Within a short while the program crashes.

Hope that is helpful to identify the problem in this beta.

Nick

[Ritesh]

What a coincidence? We fixed this same bug yesterday night after a gruelling 2 hours of debugging session. This bug is not related to Turkish data. It will crash with all kinds of data.

BTW, I would like to offer you a FREE license for all the help that you provided regarding SQLyog.

[Nick]

Ritesh wrote on Mar 28 2006, 11:38 AM:

BTW, I would like to offer you a FREE license for all the help that you provided regarding SQLyog.

Wow, thank you very much 😀

Nick

[peterlaursen]

@Nick.

In another thread I wrote:

Quote:

I you happen to live somewhere in the world where your Windows localisation is not 'western' – (that is correponding to MySQL latin1) – then

1) create this table (with MySQL 4.1 or higher):

Code:

CREATE TABLE tt (id bigint, utftext CHAR(20) CHARACTER SET utf8, ucstext CHAR(20) CHARACTER SET ucs2, latintext CHAR(20) CHARACTER SET latin1);

(replace 'latin1' in the last column with a charset that makes sense with your localisation – and @Nick that is latin5 in your case!)

2) enter some (10-20) rows of data using your national characters. Also enter table and column comments using your national characters

3) Export the table (using the utf8-option) and attach the exported file here with a screenshot of how it should display!

Could I ask you to do so, in return for the program? 🙂

And still I would like to see some similar latin2, latin7, some cyrillic and arabic thing etc. as well!

[Nick]

Hi Peter,

I did as requested but I had some problems along the way. I was using the beta 5 which I downloaded earlier today.

1. SQLyog crashed a few times while clicking in the date table view.

2. When entering Turkish data into an array of 10 rows that I inserted by entering 10 unique ids after creating the table, some data that I subsequently entered, disappeared. This happened on entering the refresh button after I had been pasting snippets of Turkish texts into different fields.

3. After entering text into the table, I tried to add some table and column comments using the alter table function. After I clicked on the alter table button the columns with converted to latin1_sweedish collation (see fig1.gif) and all the Turkish characters in each of the three columns turned to question marks (see fig2.gif). The comments I added were retained as the correct character although are not displaying properly in the Objects tab:

i.e. the comments below are correct:

Field Type Collation Null Key Default Extra Privileges Comment

---

---

---

---

---

---

---

---

---

id bigint(20) NULL YES (NULL) select,insert,update,references ııIIiiİİşşŞğğĞĞ

utftext char(20) latin1_swedish_ci YES (NULL) select,insert,update,references ğğşşİİ

ucstext char(20) latin1_swedish_ci YES (NULL) select,insert,update,references ğğŞŞİİ

latintext char(20) latin1_swedish_ci YES (NULL) select,insert,update,references ğğİİİŞŞİ

but the attached screenshot shows the comments displayed incorrectly (see fig1.gif) and test1.sql shows the comments outputting ok.

[attachment=414:attachment]

[attachment=415:attachment]

4. I couldn't find any reference to the utf8-option mentioned in the last post, so I just exported a second attempt using the “Export table using SQL statements” option.

This seemed to export ok. See fig3.gif for how the table looks in sqlyog, and the test2.sql for the resulted export.

[attachment=416:attachment]

Hope that is helpful, please get back to me if you want me to do other tests.

Nick

test1.sql and test2.sql attached as zip

[peterlaursen]

@nick

Thanks .. very useful I believe!

first a reply: to “I couldn't find any reference to the utf8-option mentioned in the last post”

This is a checkbox on the latest screen of the backup 'powertool' Wizard of BETA5.

Another new thing is that when exporting from MySQL 4.1 ++the simple 'export' option of BETA5 encodes as utf-8. So a simple export is fine as well. So file test2.sql will do!

I am a little confused.

test1.sql does not have character set definitions in the columns definition – test2.sql has.

But test1 has comments – test2 has not.

… Looks like two different tables / two different tries? Could you explain? How did you create test1.sql? With what program versions (MySQL and SQLyog)?

But basically test2.sql was what I was after. I wanted to see if those characteres displayed correctly if I imported them in SQLyog. That was why I had a utf8, and ucs2 and a latin5 column (this was the important point!). They don't – no matter what character set i choose for the connection. A specila note on this: If I choose 'latin5' for the connection the data are handled correctly internally – however displayed using the'western' mapping of the fonts chosen. With this mapping most of your Turkish characters are mapped to Icelandic characters! See attached .jpg

And that is too bad, because even Notepad displys all of those characters correctly (also the one from the latin5 column) – even on my Danish Windows XP. However I can repeatedly import the file, export the table and and characters remain OK! So the server-client interface is OK as far as charset information is concerned, however the fonts rendering library of SQLyog is not up to date (supports only one localisation and that is an unnecessary restriction with a NT-based system!) – or whatever it is that is the problem here. This is a rendering issue! Even conversion of the SQLyog codebase to UNICODE would not solve this I think!

In a HEX editor I can verify that all data keep being OK after several in-outs to/from SQLyog.

Also (what surprises me). Comments from test1.sql are readable in Notepad here. Even though table default is 'latin1'. In a HEX-editor I can see that comments are utf8 encoded. Data are not in this file! Actually I can copy comments from test1.sql to test.2 sql with the clipboard and everything is still OK – except for the display in SQLyog. So looks like MySQL 4.1 always stores comments in utf8 – as suggested by somebody else suggested in another thread!

@Ritesh. I think that is is important that you spend 5 minutes with this and understand it. Especially get the test2.sql, open it in Notepad and import into SQLyog and compare! Also read the last 20 lines here careully .. 😀

Attached file test3.sql is identical to test2.sql except that comments from test1.sql added! Windows XP has no problems in handling this character information!

Those crashes in DATA pane makes me crazy! But I had a few too! also with Beta5 – however it is much more stable here than was beta4.

[peterlaursen]

aha … now see attached …

If I use 'latin5' for the connection AND from 'preferences' choose Turkish script for the DATA/RESULT pane I can actually display it correctly. Most surprisingly I can enter rows with Danish characters æøå in all three columns – export and import again. And even the 'latin5' column does not garbage those characters. Are they mapped with the Türkish script too? @Nick – you know?

But it has no efect on the keyboard in SQLyog. The Windows keyboard configuration does not work in SQLyog. i can easily swith from Turksih to Danish keyboard layout in Notepad for instance – but not in SQLyog. It does not have effect.

BTW:

I request that font settings dialogue should display the script used.

[peterlaursen]

One issue more:

The 'script' settings in 'preferences' for font in the Editor do not work either. If I choose Turkish script and paste Nick's test2.sql into it, all the special Turkish accents are removed ('cedilla-s' becomes plain 's' etc.). This is not a display issue only because if I run the script from Editor pane characters without these accents are saved.

Also the ALTER TABLE removes accents from non-latin1 characters.

Finally the OBJECTS tab does not use the 'script' setting in 'preferences. Nick's Turkish characters are displayed as Icelandic (latin1-mapping) here (comments in table definition).

Looks like a VERY BIG revamp of the Editor code (and more) – or maybe a completely new one – is needed for 5.2 too.

Finally I upload the test3.sql again. First file seems to be damaged.

[peterlaursen]

Not quite true what I wrote.

Actually the Editor can display all scripts of the font used.

You will have to do both:

1) Change windows keyboard setting

2) Change script in SQLyog 'preferences'.

See attached. It could be fun to try this with more different USB-keyboards connected at the same time. 😀

Now when I change back cyrillic gets garbled .. but as the SQL is latin-based I don't think that is a big issue.

But of course Comments should be legal with any character (Even Chinese!) as the program gets fully UNICODE compliant.

@Nick

Can you execute SQL written with Turkish keybord settings and Turkish script setting in SQLyog correctly?

[Nick]

Hi Peter,

Let me try and answer your questions:

Quote:

test1.sql does not have character set definitions in the columns definition – test2.sql has.

But test1 has comments – test2 has not.

… Looks like two different tables / two different tries? Could you explain? How did you create test1.sql? With what program versions (MySQL and SQLyog)?

Yes, test1 and test2 were two different attempts. For both attempts I followed your instructions as close as possible using the sql you posted to create the table. For both attempts I think that I added rows by entering id values and then a mixture of typing in Turkish from the Keyboard and pasting in text from a Turkish website.

I am using mySQL 4.1.18 and SQLyog 5.1 beta 5

Btw I am also running an English Windows XP SP2 with 'English/UK' selected in the 'Regional options' tab, and 'Turkish' selected in the 'Language for non-unicode programs' in the 'Advanced' tab of the 'Regional and Language Options' in the Control Panel.

[attachment=424:attachment]

Quote:

If I use 'latin5' for the connection AND from 'preferences' choose Turkish script for the DATA/RESULT pane I can actually display it correctly. Most surprisingly I can enter rows with Danish characters æøå in all three columns – export and import again. And even the 'latin5' column does not garbage those characters. Are they mapped with the Türkish script too? @Nick – you know?

In tools, preferences, font I have Courier New 9px Turkish script for both SQL editor and data display.

Actually, Turkish characters seem to be handeled fine in the query window and in the table data view. The problems I had/or have is that 1. adding comments using the alter table converted turkish chars to question marks, although the Turkish chars in the comments were retained and 2. The fonts used in the comments and history pane don't render the Turkish correctly. [ooops SQLyog just crashed again as I was flicking between the tabs!]

Quote:

The 'script' settings in 'preferences' for font in the Editor do not work either. If I choose Turkish script and paste Nick's test2.sql into it, all the special Turkish accents are removed ('cedilla-s' becomes plain 's' etc.). This is not a display issue only because if I run the script from Editor pane characters without these accents are saved.

I don't have this problem, I think because as I mentioned above I have 'Turkish' set for non-unicode programs in the Control Panel.

Quote:

Can you execute SQL written with Turkish keybord settings and Turkish script setting in SQLyog correctly?

Yes, I can. That works ok.

[attachment=425:attachment]

Nick

What I forgot to menition is that when I did the ALTER TABLE in the first test, the 'character set utf8', 'character set ucs2' and 'character set latin5' elements were stripped out.

Compare attempt 1:

[attachment=426:attachment]

With attempt 2:

[attachment=427:attachment]

Both tables were created with the same SQL statement, with just the name of the table changed in the second attempt.

Code:

CREATE TABLE tt (id bigint, utftext CHAR(20) CHARACTER SET utf8, ucstext CHAR(20) CHARACTER SET ucs2, latintext CHAR(20) CHARACTER SET latin5);

The difference was I didn't add comments to the second attempt.

Nick

[peterlaursen]

Thanks for your extensive answer.

1) You were right. Choosing Turkish for use with non-unicode programs solved the issue with the editor! (must of course choose a Turkish script as well)

2) But it did not solve the issue with OBJECTS and HISTORY tab. Turkish here display as Icelandic (western mapping)! The Editor settings should take effect here too, I think. Did not solve for CREATE table and ALTER TABLE panes either. Could Editor settings apply here too (maybe it is not possible just like this in this type of grid object)?

3) 'Copy table to other host' (I rather did a copy to another database on the same host) changes data (pretty weird what happens here actually, when inspected in a HEX-editor!). @Ritesh. Could this not 'set names = ut8' when possible? Just like the export does? Or as an option? (Actually I request 'set names = binary' as an option too here and with DATA SYNC and BACKUP as well – because if MySQL versions are totally identical that will be the fastest !!!)

4) Yes!!! ALTER TABLE drops the charset information for the columns. Confirmed. Need a charset column in CREATE TABLE and ALTER TABLE pane.

@Nick .. again thanks for your contribution to this. I think it has been most valuable.

[Nick]

😎 Glad I could help…

Nick

[Ritesh]

After reading the whole explanation, I think all the issues can be summarised into the following points:

ALTER TABLE drops the charset information for the columns.

We have decided to support charset information from RC2 in CREATE TABLE, ALTER TABLE and REORDER COLUMN options.

Copy table to another host ( we are not using UTF8 ).

Starting from v5.1 RC1, SQLyog will issue the command “SET NAMES=utf8”, in both the source and target before exporting data. This will allow an user to export/copy any kind of data to and from MySQL. PS: Both the source and target has to be v4.1 and above for SQLyog to use utf8.

This feature has already been implemented in RC1 development tree and I was able to copy TURKISH characters stored as utf8.

The editor does not support other char set ……in history and objects tabs….

This is a known issue and we will fix it in v5.2 only when we provide complete support for uc2 and multilingual in v5.2.

Adding comments using the alter table converted turkish chars to question marks.

This is a known issue and we will fix it in v5.2 only when we provide complete support for uc2 and multilingual in v5.2.

[peterlaursen]

That is fine with me …

As I wrote elsewhere it is all things that must be fixed 'on the road to 5.2'. Exactly when and in what order is much a planning issue ..

And as Nick made it clear, it is posible now to 'localise' the Editor with the 'non-unicode' setting from Control Panel and the 'script' setting from SQLyog preferences. It is a minor thing that OBJECTS and HISTORY don't display right. To write localized comments you can then manually write an ALTER TABLE statement in editor.

The main thing is that we all understand what happened and what must happen!

[peterlaursen]

@ritesh

I think you are mistaken to one point! It is about the native Windows UNICODE implementation. Win NT/2K/XP does not use UCS-2 but UTF-16 internally. The help file on my system reads (in Danish):

Quote:

En tegnkodningsstandard udviklet af Unicode Consortium, der kan gengive næsten alle skriftssprog i verden. De tilgængelige tegn i Unicode kan repræsenteres i forskellige formater, herunder UTF-8, UTF-16 og UTF-32. I de fleste Windows-grænseflader benyttes UTF-16.

Last paragraph translates to 'In most Windows interfaces UTF-16 is used.' I don't know about the 'unicode layer' in Win98SE. It could be UCS-2. But UCS-2 is mostly considered depreciated.

Read: http://en.wikipedia.org/wiki/UTF-16:

Quote:

UTF-16 is the native internal representation of text in the Microsoft Windows NT, Windows CE, Qualcomm BREW, and Symbian operating systems; the Java and .NET bytecode environments; Mac OS X's Cocoa and Core Foundation frameworks; and the Qt cross-platform graphical widget toolkit.

I don't know if it makes any difference as far as SQLyog 5.2 goes!

[Nick]

Just to let you know that 5.1 Beta 6 seems to have sorted all the issues out and I can now do Turkish perfectly both locally and via http tunnelling.

Thanks very much for working to fix the problems I was having.

Nick

[Ritesh]

BETA 7 will be even better as we have implemented user defined CHARSET selection in HTTP Tunneling. I think we are now done with one byte data handling. v5.2 will have complete support for all kinds of languages.

[peterlaursen]

Code:

I think we are now done with one byte data handling.

1) I think that as long with wont need to display and be able to read and manipulate from GUI data we are done with all data if MySQL is >= 4.1. SET NAMES = utf8 converts data to a stream of bytes that SQLyog/SJA can handle internally. That goes for copy, export/backup, sync etc. But not for display and for keyboard manipulation.

2) But we can also handle all data (even with the GUI – display and keyboard) from multibyte-storage (possibly mixed with single-byte storage) as long as all characters used are convertible to one single byte charset. That is: if data are stored as utf8, ucs2 etc., SET NAMES = latin1/2/5/7 etc will load them into the client in a readable and manipulate-able way as long as this client has support for the Windows parallel for the charset that was specified with SET NAMES – and as long as this SET NAMES makes sense with the data.

But we cannot handle multibyte data where some characters must be converted to different one-byte character set (some latin2 and others to latin5 for instance) to be presented as single-byte. As SQLyog uses one byte internally there is no way to have a Polish l-with-an-accent and a Turkish i-without-a-dot at the same time! Unless there is some strange single byte charset supported by MySQL as well as Windows that has both.

And of course we cannot either handle multibyte characters with the GUI that cannot be represented in a single-byte charset. But we can copy, backup/restore and sync etc if server allows for SET NAMES = utf8.

@ritesh – actually I'd like your comment on whether you agree to this …

[peterlaursen]

This posting is only additional info for those interested!

I may not be 100% correct what I wrote last. I overlooked one link in the chain, and that is XML-encoding! So for conversion of characters from a multibyte-storage to a single byte representation in the SQLyog workspace the XML encoding scheme must support those characters where XML-encoding applies (that is SJA operations). However it should still work as I described above it should still work with most latin-based charset and even cyrillic. But Arabic, Hebrew, Greek, Armenian etc. … well, I really don't know!

For those functionalities that do not use XML representation of data (export/import, copy to other host) I still believe that is is correct what I wrote.

[Author]

Posts