MySQL Bugs: #42149: Synchronization nearly unusable

Bug #42149	Synchronization nearly unusable
Submitted:	16 Jan 2009 8:53	Modified:	26 Jun 2009 10:42
Reporter:	Jan Juricek	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S1 (Critical)
Version:	5.0.30	OS:	Windows
Assigned to:	Alexander Musienko	CPU Architecture:	Any
Tags:	CHECKED

Description:
I have created a mysql chema using Workbench tool, then I changed the cheme a lot and came back to Workbench to continue the work in it. I tried to synchronize changes made in the database with my model, so I would at least have new tables, triggers, views and other stuff in my model (and I would place them on their place in different ERR diagrams manually)

First thing was, that I had to click about 400x to change direction of arrows to make them point to the left in the SQL diff tree. I assumed that would mean, that no changes would be made to database and everything would go to my model.

Nope.

Please find attached screenshots of SQL diff tree before pressing Next button (if I'm really able to attach files after submitting this form)

Review of SQL queries which were supposed to be executed on the database is attached. Please look at it now.

First of all, I don't know why the sync tool doesn't respect my wish to don't touch the database at all? Why it would delete all my procedures and views? Why those changes in tables?

Second thing is, that I didn't see any review of changes in my model.

I am unable to use this feature.

If I'm doing something wrong, please correct me.

How to repeat:
- Create new model, create new database
- Add a table in the model with one column (in my case table1 with int idtable1)
- Add a column to your table in the model (in my case varchar column2)
- Directly in the mysql, create a view (in my case create view table1_view as select * from table1)
- Select Database -> Synchronise in Workbench, connect to your database (in my case test)
- Set both arrows (both table and view) to point to the left

Result will be

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';

-- -----------------------------------------------------
-- Placeholder table for view `test`.`table1_view`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test`.`table1_view` (`id` INT);
USE `test`;

-- -----------------------------------------------------
-- View `test`.`table1_view`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test`.`table1_view`;
DROP VIEW IF EXISTS `test`.`table1_view` ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

--------------------------

Commence.

- The second column in table1 should dissapear! Nothing like that was announced.
- The view in the table has been deleted. Why?

That is definitely wrong!

Note: If you do the sync again, view is created in the DB again. But the column in table1 is lost.

SQL produced by SQL Diff tree

Attachment: result-diff-query.sql (text/x-sql), 7.25 KiB.

Shema screenshot part 1

Attachment: schema01.JPG (image/jpeg, text), 121.33 KiB.

Shema screenshot part 2

Attachment: schema02.JPG (image/jpeg, text), 145.44 KiB.

Shema screenshot part 3

Attachment: schema03.JPG (image/jpeg, text), 120.11 KiB.

Shema screenshot part 4

Attachment: schema04.JPG (image/jpeg, text), 114.67 KiB.

Shema screenshot part 5

Attachment: schema05.JPG (image/jpeg, text), 109.10 KiB.

Shema screenshot part 6

Attachment: schema06.JPG (image/jpeg, text), 110.02 KiB.

Test model after second sync

Attachment: test.mwb (application/octet-stream, text), 3.88 KiB.

Many thanks for pointing this out.

It seems there is something more broken at the sync from database into model scenario.

Verified by using a very simple design.

Our developers will check this.

I also have this problem since I consider the database to be the master. The Workbench application is for me just a way to print a nice map of the database. But it really can't handle that task in a satisfying way. I NEVER want changes in the Workbench to be propagated to the database. I would like to add the following disturbing behaviour to this bug: 

If I drop tables or views in the database and then synchronize with the Workbench model they are not deleted in the Workbench model.

Still very mystic what happens during synchronisation.

When you explicit say that you don't want to synchronise some stuff it will be synchronised anyway.

Still very mystic what happens during synchronisation.

When you explicit say that you don't want to synchronise some stuff it will be synchronised anyway.

Just another comment to confirm this behavior.

I created a model, and synchronized it to an empty live schema. 

If I I make no changes to either the live schema or the model in Workbench, the "Synchronize..." menu selection idnetifies many many changes it beleives need to be made.

There have been several fixes to the synchronization routines in version 5.1. the fixes will be included in 5.1.15

An entry has been added to the 5.1.15 changelog:

MySQL Workbench Syncronize Model feature had unexpected behavior. For example, synchronizing the model with the live database would cause changes to the database, even if this was not desired. Also, if tables were dropped in the live database, and the model synchronized, the changes to the live database were not reflected in the model. Further, if a model was created and synchronized to an empty schema in the live database, when synchronization took place again, MySQL Workbench would indicate many changes were required, even if no changes had been made to the model or database.