Syncronization Case Sensitivity

[jrossiter]

I've used SqlYog for quite a while now, with a couple different employers, and this is an issue that still hasn't been resolved.

If I have a database that has the “same” tablename in two different cases, Structure Sync fails horribly.

For example:

Code:

CREATE DATABASE testDB1;
CREATE TABLE `testDB1`.`testTable1` (                    
             `primField` int(11) NOT NULL auto_increment,  
             `fieldOne` int(11) NOT NULL,                  
             `fieldTwo` text NOT NULL,                  
             PRIMARY KEY  (`primField`)                    
           );

CREATE DATABASE testDB2;
CREATE TABLE `testDB2`.`testtable1` (                    
             `primField` int(11) NOT NULL auto_increment,  
             `fieldThree` int(11) NOT NULL,                  
             `fieldFour` text NOT NULL,                  
             PRIMARY KEY  (`primField`)                    
           );

Trying to run the struction syncronization against these two tables (on the same UNIX host) returns a fatal (for the sync) error dialog of:

Quote:

Error No. 1146

Table 'testDB2.testTable1' doesn't exist

The reason this occurs is that the tables are being compared case insensitively and SqlYog thinks that they're the same table.

I've seen this reported other places in the forum over a year ago, but it still hasn't been addressed. What's the holdup? This is a major functional issue – MySQL is case sensitive, SqlYog is not.

[peterlaursen]

Issue confirmed (with 5.0x and direct connections from Windows client to Linux server).

Also DATA SYNC is affected

Quote:

Sync started at Mon Feb 20 13:05:03 2006

Table                      SrcRows  TgtRows  Inserted  Updated  Deleted

=========================  =======  =======  ========  =======  =======

Could not get FIELD information for '`testTable1`' in the TARGET database

But this

Quote:

MySQL is case sensitive, SqlYog is not

is somewhat an exageration – not true everywhere in the program. Attached picture show the CREATE TABLE can handle 'cases'. And common SQL processing from the GUI can. Also 'rename table' handles it. So it could be a workaround to rename one of the tables before running the STRUCTURE SYNC. 'rename table' is fast as it does not involve copying of data.

But all 'powertools' in particular should be checked! A trivial thing as a wrong operator for compare may cause this!

But when I wrote this: http://webyog.com/faq/index.php?action=art…ase%20sensitive is thought it was fixed!

Quote:

This is a major functional issue

aahhh .. MAJOR ??? excuse me major ??? 😀

It is just a silly bug!

EDIT: I replaced the image – first one was wrong.

[Ritesh]

Structure Sync is case insensitive.

I have to put this for v5.1. We will fix this along with COLUMN ORDER issue in structure sync.

[peterlaursen]

@ritesh

DATA SYNC also has problems. With my example it should not even attempt to sync! Tables are different. There are no tables to sync. But it sees them as identical. However the the server does not agree (!) and thus the error.

This is an error in SQLyog.exe. That does not exclude errors in sja.exe!

Also – if you run a MIGRATION, BACKUP, or NOTIFY job and connect to a DB with a table and a TABLE – are you sure that it tries to read from/write to the right table? It should be checked! Though it could very well be that it affect only tools involving two MySQL datases with the task/job.

[peterlaursen]

also:

it should not be either case-sensitive or case-insentensive – it should be both! And adjust according to the 'lower_case_table_names' settings of the server(s).

Now (an these questions apply to STRUCT SYNC and DATA SYNC as well):

If two servers have different settings – what then?

If sync'ing a LINUX server with a Windows server (with their default settings)?

I see no other option than an additional popup to ask user what to do if things are ambigious. But the SJA SCHEMA can't handle this as of now. Right? How to data sync TABLE on Windows with table on Unix?

[peterlaursen]

Quote:

If sync'ing a LINUX server with a Windows server (with their default settings)? …  But the SJA SCHEMA can't handle this as of now. Right?

Wrong actually I believe after thinking a little! If the jobfile uses the table-names of the Unix-machine, it should work!

[jrossiter]

peterlaursen wrote on Feb 20 2006, 04:25 AM:

aahhh .. MAJOR ??? excuse me  major ???  😀

It is just a silly bug!

When it comes to using structure or data sync on DBs where this exists, it's a major issue. It's a complete failure of the feature. In my world as a former QA engineer, that's known as “blocking”. It isn't as if there were a typo in the dialog or something – the feature fails and in bad cases who knows what it has the possibility of doing?

What happens if one DB has “testTable” and the other has “testtable” and “testTable” – does it sync into the wrong location and erase data? I haven't bothered to test those scenarios explicitly, but given the nature of the bug and its behavior it would be a valid assumption.

[peterlaursen]

we are a lot of 'former's 🙂

But you are missing an important point:

Case sensivity with *NIX and Non-case sensivity with Windows (and sometimes MAC, depending on the filesystem on which it is installed) are only DEFAULTs for the systems. The behaviour can be changed with the 'lower_case_table_names' system variable on all platforms.

So where appropriate SQLyog should query this system variable and adjust its CASE SENSIVITY accordingly!

[peterlaursen]

and FAQ write-up accordingly:

http://webyog.com/faq/index.php?action=art…ase%20sensitive

[peterlaursen]

to be specific:

If 'lower_case_table_names' is '1' or '2' on both servers sync should be case insensitive

If 'lower_case_table_names' is '0' on both servers sync should be case sensitive

If 'lower_case_table_names' is '0' on one server and '1' or '2' on the other sync should be case sensitive as default. However if there is no conflict a popup could display offering to do the sync anyway. And to avoid server errors the name-CASE of the table on the server where it is case sensitive should be use.

This applies to STRUCTURE SYNC and DATA SYNC as well.

Did I get it all? 😮

[Ritesh]

I think the option to do case-sensitive or case-insensitive checking should be put into preference.

The data-sync as well as structure-sync were case-sensitive in the earlier versions and due to couple of user-requests, we made it case insensitive.

[peterlaursen]

I still think my proposal is more 'strict'. Server configurations tell you whether CASE is significant.

But I have no issue with this personally! And a setting will be OK too. Now that setting should preferably be displyed by the wizards as a reminder – and maybe even be changable from the wizards.

[Ritesh]

Since the sync is mostly done between two databases/hosts, one might be running on case-sensitive option and one might be running on case-insensitive option. We are not sure which one to use then?

[peterlaursen]

I have answered this.

Quote:

If 'lower_case_table_names' is '0' on one server and '1' or '2' on the other sync should be case sensitive as default. However if there is no conflict a popup could display offering to do the sync anyway. And to avoid server errors the name-CASE of the table on the server where it is case sensitive should be use.

replace 'conflict' with 'ambiguity' for clarity.

Now figure:

You sync a LINUX with a `TABLE` and a `table` with a Windows having either. If case-insensivity is chosen in preferences and user not aware, what will happen? Windows will be sync'ed with the one first returned by SHOW TABLES on LINUX. That is a risk!

[Author]

Posts