First I tried to copy a table between:
remote host (Mysql 4.0.24 Debian)
and
localhost (3.23.49nt on XP)
using SQLcommunity 6.16. The application froze.
What's interesting is that the problem did not exist in old SQLyog 4.06 freeware.
I've also noticed that even when I try to copy tables within the same host but different dbs, the app freezes as well.
Upgrading to the latest 6.55 gave no change.
Please help,
Joey
Full Version: Copy Tables Error In Sqlcommunity
It is no excuse but why do you use MySQL 3.23.49nt on localhost?
If this problem is related to that server version we won't fix it!
At least (if it is necessary to use 3.23) you should use 3.23.58.
Which of the one is <source> and which is <target>?
How can you tell it froze? How long did you wait? Did you see in Task Manger if programs (both SQLyog and MySQL) were active (using CPU)?
Please give some details about the database like number and size of tables. For instance do this
.. and paste the result here (after showing results in text mode). That will give us an idea where to start! Also tell: how long did it last before SHOW TABLE STATUS returned a result?
If this problem is related to that server version we won't fix it!
At least (if it is necessary to use 3.23) you should use 3.23.58.
Which of the one is <source> and which is <target>?
How can you tell it froze? How long did you wait? Did you see in Task Manger if programs (both SQLyog and MySQL) were active (using CPU)?
Please give some details about the database like number and size of tables. For instance do this
CODE
USE the_database;
SHOW TABLE STATUS;
SHOW TABLE STATUS;
.. and paste the result here (after showing results in text mode). That will give us an idea where to start! Also tell: how long did it last before SHOW TABLE STATUS returned a result?
QUOTE (peterlaursen @ May 21 2008, 01:11 PM) 
It is no excuse but why do you use MySQL 3.23.49nt on localhost?
If this problem is related to that server version we won't fix it!
If this problem is related to that server version we won't fix it!
To tell you the truth, I use it just because it has always worked fine for me and I've never found it
necessary to upgrade;) Once I tried with 4.xx but had some trouble with configuration.
QUOTE
At least (if it is necessary to use 3.23) you should use 3.23.58.
I'll try to upgrade then.
QUOTE
Which of the one is <source> and which is <target>?
The source was the remote and localhost the target.
But I also tried within the localhost two databases and it also failed.
QUOTE
How can you tell it froze? How long did you wait? Did you see in Task Manger if programs (both SQLyog and MySQL) were active (using CPU)?
The cursor goes into sandglass symbol and stays like that 'forever' (currently I've been waiting for more than 10 minutes).
In Task manager both applications (SQLyog and Mysql) use 0% Cpu.
QUOTE
Please give some details about the database like number and size of tables. For instance do this
.. and paste the result here (after showing results in text mode). That will give us an idea where to start! Also tell: how long did it last before SHOW TABLE STATUS returned a result?
CODE
USE the_database;
SHOW TABLE STATUS;
SHOW TABLE STATUS;
.. and paste the result here (after showing results in text mode). That will give us an idea where to start! Also tell: how long did it last before SHOW TABLE STATUS returned a result?
The db is a few tables with 208 rows max. The table I'm trying to copy is as follows:
(I'm sorry for the messy presentation, I hope you can figure out which columns match the headings, it is copied from SQLyog)
CODE
Name Type Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Create_options Comment
words_1 MyISAM Dynamic 208 34 7236 4294967295 4096 0 261 2008-05-21 16:18:35 2008-05-21 20:28:42 (NULL)
words_1 MyISAM Dynamic 208 34 7236 4294967295 4096 0 261 2008-05-21 16:18:35 2008-05-21 20:28:42 (NULL)
ps. currently SQLyog is not responding and I have to kill the task in Task Manager.
ps2 at the time of SQLyog's hanging, the Mysql server is responding - I entered the cmd mode and could see table status (in 0,05sec)
OK .. one idea!
Please try turning everything related ot firewall off!
Do you use ZoneAlarm ?
Please try turning everything related ot firewall off!
Do you use ZoneAlarm ?
I'm using ESET smart security. Turning off gave no change. Hangs up the same.
I managed to grab temporary files generated at the moment of the crash when you decide to send the report to ms.
appcompat.txt
and
SQLyog.exe.mdmp
I you want I'll send the files. There's a good deal of technical stuff that could probably cast some light?
I managed to grab temporary files generated at the moment of the crash when you decide to send the report to ms.
appcompat.txt
and
SQLyog.exe.mdmp
I you want I'll send the files. There's a good deal of technical stuff that could probably cast some light?
QUOTE (Joey33 @ May 23 2008, 04:57 AM)
I'm using ESET smart security. Turning off gave no change. Hangs up the same.
I managed to grab temporary files generated at the moment of the crash when you decide to send the report to ms.
appcompat.txt
and
SQLyog.exe.mdmp
I you want I'll send the files. There's a good deal of technical stuff that could probably cast some light?
I managed to grab temporary files generated at the moment of the crash when you decide to send the report to ms.
appcompat.txt
and
SQLyog.exe.mdmp
I you want I'll send the files. There's a good deal of technical stuff that could probably cast some light?
Hi,
Can you please provide proper testcase with all the details like crash dump file, structure of the table and also SQLyog and MySQL versions used both in source and target?
Regards,
Navya
QUOTE (navyashree.r @ May 23 2008, 05:45 AM)
Hi,
Can you please provide proper testcase with all the details like crash dump file, structure of the table and also SQLyog and MySQL versions used both in source and target?
Regards,
Navya
Can you please provide proper testcase with all the details like crash dump file, structure of the table and also SQLyog and MySQL versions used both in source and target?
Regards,
Navya
Hello,
remote host (Mysql 4.0.24 Debian)
and
localhost (3.23.49nt on XP)
BUT I have just discovered that the blame is on the version of my localhost.
I have connected between two remotes
4.0.24
and
5.0.51-2
and copying works!
If you want however I can post all the data you say to your email.
Regards,
Joey
Edited:
I've just upgraded to 4.1.22-community-nt (I always trust older tested versions) and copying works smoothly:) The only problem I still have is losing latin2 Polish characters when transfering data - if you could give me a clue as to where to look for solution? I've read sth about character coding collations but that's still a bit of mistery to me...
THANK YOU FOR YOUR GENEROUS HELP AND GREAT APPLICATION!
"The only problem I still have is losing latin2 Polish characters when transfering data".
So let us concentrate on that! Please:
1) First Try 6.56
2) Zip and attach a SQL dump from a small example of a table that fails to copy and that has such data. Please both data from surce and from target after copy.
3) Execute on both hosts SHOW VARIABLES LIKE '%character%'; and paste the results
So let us concentrate on that! Please:
1) First Try 6.56
2) Zip and attach a SQL dump from a small example of a table that fails to copy and that has such data. Please both data from surce and from target after copy.
3) Execute on both hosts SHOW VARIABLES LIKE '%character%'; and paste the results
1) First Try 6.56
Ok, I didn't know there's a new version;) I'll upgrade then. I've been reading into mysql manual and doing a series of experiments and I suppose the problem is due target server version, it doesn't attach DEFAULT CHARSET=latin2 to create table statement when dumping. But see yourself. I may be wrong as the the cause.
2) Zip and attach a SQL dump from a small example of a table that fails to copy and that has such data. Please both data from surce and from target after copy.
Cannot attach the files "Upload failed. You are not permitted to upload this type of file".
Here are the listings of sql:
PRE_COPY
POST_COPY
3) Execute on both hosts SHOW VARIABLES LIKE '%character%'; and paste the results
SOURCE: 5.0.51-2
character_set_client utf8
character_set_connection utf8
character_set_database latin2
character_set_filesystem binary
character_set_results utf8
character_set_server latin2
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
TARGET: 4.0.24
character_set latin2
character_sets latin1 big5 cp1251 cp1257 croat czech danish dec8 dos estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru koi8_ukr latin1_de latin2 latin5 sjis swe7 tis620 ujis usa7 win1250 win1251ukr win1251
convert_character_set
There are these two words that lose latin2 chars: 'Uk?ad okresowy' and 'Wi?zania chemiczne'.
ADDED:
ps. when trying to restore a table from sql dump, the target server (4.0.24) throws an error in sql syntax - it doesn't allow for DEFAULT CHARSET=latin2 in create table. It also doesn't understand 'ALTER TABLE cat_2 CHARACTER SET latin2 COLLATE latin2_general_ci' when ther's COLLATE .. section;
Ok, I didn't know there's a new version;) I'll upgrade then. I've been reading into mysql manual and doing a series of experiments and I suppose the problem is due target server version, it doesn't attach DEFAULT CHARSET=latin2 to create table statement when dumping. But see yourself. I may be wrong as the the cause.
2) Zip and attach a SQL dump from a small example of a table that fails to copy and that has such data. Please both data from surce and from target after copy.
Cannot attach the files "Upload failed. You are not permitted to upload this type of file".
Here are the listings of sql:
PRE_COPY
CODE
/*
SQLyog Community Edition- MySQL GUI v6.55
MySQL - 5.0.51-2+tld2-log : Database - moja1
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`moja1` /*!40100 DEFAULT CHARACTER SET latin2 */;
USE `moja1`;
/*Table structure for table `cat_2` */
DROP TABLE IF EXISTS `cat_2`;
CREATE TABLE `cat_2` (
`cat_id` int(10) unsigned NOT NULL auto_increment,
`cat_name` varchar(30) default NULL,
PRIMARY KEY (`cat_id`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin2;
/*Data for the table `cat_2` */
insert into `cat_2`(`cat_id`,`cat_name`) values (8,'Układ okresowy'),(6,'Budowa atomu'),(3,'Substancje chemiczne'),(7,'mol'),(5,'Wiązania chemiczne'),(9,'Chemia nieorganiczna'),(10,'Pierwiastki'),(11,'Podstawy chemii'),(12,'Reakcje redoks'),(13,'Elektrochemia');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
SQLyog Community Edition- MySQL GUI v6.55
MySQL - 5.0.51-2+tld2-log : Database - moja1
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`moja1` /*!40100 DEFAULT CHARACTER SET latin2 */;
USE `moja1`;
/*Table structure for table `cat_2` */
DROP TABLE IF EXISTS `cat_2`;
CREATE TABLE `cat_2` (
`cat_id` int(10) unsigned NOT NULL auto_increment,
`cat_name` varchar(30) default NULL,
PRIMARY KEY (`cat_id`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin2;
/*Data for the table `cat_2` */
insert into `cat_2`(`cat_id`,`cat_name`) values (8,'Układ okresowy'),(6,'Budowa atomu'),(3,'Substancje chemiczne'),(7,'mol'),(5,'Wiązania chemiczne'),(9,'Chemia nieorganiczna'),(10,'Pierwiastki'),(11,'Podstawy chemii'),(12,'Reakcje redoks'),(13,'Elektrochemia');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
POST_COPY
CODE
/*
SQLyog Community Edition- MySQL GUI v6.55
MySQL - 4.0.24_Debian-10sarge2-log : Database - db1
*********************************************************************
*/
/*!40101 SET NAMES latin2 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db1`;
USE `db1`;
/*Table structure for table `cat_2` */
DROP TABLE IF EXISTS `cat_2`;
CREATE TABLE `cat_2` (
`cat_id` int(10) unsigned NOT NULL auto_increment,
`cat_name` varchar(30) default NULL,
PRIMARY KEY (`cat_id`)
) TYPE=MyISAM;
/*Data for the table `cat_2` */
insert into `cat_2`(`cat_id`,`cat_name`) values (8,'Uk?ad okresowy'),(6,'Budowa atomu'),(3,'Substancje chemiczne'),(7,'mol'),(5,'Wi?zania chemiczne'),(9,'Chemia nieorganiczna'),(10,'Pierwiastki'),(11,'Podstawy chemii'),(12,'Reakcje redoks'),(13,'Elektrochemia');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
SQLyog Community Edition- MySQL GUI v6.55
MySQL - 4.0.24_Debian-10sarge2-log : Database - db1
*********************************************************************
*/
/*!40101 SET NAMES latin2 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db1`;
USE `db1`;
/*Table structure for table `cat_2` */
DROP TABLE IF EXISTS `cat_2`;
CREATE TABLE `cat_2` (
`cat_id` int(10) unsigned NOT NULL auto_increment,
`cat_name` varchar(30) default NULL,
PRIMARY KEY (`cat_id`)
) TYPE=MyISAM;
/*Data for the table `cat_2` */
insert into `cat_2`(`cat_id`,`cat_name`) values (8,'Uk?ad okresowy'),(6,'Budowa atomu'),(3,'Substancje chemiczne'),(7,'mol'),(5,'Wi?zania chemiczne'),(9,'Chemia nieorganiczna'),(10,'Pierwiastki'),(11,'Podstawy chemii'),(12,'Reakcje redoks'),(13,'Elektrochemia');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
3) Execute on both hosts SHOW VARIABLES LIKE '%character%'; and paste the results
SOURCE: 5.0.51-2
character_set_client utf8
character_set_connection utf8
character_set_database latin2
character_set_filesystem binary
character_set_results utf8
character_set_server latin2
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
TARGET: 4.0.24
character_set latin2
character_sets latin1 big5 cp1251 cp1257 croat czech danish dec8 dos estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru koi8_ukr latin1_de latin2 latin5 sjis swe7 tis620 ujis usa7 win1250 win1251ukr win1251
convert_character_set
There are these two words that lose latin2 chars: 'Uk?ad okresowy' and 'Wi?zania chemiczne'.
ADDED:
ps. when trying to restore a table from sql dump, the target server (4.0.24) throws an error in sql syntax - it doesn't allow for DEFAULT CHARSET=latin2 in create table. It also doesn't understand 'ALTER TABLE cat_2 CHARACTER SET latin2 COLLATE latin2_general_ci' when ther's COLLATE .. section;
Your observation is correct. After copy:
4.1 (source):
CREATE TABLE `cat_2` (
`cat_id` int(10) unsigned NOT NULL auto_increment,
`cat_name` varchar(30) default NULL,
PRIMARY KEY (`cat_id`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin2;
becomes
4.0 (target):
CREATE TABLE `cat_2` (
`cat_id` int(10) unsigned NOT NULL auto_increment,
`cat_name` varchar(30) default NULL,
PRIMARY KEY (`cat_id`)
) TYPE=MyISAM;
This is the same in 6.56 (I just tried). MySQL 4.0 does not support table level charset specification (you may specify it but it is ignored). In this version charset is specified server level.
But in your case you have "character_set latin2" in 4.0 server configuration, so it should really work!
And actually I think it DOES work, but data just does not *display* in SQLyog. To display latin2 data from MySQL 4.0, your PC must be running a LOCALE that matches latin2 (Polish, Chech etc.). We cannaot help that becasue there is not way to have MySQL 4.0 re- encode data ent to the client (no SET NAMES statement).
What is the locale setting of your PC?
workarounds:
1)
You may try 5.32 - this one hs an option to specify the charset for connection - try to connect with this one and select 'latin2' in the dropdown.
5.32 download:
http://webyog.com/downloads/oldies/SQLyog532Ent.exe
2)
In Windows 'regional settings' change the LOCALE (you will NOT need to change keyboard!)
4.1 (source):
CREATE TABLE `cat_2` (
`cat_id` int(10) unsigned NOT NULL auto_increment,
`cat_name` varchar(30) default NULL,
PRIMARY KEY (`cat_id`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin2;
becomes
4.0 (target):
CREATE TABLE `cat_2` (
`cat_id` int(10) unsigned NOT NULL auto_increment,
`cat_name` varchar(30) default NULL,
PRIMARY KEY (`cat_id`)
) TYPE=MyISAM;
This is the same in 6.56 (I just tried). MySQL 4.0 does not support table level charset specification (you may specify it but it is ignored). In this version charset is specified server level.
But in your case you have "character_set latin2" in 4.0 server configuration, so it should really work!
And actually I think it DOES work, but data just does not *display* in SQLyog. To display latin2 data from MySQL 4.0, your PC must be running a LOCALE that matches latin2 (Polish, Chech etc.). We cannaot help that becasue there is not way to have MySQL 4.0 re- encode data ent to the client (no SET NAMES statement).
What is the locale setting of your PC?
workarounds:
1)
You may try 5.32 - this one hs an option to specify the charset for connection - try to connect with this one and select 'latin2' in the dropdown.
5.32 download:
http://webyog.com/downloads/oldies/SQLyog532Ent.exe
2)
In Windows 'regional settings' change the LOCALE (you will NOT need to change keyboard!)
QUOTE (peterlaursen @ May 24 2008, 08:46 AM)
But in your case you have "character_set latin2" in 4.0 server configuration, so it should really work!
And actually I think it DOES work, but data just does not *display* in SQLyog. To display latin2 data from MySQL 4.0, your PC must be running a LOCALE that matches latin2 (Polish, Chech etc.). We cannaot help that becasue there is not way to have MySQL 4.0 re- encode data ent to the client (no SET NAMES statement).
And actually I think it DOES work, but data just does not *display* in SQLyog. To display latin2 data from MySQL 4.0, your PC must be running a LOCALE that matches latin2 (Polish, Chech etc.). We cannaot help that becasue there is not way to have MySQL 4.0 re- encode data ent to the client (no SET NAMES statement).
Yes, this is I what thought. Because the default codepage in my pc is CP-1250, this should be just the problem of display, but actually the data stays like that. When I e.g. export to html, set the cp to iso-8859-2 (utf8, cp1250 too), Polish chars are still unreadable. And you're right as to the logic of transfer. I mean I've also concluded that if the target server is set to latin2(iso-8859-2) then it should accept so-coded chars with no problem.
QUOTE
What is the locale setting of your PC?
It is Polish and in advanced setting Polish for apps that do not recognize Unicode.
QUOTE
workarounds:
1)
You may try 5.32 - this one hs an option to specify the charset for connection - try to connect with this one and select 'latin2' in the dropdown.
5.32 download:
http://webyog.com/downloads/oldies/SQLyog532Ent.exe
2)
In Windows 'regional settings' change the LOCALE (you will NOT need to change keyboard!)
1)
You may try 5.32 - this one hs an option to specify the charset for connection - try to connect with this one and select 'latin2' in the dropdown.
5.32 download:
http://webyog.com/downloads/oldies/SQLyog532Ent.exe
2)
In Windows 'regional settings' change the LOCALE (you will NOT need to change keyboard!)
If I understand correctly... You mean that changing system display to latin2 while data in mysql is coded in latin2 should make it display correctly?
But then all the other win applications that display chars in standard cp1250 will now display messy...? If my logic is right.
I think the best option will be to migrate my target base to a newer version of mysql, just like the source one.
I have such an option with my hosting company. The problems will be gone:) I wanted however to understand the above problem for
experience and potential future issues when designing pages.
"You mean that changing system display to latin2 while data in mysql is coded in latin2 should make it display correctly?"
Yes that was what I meant. For non-ASCHII ANSI characters the system can only display with the current codepage setting of the system. If for instance the letter Polish letter "ł" in latin2 is mapped to a byte-value that is not used by your current LOCALE setting a 'placeholder' (a question mark, and empty box etc. depending on the software) will display.
That would explain why it idoes not display in the SQlyog GRID. But I am somewhat surprised that it does not in the dump! But I actually asked you to zip and attach the dumps so that I will get a binary copy (when a browser is involved other encoding issues may occur and also when copying from an editor where a 'placeholder' is displayed I am not sure actually what will come out of it
Do you still have the *FILES* that you exported? If so try open in Notepad 'save as UTF8 and open again. Does it now display?
But with servers from 4.1 SQLyog will execute SET NAMES UTF8 when connecting. No matter how data arestored on the server, the will re-encode to UT8 before sending to SQLyog (and re-encode from UTF8 to *whatever specified for storage before saving). Also dumps will be utf8-encoded and marked with utf8 BOMs (Byte Order Marks). Working across pre-4.1 and post 4.1 servers is somewhat a pain!
You can install more server on your local (using different ports and service names) so that you can test this without changing anything at your hosting.
I will need to configure a 4.0 server for latin2 to go deeper in detail analysing this (yes working with encodings IS a 'brain-tweaker'!).
Yes that was what I meant. For non-ASCHII ANSI characters the system can only display with the current codepage setting of the system. If for instance the letter Polish letter "ł" in latin2 is mapped to a byte-value that is not used by your current LOCALE setting a 'placeholder' (a question mark, and empty box etc. depending on the software) will display.
That would explain why it idoes not display in the SQlyog GRID. But I am somewhat surprised that it does not in the dump! But I actually asked you to zip and attach the dumps so that I will get a binary copy (when a browser is involved other encoding issues may occur and also when copying from an editor where a 'placeholder' is displayed I am not sure actually what will come out of it
Do you still have the *FILES* that you exported? If so try open in Notepad 'save as UTF8 and open again. Does it now display?
But with servers from 4.1 SQLyog will execute SET NAMES UTF8 when connecting. No matter how data arestored on the server, the will re-encode to UT8 before sending to SQLyog (and re-encode from UTF8 to *whatever specified for storage before saving). Also dumps will be utf8-encoded and marked with utf8 BOMs (Byte Order Marks). Working across pre-4.1 and post 4.1 servers is somewhat a pain!
You can install more server on your local (using different ports and service names) so that you can test this without changing anything at your hosting.
I will need to configure a 4.0 server for latin2 to go deeper in detail analysing this (yes working with encodings IS a 'brain-tweaker'!).
"Do you still have the *FILES* that you exported? If so try open in Notepad 'save as UTF8 and open again. Does it now display?"
OK, I'm attaching a zip with both files:
pre_copy.sql (dumped from source 5.xx)
post_copy.sql (dumped from target 4.xx)
Before dumping I added two rows of strings with arbitrary Polish chars for clearer results.
I opened in Notepad++ and saved in utf8 with bom but no change. Polish chars in post_copy.sql are questionmarks (or placeholders as I know now:)
"But with servers from 4.1 SQLyog will execute SET NAMES UTF8 when connecting. No matter how data arestored on the server, the will re-encode to UT8 before sending to SQLyog (and re-encode from UTF8 to *whatever specified for storage before saving). Also dumps will be utf8-encoded and marked with utf8 BOMs (Byte Order Marks). Working across pre-4.1 and post 4.1 servers is somewhat a pain!"
Yes, you're right. I've just discovered that reading pre_copy.sql dumped from 5.xx is a total mess in Totalcmd lister. Only Notepad++ displays correctly.
It reads utf8, as I understand that.
"You can install more server on your local (using different ports and service names) so that you can test this without changing anything at your hosting."
I'm afraid I've had enough headache with that already...
"I will need to configure a 4.0 server for latin2 to go deeper in detail analysing this (yes working with encodings IS a 'brain-tweaker'!)."
I wouldn't like to feel responsible for health losses:)
OK, I'm attaching a zip with both files:
pre_copy.sql (dumped from source 5.xx)
post_copy.sql (dumped from target 4.xx)
Before dumping I added two rows of strings with arbitrary Polish chars for clearer results.
I opened in Notepad++ and saved in utf8 with bom but no change. Polish chars in post_copy.sql are questionmarks (or placeholders as I know now:)
"But with servers from 4.1 SQLyog will execute SET NAMES UTF8 when connecting. No matter how data arestored on the server, the will re-encode to UT8 before sending to SQLyog (and re-encode from UTF8 to *whatever specified for storage before saving). Also dumps will be utf8-encoded and marked with utf8 BOMs (Byte Order Marks). Working across pre-4.1 and post 4.1 servers is somewhat a pain!"
Yes, you're right. I've just discovered that reading pre_copy.sql dumped from 5.xx is a total mess in Totalcmd lister. Only Notepad++ displays correctly.
It reads utf8, as I understand that.
"You can install more server on your local (using different ports and service names) so that you can test this without changing anything at your hosting."
I'm afraid I've had enough headache with that already...
"I will need to configure a 4.0 server for latin2 to go deeper in detail analysing this (yes working with encodings IS a 'brain-tweaker'!)."
I wouldn't like to feel responsible for health losses:)
Don't worry .. my brain is totally screwed up already .. after all I have been here for around 3 years! 
I did the experiment.
1) reconfigured MySQL 4.0 to latin2 (by inserting "character-set = latin2" in configuration and restarting MySQL)
2) changed the non-unicode default for my system to Polish (see how to do in 1st image)
3) imported your 'pre_' dump to MySQL 5 (4.1 or 5.0 does not matter in this respect).
4) copied the table to MySQL 4.0 with SQLyog
5) See in 2nd image that data display correct in the gird
6) I dumped that table. Zipped dump attached. Opened the dump in Notepad. As long as non-unicode default for system is Polish also the Dump is OK. It is ANSI encoded (it need to be if it shall be possible to import to 4.0 again!). See 3rd image
7) Restarted system with non-unicode default for my system as Danish (note MySQL 4.0 still uses latin2). When the same dump is now opened non-ASCII characters are garbled just as they are in the SQLyog GRID. See 4th and 5th image. Western mapping is displayed for every byte.
Click to view attachment
Click to view attachment
Click to view attachment
Click to view attachment
Click to view attachment
Click to view attachment
So it is basically true what I told in the first place. If server version is 4.0 or lower and if the encoding used for data storage differs from the Windows ANSI codepage defined for non-unicode, then SQLyog cannot handle it. The only point that I was missing was that when the '?' character is inserted in a dump as a placeholder for a non-valid (with the defined LOCALE) character it is not possible to repair that later - the '?' has been inserted and that one is a valid character. Re-encoding will still preserve the '?'!
Solutions and Workarounds:
1) Use MySQL servers only that have compatible charset handling and support SET NAMES (>= 4.1)
2) And/or use Polish as system setting for non-unicode when working with those data
3) With SQLyog 5.32 it is possible to copy such data even with mismatching MySQL charset and system LOCALE if you specify 'latin2' as the charset for connection to MySQL 4.0. But they still will not display correctly. So this is an 'ugly' workaround!
I did the experiment.
1) reconfigured MySQL 4.0 to latin2 (by inserting "character-set = latin2" in configuration and restarting MySQL)
2) changed the non-unicode default for my system to Polish (see how to do in 1st image)
3) imported your 'pre_' dump to MySQL 5 (4.1 or 5.0 does not matter in this respect).
4) copied the table to MySQL 4.0 with SQLyog
5) See in 2nd image that data display correct in the gird
6) I dumped that table. Zipped dump attached. Opened the dump in Notepad. As long as non-unicode default for system is Polish also the Dump is OK. It is ANSI encoded (it need to be if it shall be possible to import to 4.0 again!). See 3rd image
7) Restarted system with non-unicode default for my system as Danish (note MySQL 4.0 still uses latin2). When the same dump is now opened non-ASCII characters are garbled just as they are in the SQLyog GRID. See 4th and 5th image. Western mapping is displayed for every byte.
Click to view attachment
Click to view attachment
Click to view attachment
Click to view attachment
Click to view attachment
Click to view attachment
So it is basically true what I told in the first place. If server version is 4.0 or lower and if the encoding used for data storage differs from the Windows ANSI codepage defined for non-unicode, then SQLyog cannot handle it. The only point that I was missing was that when the '?' character is inserted in a dump as a placeholder for a non-valid (with the defined LOCALE) character it is not possible to repair that later - the '?' has been inserted and that one is a valid character. Re-encoding will still preserve the '?'!
Solutions and Workarounds:
1) Use MySQL servers only that have compatible charset handling and support SET NAMES (>= 4.1)
2) And/or use Polish as system setting for non-unicode when working with those data
3) With SQLyog 5.32 it is possible to copy such data even with mismatching MySQL charset and system LOCALE if you specify 'latin2' as the charset for connection to MySQL 4.0. But they still will not display correctly. So this is an 'ugly' workaround!
So you made it.., my respect:)
There's one thing that still baffles me though. You set the codepage to polish (for non-unicode) - so that's what I did too. Then it should work with me too whereas it doesn't... Or maybe the codepage for non-unicode in your case was set to latin2, not cp1250? (I'm using Xp, have to check on my laptop - there I've got Vista)
Step5 (2nd img) is where our ways totally diverge. Or I just got even more confused?
QUOTE (peterlaursen @ May 24 2008, 01:04 PM)
So it is basically true what I told in the first place. If server version is 4.0 or lower and if the encoding used for data storage differs from the Windows ANSI codepage defined for non-unicode, then SQLyog cannot handle it.
There's one thing that still baffles me though. You set the codepage to polish (for non-unicode) - so that's what I did too. Then it should work with me too whereas it doesn't... Or maybe the codepage for non-unicode in your case was set to latin2, not cp1250? (I'm using Xp, have to check on my laptop - there I've got Vista)
Step5 (2nd img) is where our ways totally diverge. Or I just got even more confused?
Don't worry .. I did the almost same exercise with Thai (tis60 charset) 1-2 years ago. That was more tricky as it was rather complicated to find out where one character started and another ended! I also know a little Russian and Czech and have been visiting Poland a few times with a friend who speaks Polish so I can handle the basics of slav languages (know the special characters of most of them)!
(I only do not understand when there has been some disaster in Poland and some town was badly damaged, why newspapers always tell the name after the disaster and not the one it had before it happened ...)
:-)
Jokes apart:
I just selected 'Polish' as 'default setting for non-unicode' in the Windows 'regional settings' menu. The term 'latin2' applies to MySQL charsets not to ANSI codepages.
But now see here: I have 'Danish' regional settings and MySQL 4.0 running with 'latin2'. I copy data from MySQL 5.0 to 4.0.
Click to view attachment
In this situation (copy) Polish 'accents' are simply stripped. This is not the same as what happens when dumping! And that I am not able fully to explain.
SQLyog uses UTF8 internally (C/C++ data type 'multibyte') for character data. Binary data are C/C++ datatype 'char'. Single byte character data from MySQL 4.0 arrive as 'char' too but are converted to 'multibyte'. Further: whenever we need to print to Windows GUI (like to the grid) we need to convert to UTF16 (C/C++ data type 'widechar') before passing to Windows.
All those conversions are handled with some functions that I think (but not sure) are part of the Win32 API. I do not know all those internals. But I also think that *when it is not working* it does not really matter what will exactly happen.
(I only do not understand when there has been some disaster in Poland and some town was badly damaged, why newspapers always tell the name after the disaster and not the one it had before it happened ...)
:-) Jokes apart:
I just selected 'Polish' as 'default setting for non-unicode' in the Windows 'regional settings' menu. The term 'latin2' applies to MySQL charsets not to ANSI codepages.
But now see here: I have 'Danish' regional settings and MySQL 4.0 running with 'latin2'. I copy data from MySQL 5.0 to 4.0.
Click to view attachment
In this situation (copy) Polish 'accents' are simply stripped. This is not the same as what happens when dumping! And that I am not able fully to explain.
SQLyog uses UTF8 internally (C/C++ data type 'multibyte') for character data. Binary data are C/C++ datatype 'char'. Single byte character data from MySQL 4.0 arrive as 'char' too but are converted to 'multibyte'. Further: whenever we need to print to Windows GUI (like to the grid) we need to convert to UTF16 (C/C++ data type 'widechar') before passing to Windows.
All those conversions are handled with some functions that I think (but not sure) are part of the Win32 API. I do not know all those internals. But I also think that *when it is not working* it does not really matter what will exactly happen.
QUOTE
Step5 (2nd img) is where our ways totally diverge. Or I just got even more confused?
I cannot explain! Please verify what you did! I think you were prompted to restart Windows?
Anyway my Thai experience was on XP - I think XP and Vista are functioning 100% the same in this respect!
"(I only do not understand when there has been some disaster in Poland and some town was badly damaged, why newspapers always tell the name after the disaster and not the one it had before it happened ...) rolleyes.gif:-) "
That joke I didn't know... But to be serious, even for Poles some Polish words are tongue twisters.
"I cannot explain! Please verify what you did! I think you were prompted to restart Windows?"
No, I didn't have to restart since that is my default setting.
I had problems with illustrator once and Adobe told me to switch to english for the time being.
Having Pl win and EN illustrator caused some pretty bizzare hangups. And then I had messy chars in all the other apps like Outlook Expr.
Switching to illustrator CE cured the mess.
As to SQLyog, I'm happy it finally works and it's good to know there are people willing to help:)
Thank you
Joey
That joke I didn't know...
But to be serious, even for Poles some Polish words are tongue twisters."I cannot explain! Please verify what you did! I think you were prompted to restart Windows?"
No, I didn't have to restart since that is my default setting.
I had problems with illustrator once and Adobe told me to switch to english for the time being.
Having Pl win and EN illustrator caused some pretty bizzare hangups. And then I had messy chars in all the other apps like Outlook Expr.
Switching to illustrator CE cured the mess.
As to SQLyog, I'm happy it finally works and it's good to know there are people willing to help:)
Thank you
Joey
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.