Wrong Umlaut-characters On Comments

[jwh]

Hello from Germany,

it seems, that SQLYog uses “latin1” character encoding for table and column comments, where MySQL-Admin and phpMyAdmin use “utf8”. I don't know what is right (I guess utf8 is better) but it should definitly be the same.

I've seen notes about utf8 in the roadmap, maybe you're aleady aware of that.

Never-the-less it's a great tool 🙂 , I like and use it mostly for structure syncronisation.

cu jwh

[peterlaursen]

Hello to Germany 🙂

but it should definitly be the same. No. It is only the same for the ASCII-subrange (nonaccented characters). A German letter like ä or a Danish like ø is encoded with two bytes of data with utf8 – only one byte with Latin1.

What is the MySQL version and SQLyog version that you use?

If recent MySQL versions always use utf8 for comments, I think we have overlooked this. Will be fixed then! We will have to change charset with the 'set_characters_result = utf8' command when writing and reading comments and change back when working with data themselves.

SQLyog uses the server default charset. In your situation that is probably LATIN1 since you are living in Germany – but it maybe LATIN2, ..5, ..7 or a single byte Cyrillic or Arabic character set as well.

With SQLyog 5.1 (in Beta) you can specify another charset to use than the server default. Single byte charsets are supported. Multibyte charsets (utf8, ucs2) will be fully supported with SQLyog 5.2 (a couple of months away).

[Ritesh]

This is strange. All the data/query sent by SQLyog will be in the same format as set by character_set_client. For altering the comments of table and columns, SQLyog issues standard ALTER TABLE SQL command. Looks like we can issue a command like:

Code:

alter table ……. comment _utf8('asasas')

But I need to know why the comments are being sent as only utf8. Is there something special about the comments meta data?

[peterlaursen]

@ritesh: you are supposed to answer – not to ask questions 😀

I see two possibilities:

1)

Recent MySQL versions store comments in UTF8 no matter the server/db/table charset. If so SQLyog must send UTF8 or let the server convert.

2)

It is a bug with MySQL tools. I believe I read that Mike Zinner wrote somewhere that they use utf8 internally. Maybe they simply forgot to convert?

[peterlaursen]

I cannot reproduce any issue, as long as server has a single byte charset as default.

Table and column comments can be written here with SQLyog and are displayed correctly by Query Browser and vice vesa. (server default is Latin1). The 'Table Editor' of MySQL Administrator and Query Browser is the same.

@jwh: I think I must ask you to explain somewhat more in detail. Is your server running UTF8 as default?

Also check your my.ini file: there might be a 'default-character-set=' in both the [client] and the [mysqld] section. I think the MySQL GUI tools read from the [client] section (and maybe phpmyadmin does too).

Do you have such statements in both sections and how do they read?

[peterlaursen]

However if I change server default to UTF8, the information that I entered before is displayed correctly by MySQL tools but not by SQLyog. Se attached. SQLyog has just been restarted and sent “Set character_set_results=default”

I now think that the server 'silently' converts any comment to utf8 when storing them and back to character_set_result when sending them to a client.

Actually if I execute ” Set character_set_results=latin1 ” they are again displayed correctly by SQLyog.

Now this is VERY INTERSTING! 😀

Because I tested not only with comments and also with data. Actually I can use UTF8 for the server (or a database or a table) and use it with SQLyog and the subset of the UFT8 charset included with Latin1, if I only choose LATIN1 from Connections Manager! The server does the conversion 'silently' – but of course only with the subset of UTF-characters supported by LATIN1.

basically when working with unicode data, choose a single-byte CHARSET that corresponds to your keyboard!

@ritesh: did you know? Did we understand one another coreectly?

[peterlaursen]

Now even with this table definition:

Code:

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

.. I can write correctly to all columns from keyboard if only I execute 'Set character_set_results=latin1;' no matter what the server default because my keyboard layout and my Windows localisation corresponds to latin1! The server does anything else 'silently'!

Everything I enter from SQLyog is displayed correctly by MySQL tools. And 'edit' from QB is 'greyed out' actually. (I know entering latin1 data into a latin5 table is nonsence .. this was just to point it out!)

@jwh …

I think you are using utf8 as default set for the server.

Then SQLog sends 'Set character_set_results=utf8;' or 'Set character_set_results=default;' (with 5.02 and 5.1 beta respectively). It is a bad idea, as it does not support the default charset utf8 yet! As long as multibyte charsets are not supported, it was better to get the windows localization an 'set character_set_result' accordingly or display a message box like 'Default (multibyte) character set for server is not yet supported by SQLyog – you should choose a charset correspondidng to your windows localisation'

With SQLyog 5.02 you may write this statement manually: 'Set character_set_results=latin1;'. With SQLyog 5.1 BETA4 you can choose the Latin1 charset from connections manager.

[peterlaursen]

And to work with UTF8 and UCS2 data with HTTP-tunnelling I simply replace everything in the function SetName() in the tunnelling file

with

Code:

$query = “set character_set_results = latin1”;

The server will transform any Latin1 character to UTF8 or UCS2 and vice versa 'on the fly' as needed.

