MySQL Bugs: #47953: MySQL Workbench Database Synchronization not working ( simple synch )

Bug #47953	MySQL Workbench Database Synchronization not working ( simple synch )
Submitted:	9 Oct 2009 18:33	Modified:	25 Nov 2009 14:54
Reporter:	Shantanu Upadhyaya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.1.18, 5.2.6	OS:	Windows
Assigned to:	Alexander Musienko	CPU Architecture:	Any
Tags:	Database Synchronization, Database Synchronization not working

Description:
Prior to creating this report, I have posted this in the forum :
http://forums.mysql.com/read.php?159,285453,285453#msg-285453

This is a very simple use case. I'm trying to synchronize my model to MySQL 5.1 database. Its repeatedly failing.

When I first installed MWB, I believe it was able to successfully synchronize. After that, for 2 days I've not been able to get it working.

If you want the .mwb file, let me know and I'll send it across. 

I will try this on Snow Leopard as well.

(See Private Comments for SQL Scripts generated from MWB )

How to repeat:
1. I have 5 tables - (a) ACCOUNT A, ROLE R, ROLEMAP RM. A and R have m:n relationship. (b) 2 standalone tables LOGIN_HISTORY and REMOTE.

2. Forward engineer the model to my schema - works fine. Verified by viewing the tables in Toad Freeware.

5. Modify datatype of REMOTE table some column / add a column / add constraints.

6.Save the file

7. MWB Menu : Database --> Synchronize Model --> follow till the end - shows 'Synchronization compeleted successfully'.

( at this point the .mwb file goes into unsaved state, * appears next to filename on header of window, why is that ? just thought I should mention this.)

8. I check my table using Toad Freeware for MySQL - NO CHANGE !

MWB does not synchronizing with DB. I've tried this several time. It's as if it's picking up the old file and applying it.

Try with a single simple table
------------------------------
9. Drop all tables in the schema - via Toad.

10. In MWB model - I add a column to REMOTE. Save file.

11. Do step 7 again - but only synchronize REMOTE. Ignore the rest.

12. Goto Toad - REMOTE is present with new column.

13. Goto MWB - change REMOVE.someColumn from VARCHAR(45) to VARCHAR(100) - Save.

14. Do step 7 - I see the SQL statements do not contain any ALTER statements. It has :
<b>
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
</b>

15. Click Execute. Shows 'Synchronization Completed Successfully'

16 Goto Toad. REMOVE.someColumn IS STILL VARCHAR(45) !! Why ??

I will try this on Snow Leopard.

Forgot to ask :

When tables get created from 'Forward Engineer', why do they have lowecase names ??

Is there any update on this bug ??

Yes, bug #39060 is related to this bug here. I set bug #39060 as duplicate of this bug here.

Susan et al,
From a process perspective, what happens next ? Will a fix be available anytime soon ? Thank you.

Thank you for your bug report. This issue is already fixed in our source repository of MySQL Workbench 5.2 and will be incorporated into the next release.

Susan and others,

Thank you for the update. When will the next release come out ?
Can I get a patch applied to my installation ?

I need this to continue working effectively.

Thanks again.

Btw, I did not get time to test this on Snow Leopard as I said earlier. Is this bug also applicable on that OS ?

Hello Shantanu Upadhyaya,

Honestly, Workbench 5.2 has lots of more functionality. And our developers fixed a lots of bugs during implementing these functionalities without looking if there is an official bug report for it or not.

They just saw stuff not working during their own tests and just immediately fixed it.

This occurs a little chaos here on our bug system and so I am on the way at the moment to test all the bug reports again where we think that they are already fixed "by accident".

I am only testing with our source release and of course I am careful and just tell that it will work in next outcoming release.

In your case here I am 98 percent sure it is already working in actual release which you can download from:

http://dev.mysql.com/downloads/workbench/5.1.html#Workbench_5.2_Alpha_-_Binaries_and_Sourc...

But as I told before I didn't test it.

To answer your OS question: The source is the same for all OS which means bugs like this here usually affect all OS and also will be fixed on all OS after we fixed it.

I uninstalled the old one and installed 5.2.6 OSS Alpha as pointed out by you.
Synchronize model is NOT working.

I changed datatype in my ER diagram ( from varchar(100) to varchar(33) ).
After synchronizing to the DB, the DB table still has varchar(100).

Does this product do what it's intended to ?

Hi Shantanu,

sorry for the information about this being already fixed - there was some internal misunderstanding when communicating about that bug.

could you please provide information which DB server version you are using and which platform the server runs (especially the filesystem). This looks very much like a problem caused by a Case-Insensitive Filesystem on DB-Server-Side, as already pointed out by you in a comment, you are using uppercase names in your table names and synchronization sees the tables in DB as lowercase names - thats propably the root of the problem.
With your information we can propably reproduce this better and we will fix that in one of the next releases of Workbench 5.2.

thx for your help
tax

Johannes,

It is indeed a problem with MWB creating lower-case table names. I forgot to try this !. Tried everything else.

Synch is working with lower case names.

Details you wanted :
DB  		: Server version: 5.1.36-community MySQL Community Server (GPL)
OS  		: Windows XP Professional
File system	: NTFS
MWB 		: 5.2.6 OSS ALpha ( also tried on 5.1.8 )

Questions :
-----------
A) Since you mentioned file system, would MWB work correctly in Mac OS X ?If so, I can switch to that and continue.
B) Can I get a patch when you fix this bug ?
C) If not B), can I be notified when the new release is available ?

One more bug ( do you want me to log this separately ?, I'm short on time but I might be able to )
1. Double clic on table in ER diagram
2. Modify varchar(n) to varchar(m)
3. * is apprears next to file name on MWB titlebar. '<filename.mwb> - MySQL Workbench'
4. Ctrl-S does not save file. '*' still appears.
5. Goto menu File --> Save File . File is saved.

Will be willing to pass on any other information your team needs.

Thanks.
Shantanu

Hello Shantanu Upadhyaya,

I opened a separate bug report for you second issue. Unfortunately I need some additional information. So would you please answer me on bug #48682.

Back to your original issue.

As Johannes pointed out this is related to the case insentivity of NTFS.

I tested with Linux and there all worked fine. I don't have a MAC. Because file system on MAC also is case sensitive like Linux file system all should work fine on MAC.

Because this bug here is a file system/server problem this won't be easy to fix. The efford to fix this is high and also the risk to fix and break something else is high.

Usually, our development will add the patches directly to the bug report. So you will find a patch here when this bug is fixed.

Because of high fixing risk and effort we can't promise that we will get fix this up to the next release. But we will give our best here to fix it asap.

this has been fixed on repository.
fix will be included in 5.2.9

An entry was added to the 5.2.9 changelog:

Working through the Synchronize Model with Database wizard did not result in the live database being updated.

Thank you MWB team :-)

Hello y'all. First, thank you for all your hard work.  MWB its great!

I just wanted to comment the following:

I'm working on Windows OS with MWB 5.2.30 CE (V6790).

I'm having the same issue with sync, but all of my table names (and column names) are lowercase.  I too experienced a successful sync the first time, and each time after no changes were made upon sync.

I'm commenting because y'all seem to have pinned the problem to case sensitivity.
Am I doing something else wrong?

To me it sounds as if you have problems with a setting in the database.
I assume that the script file generated at the end of the model syncronization has the proper capitalized letters in them ?
If so - here is your solution.

http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html