[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: 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.co...q/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!

Attached File(s)

•  versions.jpg (122.11K)
  Number of downloads: 206
•  nchar.jpg (77.46K)
  Number of downloads: 139

[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!

Attached File(s)

•  latin5.jpg (130.44K)
  Number of downloads: 133
•  ______.jpg (87.87K)
  Number of downloads: 120

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

Attached File(s)

•  cnt.jpg (133.37K)
  Number of downloads: 123

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

Attached File(s)

•  proto.jpg (22.78K)
  Number of downloads: 120

[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:

$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:

$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:

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

/* 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, on Jan 26 2006, 11:47 PM, said:

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:

<?php
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);
	
?>
<html>
etc...

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

http://dev.mysql.com...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...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