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

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:
That is local dialect here for 'I am on the island in the stream' . SQLyog handles it perfectly!

Hi Peter,

Thanks for your comments... THe host in question is a Turkish host based in Istanbul: 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

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!

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)

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!

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?

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 ??

Thanks for taking the time to look into this problem,



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



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:



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

From where will you have to run that?
From your own script(s)? Do I understand correctly?

Is this correct syntax:
If you execute from SQlyog the server returns:
Error Code : 1193
Unknown system variable 'NAME'

I also tried to edit the tunnelling script, like


but it seems to have no effect.

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:



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

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.

I don't think this
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.

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

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.

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.

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.

@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).

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.

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
so it should surely be able to write all those characters correctly if it applies to WRITE too!

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

Also Spanish ñ display as cyrillic
(the glyph c represents the 's' sound in cyrillic writing)

And what is this button ...

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!

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!

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!

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!

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

one more Turkish character issue at bugs.mysql
http://bugs.mysql.com/bug.php?id=17976

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

<?php
declare(encoding="utf-8");

and all queries that SQLyog send must be converted to uft-8 using an appropriate C-string-function for this!
I guess that is how simple that is!


Is does NOT solve issues with the MySQL character sets such as turkish i-with-dot and i-without-dot. This is a MySQL issue and not a SQLyog issue!

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!

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

That works perfect :-)

Great job!!

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

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

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

What do you mean by 'NULLED OUT' ?

What were values become (NULL)...

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

NIck

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).

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.

@ritesh

regarding the crash: compare ticket #98

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

Still getting the ILLEGAL COLLATION error with mySQL 4.1.18

Nick

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.

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

Nick

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

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

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.

Wow, thank you very much

Nick

@Nick.
In another thread I wrote:



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!