Bug: Structure Synchr. Disregards Case

[motin]

Just found that the two fields memberID and MemberID is considered the same in the structure synchronization.

This opens up for problems, as mysql run on linux is case sensitive, or at least was back in mysql 3.x

I am running WinXP Pro btw.

[peterlaursen]

Confirmed. I tested on Linux too.

There also are problems with datasynch

I created these two tables on my Webhost

Quote:

CREATE TABLE `Base1`.`tablename1` (               

              `ID` bigint(20) NOT NULL auto_increment, 

              `t` bigint(20) NOT NULL default '0',     

              `u` varchar(20) default NULL,           

              PRIMARY KEY  (`ID`,`t`)                 

            ) ;

CREATE TABLE `Base2`.`TableName1` (               

              `ID` bigint(20) NOT NULL auto_increment, 

              `t` bigint(20) NOT NULL default '0',     

              `u` varchar(20) default NULL,           

              PRIMARY KEY  (`ID`,`t`)                 

            ) ;

and ran the datasync. I offfered me to sync the two tables (which it should not on Linux!). Howewer it failed. See picture.

Next I crated tablename1 and TableName1 in both databases. SQLyog displayed an error “Table allready exists” when the second was created. Nevertheless they were all created. Also see picture.

Next I created tablename1 like

Quote:

CREATE TABLE `tablename1` (               

              `ID` bigint(20) NOT NULL auto_increment, 

              `t` bigint(20) NOT NULL default '0',     

              `u` varchar(20) default NULL,           

              PRIMARY KEY  (`ID`,`t`)                 

            ) ;

in one database and

Quote:

CREATE TABLE `tablename1` (               

              `ID` bigint(20) NOT NULL auto_increment, 

              `T` bigint(20) NOT NULL default '0',     

              `U` varchar(20) default NULL,           

              PRIMARY KEY  (`ID`,`t`)                 

            ) ;

in another and ran datasync. The columns t and T were synced as were U and u. The should not!

Either case-sensivity should be handled automatically by the program, or it should be an option to choose by user. It is a little tricky when sync'ing two DB's on WIN and NIX respectively … For instance: What to do if there is tablename1 on WIN and both TableName1 and tablename1 on NIX ??

Maybe those issues would solve themselves more or less with SQLyog 4.3 that would compile with a Unicode codebase?? As long as SQLyog uses ANSI internally there is no chance to fix it I guess! At least not worth spending time with in the 4.2 tree! But should have attention with 4.3.

[Ritesh]

Quote:

Maybe those issues would solve themselves more or less with SQLyog 4.3 that would compile with a Unicode codebase?? As long as SQLyog uses ANSI internally there is no chance to fix it I guess! At least not worth spending time with in the 4.2 tree! But should have attention

This is not a UNICODE issue but rather a bug with SQLyog. We never considered the issues relating to case-sensitivity between Linux and Windows boxes.

I will try to setup a MySQL server on my Linux box and work on all the cases. Should not take us more then 1 day to fix all the crash issues.

Quote:

Just found that the two fields memberID and MemberID is considered the same in the structure synchronization.

I have added this in the TO-DO list but I cannot guarantee the version which will have the fix.

[Ritesh]

More food for thought:

http://dev.mysql.com/doc/refman/5.0/en/nam…ensitivity.html

If you read the docs, case sensitivity applies only for database and table names and not for column names.

Quote:

The columns t and T were synced as were U and u. The should not!

Its correct as columns are not case-sensitive.

[peterlaursen]

But still DB1.TableName1 and DB2.tablename1 (both on my Linux webhost) were synced.

[Ritesh]

This is very strange. When we tried the same in our Linux box, the process stopped after giving out the error:

Table does not exists

[peterlaursen]

@Ritesh

I don't understand your point!

Does that mean that you don't finde any error or found a new one ??

Actually I just got Linux installed locally and have MySQL running with it.

So I could test somewhat more. But what to test ?

[peterlaursen]

See atached.

The databases are on SUSE linux 10 with MySQL 5.0.13 on one conputer on my network. Obviously SQLyog (form a windows computer on the network) offers to sync. However the databases are not sync'ed – indsted the err msg “Could not get FIELD information for '`TableName1`' in the TARGET database” . Obviously because there is not `TableName1` but a `tablename1`.

On my webhost I experienced in a similar situation that databases were sync'ed. And you have a third thing happening ?

[peterlaursen]

With tunnelling SJA crashes with a similar setup. See pic.

Tested on my webhost a new local Linux installation.

(yeah I managed to rudimentarily set up Apache + php in 10 minutes 😀 )

[peterlaursen]

Some more research:

Now that I got a local LINUX working I am able to research some more into issues like this one. I am also able to run SJA for LINUX.

I created a simple test database with only one table identically on a Windows host and a LINUX (SUSE 10) host. MySQL is 5.0.13 on both

Code:

CREATE TABLE `TableName1` (
 `id` bigint(20) NOT NULL auto_increment,
 `t` varchar(40) default NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `tablename1` */

insert into `TableName1` values (1,'a');
insert into `TableName1` values (2,'b');
insert into `TableName1` values (3,'c');
insert into `TableName1` values (4,'d');

observations:

1) I changed data a little bit in both. DATA sync was perfect with SJA for LINUX as well as SJA for Windows.

2) Now changed tablenames to `TableName1` on both hosts. It results in errors

“Error No. 1050 Table 'tablename1' already exists” or

“Could not get FIELD information for '`tablename1`' in the SOURCE database”

