Character Set Doesn't Work With Umlaut (Ü,ö,ä)

[the_wizzkid]

Hi, I'm using SQLYog Enterprise 6.04 on Win XP SP1, English Language.

Mysqlserver 4.1.22 on SUSE ES 9.0 Linux.

show variables like 'character%';

“Variable_name” “Value”

“character_set_client” “utf8”

“character_set_connection” “latin1”

“character_set_database” “latin1”

“character_set_results” “latin1”

“character_set_server” “latin1”

“character_set_system” “utf8”

“character_sets_dir” “/usr/share/mysql/charsets/”

When doing a query on a DB that contains Umlaute like Ü,ö,ä,ß, then the Result is correct:

set collation_connection = latin1_german1_ci;

then

Set character_set_results=utf8;

select customer, city, addressline1 from IASCUSTOMER where customer = 010003 and company = 1;

Resultpane:

“customer” “city” “addressline1”

“010003” “Jülich” “Tierärztin”

BUT With this same setting, the output from an export to csv is the following:

“customer city addressline1”

“010003 “”Jülich”” “”Tierärztin”””

This is not correct. It should be the same like in the result pane and was until Release 5.21 Ent.

Now I use:

Set character_set_results=latin1;

select customer, city, addressline1 from IASCUSTOMER where customer = 010003 and company = 1;

With this setting, the output from an Export to csv is the following:

Resultpane:

“customer” “city” “addressline1”

“010003” “Jlich” “Tierrztin” (missing Umlaute)

With this same setting, the output from an export to csv is the following:

“customer city addressline1”

“010003 “”Jülich”” “”Tierärztin”

This ist correct.

It would be great to have the same results with one setting- latin1

Otherwise i need to switch arround continuously.

Please help!

I'm using SQLYog a lot and it is a great Tool!

[peterlaursen]

Before posting like this the please search a little in this Forums itself and it the FAQ! There are a least 5 almost identical posts and this FAQ: http://webyog.com/faq/34_148_en.html

You do not seem to understand that character set on the server side and the client side are independent! You shall not SET NAMES yourself. SQLyog does all what is required!

[the_wizzkid]

Hi there, thank you for your advice. I looked into the FAQ which I didn't look at before, but the problem remains the same…

if I start the GUI Client and do NOT use any SET NAMES command, just do my select, it comes out right in the result pane. I can copy the result into Excel, for example, and it is correct.

But when doing an Export of this data from the Resultpane with right klick and “Export table data…” into “CSV”, then it gets scrambled in the CSV.

Doing the same Export into “Excel XML”, I get the correct output…

This worked still in the 5.X version.

[peterlaursen]

we will have to check CSV-export with special characters then!

Can you provide a small SQL dump of a table to reproduce with?

dump, zip and attach here!

[the_wizzkid]

Here is one- its a Table called TMPSALCOMMIS with all german special Characters in it.

[peterlaursen]

issue confirmed!

Will be fixed of course! it will never work to SET NAMES anything else than UTF8 in SQLyog 6. You will need to wait for the fix!

[the_wizzkid]

Hi there,

I didn't see this in the last bug fixes… is this already fixed, or when will it be?

we're using this option very frequently and need it…

Martin

[peterlaursen]

I am sorry for not informing you!

The fix will be included in 6.1.

When we export we use utf8-encoding.

Basically we will need to specify the charset with the LOAD DATA INFILE statement to be sure that it always import correctly, no matter the server settings.

But there is an easy workaround, actually. Just open the file in Notepad .. save as .. explicitly select ANSI for the encoding. Now latin special characters will import.

[yogus]

peterlaursen wrote on Sep 7 2007, 02:49 PM:

I am sorry for not informing you!

The fix will be included in 6.1.

When we export we use utf8-encoding.

Basically we will need to specify the charset with the LOAD DATA INFILE statement to be sure that it always import correctly, no matter the server settings.

But there is an easy workaround, actually. Just open the file in Notepad .. save as .. explicitly select ANSI for the encoding. Now latin special characters will import.

when do you finally fix this bug? i think it's not fixed yet. (when i import an utf8 database-dumb with an “umlaut” (ä,ü,ö) it does not work correctly) ä => ä; ö => ö

please fix this bug, it's so annoying… 🙄

greets

you are doing a great job, thanks!!

[peterlaursen]

It is fixed in 6.16

