In MySQL Workbench 6.0, three longstanding issues related to model synchronization were addressed. In this post, we’ll cover how these issues can happen, the underlying cause and how to resolve them.
Model Synchronization
When you synchronize a model to a database, Workbench performs the following steps:
- Retrieve and reverse engineer the target schema from the database, to an internal representation that can be worked on by Workbench
- Compare the schema from the model with the corresponding schema from the database, object by object
- For each difference found, perform the corresponding action needed to change either the source or the target object, so they’re both looking the same. This may mean creating, altering or dropping objects in the database or updating the model to match the database. Because some of these actions can be destructive (such as when a table or column needs to be dropped), care must be taken to review the generated MySQL script for any mistakes before applying it to the actual server.
Reverse Engineering Source and/or Target
The first step is usually straightforward, since the internal representation used in WB maps 1:1 to schemas and database objects from MySQL. Workbench also supports synchronising with schemas reverse engineered from a MySQL DDL script, which is pretty much the same as using a live database.
Synchronizing with Schemas with Other Names
By default, Workbench will search the server for a schema having the same name as the one in the model. But sometimes, the desired target schema has a different name. For example, one may want to synchronize a model of sakila with a schema called sakila_prod in the database. To allow that, select a schema in the list and click the Force Synchronize With… in the Schema Selection Page of the synchronization wizard.
References to the schema name in code objects (views, triggers, SPs and functions) will be automatically refactored to the target name. So, for example, if your sakila schema has a view defined as:
CREATE VIEW <b>sakila</b>.film_list AS select * from <b>sakila</b>.film
and you synchronize it against sakila_prod it will be changed to:
CREATE VIEW <b>sakila_prod</b>.film_list AS select * from <b>sakila_prod</b>.film
Renaming schemas
WARNING: A related case is that of when instead of synchronizing a schema with another of a different name, you actually want to rename it. But because altering the name of an existing schema is not supported by MySQL, a rename would mean dropping the original schema and creating a new one in its place. Because data is not copied by the model synchronization, your data would be lost. Therefore, keep that in mind when renaming schemas in your model and synchronizing it.
Example
Here we are synchronizing a schema called sakila, but the target MySQL server does not have that schema in it. In this server, the schema was created with the name sakila_test
To force Workbench to sync sakila from your model with sakila_test from the database, select it in the list and then pick the target schema you want in the dropdown list and click [Override Target].
Schema Comparison
Things are easy when you just add, modify or delete an object in your model. We just need to lookup an object with the same name in the database and perform the appropriate action: a new object in the model must be CREATEd in the database and a removed object must be DROPed. Changed objects are handled differently depending on their type. Views, indexes, functions, stored procedures and triggers can just have their old version dropped and recreated from scratch. Tables and columns are updated using the ALTER statement as recreating them would mean data loss.
However, when an object is renamed, there’s no straightforward way to know that, for ex., the column you once created as name in your model and then forward engineered to the database, is now renamed in the model to first_name. To solve that, WB stores the original name of the object in an additional internal field called oldName. Then, when searching for a matching object in the database, it searches by oldName. So it would correctly determine that the column called name corresponds to first_name, which should be renamed for synchronization purposes. Once synchronize renames the object in the database, oldName is updated to the new name, so next time a rename occurs, we can locate the DB object by the new name.
Sometimes, the object is renamed directly in the database. So when you synchronise the model to the database, there’s no way for WB to know that by itself. oldName is of no help here. So the result is that WB thinks the renamed DB object is an object that was removed from the model, so it must be DROPped, while the original object in the model doesn’t exist yet, so it is to be CREATEd. Which could lead to potentially unpleasant consequences.
Sinchronizing against a script or when both source and targets come from a script/database is an even bigger problem, since we don’t have any information at all about renames from either direction.
Above, we’re synchronizing sakila from the model with sakila_test from the database. In the database, the table actor was created with a typo and it’s called atcor. The table was created in the database independently from the model, so Workbench doesn’t know that atcor is supposedly the same column as actor. If you click the rows corresponding for both, you see a CREATE statement for actor…
and a DROP for atcor. We need to tell Workbench that actor from the model and atcor from the database are the same columns.
In the country table, there’s a similar problem with columns. In the database, the column called country was created as name, supposedly outside of Workbench, so there’s no knowledge that country was once called name or anything like that. Thus, the synchronization action appropriate for this case is to DROP name and add country. Again, what we actually want is some way to match country and name together.
So starting from MySQL Workbench 6.0, you can manually specify the mapping of tables and columns between source and target schemas. In the “Model and Database Differences” page in the synchronisation wizard, click the Column Mapping… button to bring up the Column Name Mapping dialog. For fixing table mapping issues, click Table Mapping… for an analogous dialog for tables.
When you click the [Table Mapping…] button a dialog pops up, where all the tables for that schema is shown in a list. The list has the name of the table in the source/model, the default table to be matched against (taken from oldName), the actual table that it will be synchronized with (same as the previous column by default, blank if the target table doesn’t exist or the table it will actually be matched against, if it’s being overriden). The last column shows the expected action that would be taken (drop, create, change or rename), always considering a Model to Database synchronization direction. To override the mapping of a table, select it in the list and pick the table from the database that it should be mapped to.
Here is the result of the remapping of tables, as you see in the generated SQL, it will simply rename atcor to actor.
The Column Name Mapping dialog is similar to the one for tables. Click the table you want to fix and select [Column Mapping…] to bring it up.
Conclusion
Ideally, any changes to the database schema would first happen in your model and the database would only be updated using code generated from it. That allows WB to transparently and automatically handle any renames and changes made to your schema objects and graciously update your database to match it. But reality is not always so neat and often changes are made in the database independent of your model or in an order/situation that’s not very easy to track.
As we’ve seen, MySQL Workbench 6.0 includes some functionality to work around problems of that kind, which now allows Model Synchronization to work correctly in almost any scenario.
