MySQL Bugs: #61945: Synchronize Model feature has many bugs

Bug #61945	Synchronize Model feature has many bugs
Submitted:	21 Jul 2011 17:15	Modified:	1 Jun 2013 22:18
Reporter:	Greg Ellis	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S2 (Serious)
Version:	v5.2.34 CE Revision 7780	OS:	Windows (7 - 64 bit)
Assigned to:		CPU Architecture:	Any

Description:
I am using an install of XAMPP for mysql/php for use with MySQL workbench. Every time I try to use the "Synchronize Model" feature it either stalls at the "Fetch Object Info" stage when I click next nothing happens or if it does get past that stage it wants to recreate a bunch of my views or tables even though there hasn't been any changes to them or it decides that a view is not "attached to any database table" and deletes it without asking for any confirmation as shown below. It all seems very random because for some tables and views it handles them as expected but others (for whatever reason) are handled totally incorrectly.

The tblQRCodeWIFI view is attached to tblQRCodeWifi_base, but Workbench somehow decides that it is not needed and first re-creates it, but then after decides it should drop it like shown below. This doesn't make sense to me.

USE `fision_db`;
CREATE  OR REPLACE VIEW `fision_db`.`tblQRCodeWIFI` AS
SELECT  QRCodeWIFIID, ClientID, ClientQRCodeID, SSID, Password, WIFITypeID, DateCreated, DateModified, Active
FROM tblQRCodeWIFI_base
WHERE tenant = SUBSTRING_INDEX(USER(), '@', 1)

DROP VIEW IF EXISTS `fision_db`.`tblqrcodewifi` 

How to repeat:
Create a model named FooBar with a table and view like shown below. Then forward engineer this to a mysql database server. Right after that is done then run the "Synchronize Model" feature. On my XAMPP install it will stall at the "Fetch Object Info" stage. Using my shared hosting mysql server through lunarpages it will try to tell you that the view needs to be recreated eventhough there has been no changes. I am not sure how to reproduce the result where it wants to also drop the view, but this happens using one of my bigger models that has over 107 tables and views.

CREATE  TABLE IF NOT EXISTS `FooBar`.`tblQRCodeWIFI_base` (
  `QRCodeWifiID` INT NOT NULL AUTO_INCREMENT ,
  `ClientID` INT NULL ,
  `ClientQRCodeID` INT NULL ,
  `tenant` VARCHAR(64) NOT NULL ,
  `SSID` VARCHAR(255) NULL ,
  `Password` VARCHAR(255) NULL ,
  `WIFITypeID` INT NULL ,
  `DateCreated` DATETIME NULL ,
  `DateModified` DATETIME NULL ,
  `Active` INT NULL DEFAULT 1 ,
  PRIMARY KEY (`QRCodeWifiID`) )
ENGINE = MyISAM

CREATE VIEW `FooBar`.`tblQRCodeWIFI` AS
SELECT  QRCodeWIFIID, ClientID, ClientQRCodeID, SSID, Password, WIFITypeID, DateCreated, DateModified, Active
FROM tblQRCodeWIFI_base
WHERE tenant = SUBSTRING_INDEX(USER(), '@', 1);

Suggested fix:
If you guys could have a closer look at the code that grabs the information about the schema from the mysql database to compare to the model and make sure that it is getting the information on the tables and views correctly that would surely point you in the direction of this problem. Maybe look for code that could bring back different results depending on server configuration because I get different results using different server configurations. I have noticed that maybe case sensitivity could be an issue as well so maybe look at that too.

Thanks,
Greg

Please, send the output of:

select version();

from your MySQL server.

Output for select version() of MySQL server is 5.5.8 and I get the same results on my other MySQL server which is 5.5.11

Thanks,
Greg

Could you please provide the model file (private if you wish). Thanks.

One more thing. My shared hosting server is using MySQL 5.0.92-community

Using my shared hosting server the synch doesn't stall at "Fetch Object Info", but the problem regarding the sync trying to recreate views, dropping them, and such still occurs.

If I select "Only developers will be able to see the file" will that be private? I want to make sure no one else gets my database :)

I couldn't repeat below the log:

Executing SQL script in server

SQL script execution finished: statements: 560 succeeded, 0 failed

Yes regarding your question, only developers will see your database.

Forward engineer works fine, but it is if you select "Database" then "Synchronize Model..." and go through the steps where I have problems. Were you able to do that with proper expected results?

Thanks

Synchronization

Attachment: siynchronization.png (image/png, text), 193.73 KiB.

See prior attached picture. Thanks.

what I see #1

Attachment: Untitled-1.jpg (image/jpeg, text), 455.26 KiB.

what I see #2 (dropping view)

Attachment: Untitled-2.jpg (image/jpeg, text), 487.03 KiB.

list of changes that workbench wants to make

Attachment: changes it wants to make.sql (application/octet-stream, text), 31.14 KiB.

As you will see from the attached screenshots and sql change file, Workbench thinks that there have been changes to views and tables in on my database server , but, the tables and views are identical between my server and the model.

I can't understand how this could be happening. Do you think it might be my mysql server configuration? I have tried uninstalling workbench and reinstalling and that didn't make any difference :(

Thanks

Thank you for the feedback. Indeed now running again I was able to get what are you reporting.

To add to this conversation, I have also found that when I make changes to the model, either changing the name of a table or adding a column to a table, the synchronization utility doesn't say there are any changes.

Mac OS X 10.7.4

Has this even been addressed? I am seeing similar behavior in the latest version of Workbench where every time I synchronize the model, having made no changes, there is an index it always wants to alter.

This is of concern mainly because I fear that Workbench is getting confused. The actual effect seems to be nothing. I suspect that the change already exists and so is ignored by the database, but I am concerned that a) this will not always be the case, and b) it junks up the synchornization SQL, making it harder to tell what is going on.

Any progress on this?
I am using Workbench 5.2.47 on Mac and still have strange behaviors.
90% of my tables synch correctly.  The others are a mix.

A.
Workbench says the DB needs to be updated with...
ALTER TABLE `wb_incontrol_schema1`.`cc_build_files` CHANGE COLUMN `id_build_files` `id_build_files` INT(11) NULL DEFAULT NULL AUTO_INCREMENT  

...but even after I run it manually.  The next time I try to synchronize, it says I need it again.

B.
I have a new table in the schema.  The source does not have it.  The "Model and Database Differences" view  shows no arrow.  Even if I change it to force it to the source, no sql code is given, so I never get the table.

The view synchrnization problem is because the server modifies the definition of the view when it's created.

This is a duplicate of bug #65108