MySQL Workbench 6.2: Usability improvements and more

Direct Schema Tree Action Buttons

Screenshot 2014-09-05 15.00.21 The schema tree in the SQL Editor now has some very convenient buttons for accessing the most used functions for each object type:

  • Table or Schema Inspector
  • Object structure editor
  • Table data browser/editor
  • Call Stored Procedure or Function

Format Note Objects in Diagrams

Note objects in diagrams can now be resized and have its contents automatically rearranged. You can also change style attributes like font, background color and text color. Screenshot 2014-09-05 15.07.27

Other improvements and bug fixes that make a difference

MySQL password is remembered for the session, even if not stored in the keychain, so you don’t need to re-enter it when a new connection is needed.

Keyboard shortcuts now work in the Scripting Shell.

Platform Updates

MySQL Workbench 6.2 also finally adds native 64bit support for Windows. This should allow working with larger data sets and script files. Oracle Linux/RHEL 7 support was added. To improve quality and user experience, we will be providing 64-bit binaries for Linux. Linux users who want 32-bit binaries, can compile from source.

MySQL Workbench 6.2: New Migration Source and Sync Feature

MS Access Migration

MS Access support was added to the Migration Wizard in MySQL Workbench 6.2, joining MS SQL Server, Sybase ASE, Sybase SQL Anywhere, PostgreSQL, SQLite and “Generic ODBC” as migration sources. You can read more about it in How-To: Guide to Database Migration from MS Access using MySQL Workbench.

Include Model Scripts in Forward Engineering and Synchronization

Workbench modeling has always supported attaching SQL script files to the model, usually for documentation/organization purposes. You can now include these attachments to the output script when performing forward engineering or synchronization. Screenshot 2014-09-05 14.55.35

Resume data copy in Migration Wizard. If a data copy fails during database migration (because of a timeout or network failure, for example), you can now click Resume to retry the data copy. Workbench will find the last row that was copied successfully and try to restart the copy from that row.

MySQL Workbench 6.0: Model Synchronization Improvements

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:

  1. Retrieve and reverse engineer the target schema from the database, to an internal representation that can be worked on by Workbench
  2. Compare the schema from the model with the corresponding schema from the database, object by object
  3. 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 sakila.film_list AS select * from sakila.film

and you synchronize it against sakila_prod it will be changed to:

CREATE VIEW sakila_prod.film_list AS select * from sakila_prod.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.

 

MySQL Workbench 6.0: What’s New

With the first beta of MySQL Workbench 6.0 just released, we’ll go through the list of improvements we’ve made since 5.2.47

New Home Screen

The Home screen went through a renovation and now has a modernized look. As part of the SQL Editor and Administration GUI unification, there’s now a single list for MySQL connections. Recently opened model files and other major features are also accessible from it.

You can organize different connections into “folders” by right clicking on a connection and selecting “Move to Group…” in the context menu.

New server connections can be added by clicking the + button next to the MySQL Connections heading. By clicking the Configure Remote Management… button in the new connection setup dialog, you can add server management capabilities to the connection. As before, SSH access with “sudo” is needed for remote management.

The wrench icon next to the heading brings up the connection editor, which lets you change connection and management parameters from an editor interface. Configuration was simplified compared to 5.2.

SQL Connections

In MySQL Workbench 6.0, the SQL Editor and Administrator interfaces were merged together. You can now access administration functionality, such as restarting the server or listing connections from the same database connection tab. The primary sidebar now has both the familiar Schema tree and the administration items.

If you’d like more space for the Schema tree, you can click the expand button next to the SCHEMAS heading and give it more vertical space.

Schema Inspector

Schema Inspector, allows you to browse general information from schema objects in the server. For tables, there’s also a Table Maintenance panel, from where you can perform maintenance tasks such as ANALYZE, OPTIMIZE, CHECK and CHECKSUM TABLEs. To access, right click a schema and select Schema Inspector.

Table Data Search

You can select schemas and/or tables to perform client-side searches for arbitrary strings and patterns on their contents.

Table Templates

If you find yourself wishing for more control over the default column definition and often create tables having the same common set of columns, you can now create templates for them. The same templates can be used in the SQL Editor and also in the EER Modeling tool.

 

Improved Server Status

More information about your server in a glance.

Context Sensitive Help

Online, context sensitive help is available in query editors. Place the cursor over a SQL keyword and the help tables in the server will be queried for it. This is equivalent to the HELP keyword from the command line client. To disable help, hide or switch the sidebar pane to a different tab.

Vertical Query Output

A new text mode, vertical query output was introduced. This is equivalent to the \G option from the command line client and outputs the results of a query laid out in Column/Value pairs, one value per row. This improves readability of certain types of resultsets. Ctrl+G/Cmd-G can be used as a shortcut for that command.

Cascaded Delete Statement Generator

You can now generate the list of DELETE statements that would be needed to delete a given row from a table, in case there are other tables with foreign keys that reference them (which would prevent the row to be deleted). Select a table in the Schemas tree and from the context menu, select Copy to Clipboard -> Delete with References. A similar feature for SELECT statements will generate the queries that would list the rows to be deleted from other tables.

 

Improvements to Log Viewer

The log file viewer was improved for when browsing files that are not readable by the current user.

Modeling

Synchronization

DB modeling got several bug fixes, specially in Synchronization. You can now Synchronize a schema with another having a different name (like sakila in your model vs sakila_test in the server). You can also fix object name mapping issues (when a table or column cannot be automatically recognized as being the same, because of renames) using the new Table and Column Mapping editor.

Syntax Highlighting was also added to the various places where SQL code is visualized.

Table Templates

Quickly add tables using table templates, having any number of columns with the attributes you want.

Improved SQL Editors

A standardized toolbar was added to all SQL code editors, where you can export/import files, reformat the SQL, perform find/replace etc.

Migration Wizard

The migration wizard was extended to support 2 new database sources. You can now also migrate from Sybase SQL Anywhere and SQLite. That makes the total list of supported sources to:

  • MS SQL Server 2000, 2005, 2008, 2012
  • Sybase Adaptive Server Enterprise
  • PostgreSQL
  • Sybase SQL Server
  • SQL Anywhere
  • SQLite
  • and MySQL to MySQL migrations/database copies
This should get you started with the new stuff, but we’ll keep posting articles with details about each feature in the coming weeks, so make sure to come back for more info!