(if you are requesting CHARSET specification for CSV import (LOAD DATA INFILE)

If you are referrring to something else please read FAQs here

http://webyog.com/faq/category/34/characte…ion-issues.html

.. and detail the problem!

[yogus]

hello,

i mean when i have an .sql-dump (chars are utf8-encoded) of a whole database.

when i create a new db in sqlyog and do an import of the .sqlfile by “Restore from SQL-Dump…” it does not work with utf8-files.

it works by converting the dump to iso-8859-1 with the program iconv.

does sqlyog check which charset the .sql-import-files have?

greets

[peterlaursen]

@yogus

1) What is the program version that you are using? Unicode is supported from 6.0.

2) I asked you to read the FAQs. Did you? In particular this one:

http://webyog.com/faq/content/34/152/en/my…-in-sqlyog.html

3) Please attach a small example of such dump (SMALL and zipped, please!). It is better to do things than to talk about things!

4) “does sqlyog check which charset the .sql-import-files have?”

both yes and no!

as regards yes YES: we detect the encoding of the file.

as regards NO: SQL imports are started in a seperate connection thread. Unlike for the GUI thread we do not SET NAMES for batch jobs, The script it self is supposed to! Does the script contain the statement “SET NAMES UT8” ?

more info: http://webyog.com/faq/content/34/148/en/do…y-language.html

[yogus]

peterlaursen wrote on Mar 27 2008, 07:50 PM:

@yogus

1) What is the program version that you are using? Unicode is supported from 6.0.

2) I asked you to read the FAQs. Did you? In particular this one:

http://webyog.com/faq/content/34/152/en/my…-in-sqlyog.html

3) Please attach a small example of such dump (SMALL and zipped, please!). It is better to do things than to talk about things!

4) “does sqlyog check which charset the .sql-import-files have?”

both yes and no!

as regards yes YES: we detect the encoding of the file.

as regards NO: SQL imports are started in a seperate connection thread. Unlike for the GUI thread we do not SET NAMES for batch jobs, The script it self is supposed to! Does the script contain the statement “SET NAMES UT8” ?

more info: http://webyog.com/faq/content/34/148/en/do…y-language.html

alright, it was my mistake, sorry 🙂

my file does not contain “set names utf8;”.

i thought this is not necessary because i read parts of faq like “Do I need to SET NAMES to display characters of my language? => No, you won't. SQLyog does all what is required!”… 😉

what about a function which recognise utf8-files and automatically send “set names utf8” or whatever the encoding is to the server?

thanks

greets

[peterlaursen]

1)

OK .. I should make it clear in the FAQ that batch jobs will have to SET NAMES themselves according to the encoding of data. Currently only the GUI thread is described in the FAQ.

2)

Let us take an example:

I have the characters here 'æøåÆØÅ' (Danish characters – non-ASCII, MySQL 'latin1' characters and ANSI/western code page characters).

in ANSI they are encoded “E6F8 E5C6 D8C5”

In UTF8 they are encoded “FFFE E600 E500 C600 D800 C500”

(in ANSI such accented characters are 1 byte each – in utf8 they are 2 byte)

… but the point is that “FFFE E600 E500 C600 D800 C500” is also a valid ANSI string that prints like “æøåÆØÅ” (a 12 character string) with western codepage settings. Neither SQLyog nor the MySQL server has a monkeyman's chance to know if you want to import the byte pattern “FFFE E600 E500 C600 D800 C500” as the string “øøåÆØÅ” or as “æøåÆØÅ” unless you tell – basically because they both are valid and only you can tell that. That is what they SET NAMES statement (or SET character_set_client, SET character_set connection etc.) is for!

So I simply do not think we can detect that.

We do detect the file encoding however, so we can read ANSI files containing utf8 byte sequences (and byte sequences in any charset supported by MySQL actually – and even more weird we can import utf8 data stored in a utf16 file!). Actually in SQLyog before 6.0 if you exported a uft8 table you would see “øøåÆØÅ” appearing as “æøåÆØÅ” because with the old versions before 6.0 we did not use utf8 encoding (or another unicode encoding) for the file itself – but we did (or more correct: the server did!) for the byte/octet stream of the data in the file. So it would import correctly, but display garbled in an editor.

But if you export with SQLyog the correct SET NAMES statement will be on top of the file.

[Author]

Posts