Backup encoding problem

[bill]

I have a table with russian characters … After backup it was saved with ???? in place of these characters.

the table was created with DEFAULT CHARSET=cp1251.

[Ritesh]

Are you sure you are viewing it in an editor with correct charset setting?

BTW, if the data is not confidential then can you zip and mail me the .MYD, .MYI and .FRM file for the table?

[Chronograph]

[attachment=397:attachment]

I have some problem when i export base or table as batch script on localhost! 🙁

mysql-4.1.18-win32

default-character-set=cp1251

But when i export base using mysqldump.exe there is no problem!

[peterlaursen]

Looks like SQLyog is using the LATIN1 ANSI encoding – even when that is not system default!

I think learning about non-english localizations must be priority in Bangalore now! 😮

[peterlaursen]

I compared 'MySQL Administrator' and 'MySQL Query Browser'

1) a backup with MA preserves cyrillic charset information. When opened in Notepad table 'sizes' looks like

Quote:

INSERT INTO `sizes` (`size_id`,`size`) VALUES

(01,'Мужские'),

(02,'Женские'),

(03,'Унисекс'),

(04,'Женские (мини)'),

(05,'Карманные'),

(06,'Каминные'),

(07,'Настольные'),

(08,'Настенные'),

(09,'Напольные'),

(10,'Метеоприборы');

2) An export as HTML/CSV with MQB also preserves charset information

3) No export format from SQLyog preserves the charset info. Looks like it is irreversible converted/destroyed when it is loaded into SQLyog.

4) I a font is selected that supports cyrillic (here ARIAL is used) MQB even displays cyrillic display.

5) I think there is a bug with SQLyog 5.1 beta2: It is not possible to change the font.

6) And the fonts' control is somewhat outdated: you must choose a 'script' (a mapping/encoding) That probably is how the library does but it should be possible automatically choose a 'script' that corresponds to the COLUMN default setting. You need not in MQB – it finds out itself which 'script' to use. But I don't think this is related to the export problem.

[peterlaursen]

Also ..

I believe I read here http://forums.mysql.com/read.php?108,32285,74300#msg-74300 that Mike Zinner wrote tha MQB uses unicode internally. But it seems like he has edited!

[Chronograph]

This bug of encoding appears only at export of a dump!

When I import to CSV or HTML files – all correctly.

[peterlaursen]

On my system (Dansih localized Windows) HTML and CSV also produces '?'s. this is what I get from CSV export of sizes table

Code:

“01”;”???????”
“02”;”???????”
“03”;”???????”
“04”;”??????? (????)”
“05”;”?????????”
“06”;”????????”
“07”;”??????????”
“08”;”?????????”
“09”;”?????????”
“10”;”????????????”

Also it displays like that if I choose a font in Notepad that supports cyrillic (such as Arial). In a hex editor all cyrillic characters have become '3F' (decimal 63) – that is ASCII/ANSI/UTF8 for '?' That must mean that the information is not there and was lost!

But @Chrono: do data display as cyrillic in DATA and RESULT panes on your system?

I can add that all types of export export my national characters (æøåÆØÅ) correctly. I have an issue with HTTP-write however, but probably quite another issue!

[peterlaursen]

Also note that it does not becomes SQUARES when displayed in the GRIDs (as non-printable characters would display). It becomes chr(63) = '?' and is consequently displayed and exported as such.

@ritesh: I doubt that you coded taht yourself that way?! The VC++ library code that does this?

[Ritesh]

Thank you for your data.

We are working on it and we will revert back soon.

[peterlaursen]

Of course I forgot one thing. When working with your data I must SET NAMES accordingly. I did that now. See attached. Now your cyrillc charcters display as various accented latin-based characters. But that is of course due to the ANSI localization (mapping of characters 128-255) on my Windows installation.

Export as HTML and CSV yields the same.

However export as .SQL still produces '?'

Code:

/*Data for the table `sizes` */

insert into `sizes` (`size_id`,`size`) values (01,'???????');
insert into `sizes` (`size_id`,`size`) values (02,'???????');
insert into `sizes` (`size_id`,`size`) values (03,'???????');
insert into `sizes` (`size_id`,`size`) values (04,'??????? (????)');
insert into `sizes` (`size_id`,`size`) values (05,'?????????');
insert into `sizes` (`size_id`,`size`) values (06,'????????');
insert into `sizes` (`size_id`,`size`) values (07,'??????????');
insert into `sizes` (`size_id`,`size`) values (08,'?????????');
insert into `sizes` (`size_id`,`size`) values (09,'?????????');
insert into `sizes` (`size_id`,`size`) values (10,'????????????');

