MySQL Bugs: #65108: View synchronization problem

Bug #65108	View synchronization problem
Submitted:	26 Apr 2012 7:39	Modified:	10 Jul 2013 23:31
Reporter:	khai nodd	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.39 CE	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
When synchronizing a database model with an existing database, views can be deleted. If views exist in physical database, are erased. If views do not exists, are created.

How to repeat:
1.- Create a database
2.- Create a table
3.- Create a model using reverse engineering the database
4.- Create a view in the model
5.- Synchronize model
6.- Execute Select * from view. View exists.
7.- Synchronize model
8.- Execute Select * from view. View does not exists.
9.- Synchronize model
10.- Execute Select * from view. View exists.

And so on...

Please, upload some .mwb file that demonstrates the problem.

Model sample showing the problem

Attachment: sample.mwb (application/octet-stream, text), 5.04 KiB.

Verified just as described using your .mwb file uploaded:

mysql> use prueba;
Database changed
mysql> show tables;
+------------------+
| Tables_in_prueba |
+------------------+
| persona          |
| vistapersona     |
+------------------+
2 rows in set (0.02 sec)

mysql> select * from vistapersona;
Empty set (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_prueba |
+------------------+
| persona          |
+------------------+
1 row in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.62-community |
+------------------+
1 row in set (0.03 sec)

while working with local MySQL server 5.1.62 on Windows XP. Looks like the problem is related to mixed case view name, vistaPersona, used in the model...

Bug #67370 is a duplicate

Bug #64719 is a duplicate

I've run into a similar problem: Synch drops some (but not all) views. Resynch recreates views that don't exist and drops others. All views have mixed case names in the model. 

A workaround that works for me: 
At the "Model and Database Differences" step of the synch wizard, select the views that currently exist in the source database and "Ignore" the update from the model.
Hugh

Bug #61945 is a duplicate of this

Posted by developer:
 
New oldModelSqlDefinition and oldServerSqlDefinition fields were added to db.View object. oldModelSqlDefinition is updated whenever a synchronization happens or when forward engineering to the server. oldServerSqlDefinition is updated whenever a synchronization happens, through a new back-synchronization step that occurs at the end of the sync wizard. The final definition of the view that is stored in the server is read back and stored in oldServerSqlDefinition. So, whenever a change occurs in the model, the comparison between sqlDefinition and oldModelSqlDefinition will flag a change. Likewise, whenever the server definition changes, the comparison between oldServerSqlDefinition and the sqlDefinition of the object reverse engineered from the server will flag a change. These fields are stored once for each target server, in a Sync Profile. This should solve the problem of the server rewriting the definition of views, which would cause it to never match what the user gave as the original DDL.

Fixed as of MySQL Workbench 6.0.3 (BETA 2), and here's the changelog entry:

Database synchronization with a View would sometimes fail, and could
remove the View.

Thank you for the bug report.

I'm not sure why this one is closed, as views area always synchronized (see also #64719) even in 6.2.x, which is quite irritating if you're a database designer and need to frequently apply modifications to different servers.