MySQL Bugs: #34821: Synchronize fails on Windows on case-sensitive tables

Bug #34821	Synchronize fails on Windows on case-sensitive tables
Submitted:	25 Feb 2008 21:10	Modified:	15 Oct 2009 14:35
Reporter:	John Owen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench	Severity:	S4 (Feature request)
Version:	5.0.14 SE	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	case sensitive, CHECKED, synchronize

Description:
I'm working on a Windows Machine, using Wamp 2 (MySQL 5.0.45).
My database model has case insensitive letters (ThisIsATable), but on database it's all lowercase (thisisatable).
When i try to synchronize it, it says there's no such table.
In fact, it tries to delete my exampledatabase and create it's new one ExampleDatabase.

How to repeat:
Create a new Database with lower and uppercase letters (maybe uppercase it's enought), i mean, give a schema name like "ExampleDatabase".
Now create a table like ExampleTable.

Synchronize with a Windows MySQL Database.

Now Synchronize again and you will found it does not recognize the old synchronize.

Suggested fix:
a)
Allow case insensitive synchronize

b)
Allow mannually make match of Model/Target database and tables
I mean, you can choise this table on model is this table on target, and so on.

I think this might be an issue with the server and not with WB!

refer to: http://bugs.mysql.com/bug.php?id=20356

or rather: you should probably set lower_case_table_names = 2 if you want to use 'mixed case' in your model.  But be prepared that that will raise other problems because lower_case_table_names = 2 has never worked properly in 5.x (and has taken a horrible time to get attention to this - but now at least a developer has been assigned with it).

Anyway, if is a known issue that MySQL has an case-problem on Windows, i think Workbench should manage it, or it will just be ignored and make it and make it a user-problem? (so, users must try to solve it someway-workbench-dont-care?)

I thougth it should be a problem becouse windows is case-insensitive, that's why i propose that two ways to manage it, ignore case or give users a way to solve it.

If 'lower_case_tables_names = 2' in configuration then Workbench should handle mixed-case table names corectly - also on Windows.

For use on Windows it will have to validate that not both 'MyTable' and 'mytable' exists!  And probably the model itself will need to have information about lower_case_tables_names setting!

But that actually raises a discussion of portability of models across platforms!

That's why information about case should be in the program itself and not in each model, becouse it's something about been on windows platform

I dont understand last point.

I think (on the opposite?) that you whould be able to use WB on one platfom to generate schema's that will be used on another platform!

I only see the option that each model is 'lcoked' to a certain lower_case setting 8with an convert option posssibly)

I agree that entire model should be platform independent, that's why i propose that windows version of Workbench allow to choose which table should match with otherone, or just ignore case (just windows Workbench, so the model don't know anything about windows case-problem).

Anyway should not happen that it's imposible to synchronizes with an identical database, losing all data on database (becouse case differences will make to delete database model first). 
In fact, it's imposible to synchronizes 2 times with the same windows database without losing data.

Thank you for the bug report.

The issue is a know MySQL bug for Windows MySQL installs.  If you browse through your information_schema (and depending on what version of WAMP, XAMP or standalone MySQL installed), you'll see that tables like "REFERENTIAL_CONSTRAINTS" and "KEY_COLUMN_USAGE" have the column "REFERENCED_TABLE_NAME" always in lowercase regardless of your MySQL ini setting "lower_case_table_names".  

If I remember correctly, this has to do with an issue on the way Windows stores the file names compared to Unix.  If you look at your DB files, you'll see that ".frm" files, case wise, are ok but if you have a trigger, the ".TRG" files get saved in all lowercase which will then cause the synchronization issue.    

I wouldn't expect WB to code a work-around for this but it would be helpful as developing locally is so much quicker.

Many thanks for writing a bug report.

This is not a MySQL bug. This is a problem of your file system.

Windows is case insensitive and Linux is case sensitive.

Additionally, the SQL Standard says, that the identifiers all should be upper case. We didn't implement it yet but when we ever will change a behaviour here then we will get more standard conform.