The same thing happens with Backup 'powertool'.

1)

Now .. it looks like both backup tools fail to map non-ASCII characters with cp1251 charset. Now the developers will have to consider if same thing happens with other charsets.

2)

SET NAMES should be done on a database (or better table or better column) basis when a DB has another charset than the server default – not only on a server basis

[peterlaursen]

just a small observation:

HTML-export generates like:

Code:

01

Мужские

Note it does not use ISO 8859 '&aring' for 'е' but simply hex E5 (decimal 229) etc.

However Firefox is smart enough to realize that this must be cyrillic!

Even when there is no charset information in the header of the HTML-file

IE and Opera does not! Unless I force them to use windows-1251 encoding.

(@ritesh: shouldn't there be a charset=”windows-1251″ declaration in HTML-file when data are cp1251?).

I think a as-fast-as-possible implementation of UTF-8 is the only real solution to all these problems.

In another thread I linked to a few libraries!

[Ritesh]

peterlaursen wrote on Mar 16 2006, 02:16 PM:

just a small observation:

HTML-export generates like:

Code:

01

Мужские

Note it does not use ISO 8859 '&aring' for 'е' but simply hex E5 (decimal 229) etc.

However Firefox is smart enough to realize that this must be cyrillic!

Even when there is no charset information in the header of the HTML-file

IE and Opera does not! Unless I force them to use windows-1251 encoding.

(@ritesh: shouldn't there be a charset=”windows-1251″ declaration in HTML-file when data are cp1251?).

I think a as-fast-as-possible implementation of UTF-8 is the only real solution to all these problems.

In another thread I linked to a few libraries!

Yes indeed. We have to specidy that in the HTML header. We will work on it once we fix the dump, write and the user issue with accented characters.

[Ritesh]

@chrono: What does the query

Code:

show variables like '%character%'

return?

[peterlaursen]

Quote:

@chrono: What does the query

CODE

show variables like '%character%'

return?

that was a point!

@chrono: this database shall be the last 'USE'd when you query show variables like '%character%'

@chrono: and did you ever tell what was the MySQL version?

now lets say it returns cp1251 for the database and something else for the server! Then as SQLyog SETs NAMES with the server charset then it does not communicate correctly with the database. That also applies with MySQL <= 4.0 as SET NAMES does not work here and therefore is not sent at all. Now note what chrono wrote at the start

Quote:

the table was created with DEFAULT CHARSET=cp1251.

Well if cp1251 was default for the server, than there would be no need to create the table or database) with that charset

@ritesh: that was your point I guess? Actually if would be pretty simple to SET NAMES as database every time SQLyog sends a 'use ..' statement. However if people write SQL themselves like 'select … from mydb.mytable …' it is more complicated – probably not possible. Not needed either I think. When people are working from the SQL-pane, they should know what SQL to use!

[peterlaursen]

Now …

when starting the server MySQL 4.0 with cp1251 as default for the server, the exported file holds the HEX value of each (cyrillic) character and not just '?' (char(63)).

But this is not the case with MySQL 5.0. Correct HEX-values are not exported even with cp1251 as MySQL 5.0 server default on my system. All cyrillic are char(63) when exported, but they display in datapane as accented latins when server default is cp1251 (because that are the glyphs that those char-numbers are displayed with on a ANSI-Western system). If server default is latin1 they display AND export as char(63).

Now why this difference of MySQL 4.0 and later?? Does SET NAMES have effect on batch jobs?

[Chronograph]

What does the query show variables like '%character%'?

Code:

Variable_name;Value
character_set_client;cp1251
character_set_connection;cp1251
character_set_database;cp1251
character_set_results;cp1251
character_set_server;cp1251
character_set_system;utf8
character_sets_dir;D:ServerMySQLsharecharsets/

[Ritesh]

I think I understood the problem.

v5.1 BETA 3 will have this issue fixed.

[peterlaursen]

@ritesh

would you mind explain a little? I would like to understand too!

[Chronograph]

Ritesh wrote on Mar 17 2006, 11:31 AM:

I think I understood the problem.

v5.1 BETA 3 will have this issue fixed.

Thanks! Perfectly!

I hope and believe because this program is very convenient!

[peterlaursen]

It works fine here now with BETA3 and your data.

Get BETA3 from the same link as BETA2 .. webpage not updated yet.

(http://www.webyog.com/forums/index.php?showtopic=1943&view=getnewpost)

[Author]

Posts