I believe that the first is a MySQL error and the latter an internal SqLyog error ?

3) Next I renamed both tables to `tablename1` and renamed column`t` to `T` (with ALTER TABLE) on the windows host . A sync with SJA for LINUX returns the error “Column NAME mismatch for '`tablename1`”. An internal SQLyog error, I think? A sync with SJA from Windows runs successfylly.

4) Now reversed things. Column name is `t` on windows and `T`on LINUX (on LINUX SQLyog displays an erroneous error message when alter'ing table, but that is cosmetical!). Same result as with 3) – runs on Windows – error on LINUX.

5) It is correct that the STRUCT-SYNC tool completely disregards CASES for TABLE names and and COLUMN names.

6) Now I created an empty database TEST on LINUX (the `test`databae is still there). The SQLyog GUI cannot distinguish between `test` and `TEST` – lots of GUI errors occur. For instance when trying to copy `test` to `TEST`, `TEST` does not appear on the list of all. And when opening a new connection, SQLyog is fooled to believe that `tablename1` exists in both `test` and `TEST`, but when trying to open `TEST`,`tablename1` a MySQL error is this played (of course). “Cannot fetch ….”)

7) The struct sync tool now only displays `TEST` not `test` on the LINUX host.

[Ritesh]

peterlaursen wrote on Oct 21 2005, 07:15 AM:

See atached.

The databases are on SUSE linux 10 with MySQL 5.0.13 on one conputer on my network.  Obviously SQLyog (form a windows computer on the network) offers to sync.  However the databases are not sync'ed – indsted the err msg “Could not get FIELD information for '`TableName1`' in the TARGET database” .  Obviously because there is not `TableName1` but a `tablename1`.

[post=”7666″]<{POST_SNAPBACK}>[/post]

This is correct. Its an internal SJA error which means a particular column information for a table was not found in the TARGET server.

peterlaursen wrote on Oct 21 2005, 08:01 AM:

With tunnelling SJA crashes with a similar setup.  See pic.

Tested on my webhost a new local Linux installation.

(yeah I managed to rudimentarily set up Apache + php in 10 minutes  😀 )

[post=”7667″]<{POST_SNAPBACK}>[/post]

Bug confirmed and has already been fixed in BETA 6 development tree. We were able to reproduce it yesterday on our Linux box 🙂

peterlaursen wrote on Oct 22 2005, 07:48 PM:

Some more research:

Now that I got a local LINUX working I am able to research some more into issues like this one.  I am also able to run SJA for LINUX.

1) I changed data a little bit in both.  DATA sync was perfect with SJA for LINUX as well as SJA for Windows.

Correct. SJA is not effected by change in case of data. For SJA, A and a in data is different.

peterlaursen wrote on Oct 22 2005, 07:48 PM:

2) Now changed tablenames to `TableName1` on both hosts. It results in errors

“Error No. 1050 Table 'tablename1' already exists” or

“Could not get FIELD information for '`tablename1`' in the SOURCE database”

I believe that the first is a MySQL error and the latter an internal SqLyog error ?

Correct. The reason for the above is error is that Tablename1 which existed in source does not exist in target.

peterlaursen wrote on Oct 22 2005, 07:48 PM:

3) Next I renamed both tables to `tablename1` and renamed column`t` to `T` (with ALTER TABLE) on the windows host .  A sync with SJA for LINUX returns the error “Column NAME mismatch for '`tablename1`”.  An internal SQLyog error, I think? A sync with SJA from Windows runs successfylly.

SJA for Windows is case insensitive but SJA for Linux is case sensitive and thus the difference.

peterlaursen wrote on Oct 22 2005, 07:48 PM:

5) It is correct that the STRUCT-SYNC tool completely disregards CASES for TABLE names and and COLUMN names.

We plan to fix this issue in one of the future versions of SQLyog.

peterlaursen wrote on Oct 22 2005, 07:48 PM:

6) Now I created an empty database TEST on LINUX (the `test`databae is still there).  The SQLyog GUI cannot distinguish between `test` and `TEST` – lots of GUI errors occur.  For instance when trying to copy  `test` to `TEST`,  `TEST` does not appear on the list of all.  And when opening a new connection, SQLyog is fooled to believe that `tablename1`  exists in both  `test` and `TEST`, but when trying to open `TEST`,`tablename1`  a MySQL error is this played (of course).  “Cannot fetch ….”)

7) The struct sync tool now only displays `TEST` not `test` on the LINUX host.

Since SQLyog does not consider case for db/table/column names, there are some issues related to it. I have added it in the TO-DO list.

[peterlaursen]

@ ritesh

you write

Quote:

SJA for Windows is case insensitive but SJA for Linux is case sensitive and thus the difference.

Well yes I can see that! Obviousjt! But should it be like that you think? I think that all programs and OS-versions should be able to run in 'case-sensitive' or 'case-insensitive' mode. Simply because it is the 'case mode' of the server(s) that should decide. To me it is obvious that this is a rule a client must obey.

The triggy things happen when you are working with two connections of two different OS's that have different 'case-mode' – not to speak of the situation (that admittedly is mostly hypothetical!) where two or more DB's or tables on *nix are identical in 'case-insensitive' mode – like ´text´and ´TEXT´. Further there is a tricky thing with Mac-OS: it is 'case-sensitive' or 'case-insensitive' dependent on which filesystem the system runs on.

In these cases the user must decide – a pop-up or a setting.

[Author]

Posts