This might not work with all character sets, as the tunneller file uses ISO-8859 internally, and only these characters http://www.natural-innovations.com/wa/doc-charset.html will be transferred correctly (I believe). Cental European (Czech, Polish for instance) special consonants are not supported so working with central/eastern European UNICODE characters from a latin2-client for instance using HTTP-tunnelling won't be possible this way. To support a wider character set with HTTP-tunnel the tunneling file must use UTF-8 internally (what is possible with PHP, but SQLyog cannot communicate with the tunneller using UTF-8 yet)

Sorry Szodono :huh:

[Ritesh]

peterlaursen wrote on Mar 24 2006, 08:37 PM:

@ritesh: did you know? Did we understand one another coreectly?

This I figured out yesterday itself. Even if the data is withing the latin1 subset range of utf8, they are not displayed correctly. We will work on it Monday and update this thread with more info.

[peterlaursen]

Now I wrote this FAQ: http://www.webyog.com/faq/34_102_en.html

It will be adjusted as needed…

But I have no issue with the latin1 subset of utf8 and ucs2 data when working from SQLyog if only I choose 'latin1' and not 'default' from connections manager.

[peterlaursen]

@ritesh:

BTW – a question 🙂

How does the SJA SET NAMES or set_character_results ?

Should such option not be added to theand sections of the jobfile?

Like

Code:

latin1

or

Code:

default

[peterlaursen]

Or maybe better: simply allow for a … to specify a (sequence of) SQL-command(s) to be executed as the first command(s) executed after connection.

To let user for instance

Code:

“set @job_start = now(); set character_set_results = latin9; set sql_mode = 'strict_all_tables, no_zeros_in_date”

With this example you might for instance include this code in a Notifications Job

Code:

set @start = now() + 0;
…
…
select concat(((now() + 0) – @start ), ' seconds passed since job started');
…
…
select concat('Job finished after ',((now() + 0) – @start), ' seconds');

(though I know that the 'set @job_start = now()' can be included in the 'main SQL block as well allready)

Default for this could then be simply

Code:

“set character_set_results = default; set sql_mode = ''”

and user might simply specify

Code:

“”

if she does not want any executed.

[peterlaursen]

@ritesh:

There was a question

How does the SJA SET NAMES or set_character_results ?

BTW: just came across this related thing:

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

[peterlaursen]

Now I tried with this DB:

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

to insert some data with special characters (connected with 'set character_set_results = latin1')

Next tried to export 3 different ways:

* With SQLyog (connected with 'set character_set_results = latin1')

* With SQLyog (connected with 'set character_set_results = default')

* With SJA.

The generated files all looked right like

Code:

CREATE TABLE `tt` (
`id` bigint(20) default NULL COMMENT 'æ',
`utftext` char(20) default NULL COMMENT 'ø',
`ucstext` char(20) default NULL COMMENT 'Ã¥',
`latintext` char(20) default NULL COMMENT 'ñ'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='ølhunden æ uiæøiæå';

/*Data for the table `tt` */

insert into `tt` (`id`,`utftext`,`ucstext`,`latintext`) values (NULL,'æ','ø','ø');
insert into `tt` (`id`,`utftext`,`ucstext`,`latintext`) values (NULL,'ølhund','øllebrød','øretæve');

Everything is correct here and all imported correctly.

But I wonder what would happen on my system if there were special characters in the UNICODE columns that are not in the latin1 (ANSI 'western' character) (sub)set?

[peterlaursen]

Hello everyone out there … could I ask for a little bit help here? 😀

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:

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)

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 and attach the exported file here with a screenshot of how it should display!

I think only single-byte character set are interesting at this point of time, but anything is welcome!

[peterlaursen]

@ritesh: I repeat

There was a question

– and I have seen you at the Forums twice now without bothering to answer it.

How does the SJA SET NAMES or set_character_results ?

How would you expect anyone to beta-test your program if you don't answer their questions?

Will I have to spend 4 hours finding out myself, when you can answer in 30 seconds?

[Ritesh]

Oooops……I must have missed it. Sorry for the delay.

Right now, SQLyogTunnel.php will execute SET NAMES = {data} which we get from “show variables like '%character%'. We used this method in the earlier versions of SQLyog.

We will be changing it now as we do with the direct connection. We will add an extra tag in the XML data sent to the tunneler e.g. which will have the value that the user selected from the connection window.

[peterlaursen]

😛

I don't think you catched the point exactly!

I was asking: How does the SJA SET NAMES or set_character_results?

I was not asking about tunnelling. I can read the tunneller myself!

I meant “How does the SJA SET NAMES or set_character_results with direct connection?

But I suppose then that that additional TAG will apply to direct connection as well?

(dont forget thatand should each have a charset tag!)

the SET NAMES = {data} – now what is {data}?

show variables like '%character%' returns all those

Code:

character_set_client
character_set_connection
character_set_database
character_set_filesystem
character_set_results
character_set_server
character_set_system

which one is used by the SJA as of now?

Now what about databases having tables using different charsets? Not to speak of 'mixed tables'?

[Author]

Posts