DiLoc-Blog

This is a small review of MySQL Workbench in the current Version 5.0.16RC. Since I use Workbench at work for some time now and am a beta tester for the standard-edition, I thought it would be useful to give an overview of the current state.

This review is in no way intended to be complete, and should provide just an overview or first look.

For the purpose of this review I created a small database schema for a project management software. The database design currently supports the following features:

• Multiple customers
• Unlimited projects per customer
• Unlimited users per customer
• Users may take part in projects and have customer-defined roles
• Projects consist of unlimited milestones
• Milestones consist of unlimited tasks
• Tasks may have dependencies on other tasks
• Users may take part in tasks

You see, its fairly basic but consists of enough tables to show off the features of MySQL Workbench.

Creating the schema

Adding the needed tables for the schema was easy and straight forward. MySQL Workbench allows you to add/move/edit columns at will, and you may specify format, keys and other column-related properties. A nice feature is, that it allows you to add comments on a per column-basis so you are free to document/explain difficult columns as you see fit.

Editing Foreign keys is very easy as you can see in the next screenshot. Selecting the referenced table and referenced column works with drop-down boxes so you automaticaly get all posibilites, and it is not possible to get typos. This is very easy and comfortable. You may also set options for the foreign key (OnUpdate/OnDelete) and comment the foreign key.

Adding Indexes is achieved by double-clicking a column when editing columns (for easy primary keys). If you want special or additional indexes, this may be done through the indexes tab of the table:

During development it is useful to insert testing data into the tables. For example a set of predifined users. This is possible through the inserts-tab. After a click on the “Open Editor…”-button you get a nice editor which allows you to fill out some rows for the table:

Unfortunatly the editor has some shortcomings: It does not allow you to resize the columns, and auto-incremented columns always get 0 as value, which might be confusing for novice users.
The generated INSERT INTO clauses are then displayed and may be copied to clipboard or automatically inserted into the table when creating the database. (See below)

I think this covers the most needed features concerning table creation and editing. If you still want to define advanced parameters like triggers, table-partitioning, advanced options, etc, you are free to do so, all possibilities are there.

Documenting the schema

When you finished with your schema, you probably want to document it. The most common way to this is through an EER Diagram. MySQL Workbench allows you to add multiple EER Diagrams for one schema. You may arrange the tables on it youself, or let Workbench do the dirty job through the option “Create Diagram for Catalog Objects”. For my example database it gives the following diagram:

As you can see the automatically generated diagram is not quite optimal as several lines overlap tables and so on, but for a start its not bad. You are free to move the tables around and group them in logical parts.

Unfortunatly while editing the model and moving the tables around, MySQL Workbench crashed. I reported the Bug on the Bugtracker, lets see if they may fix it.

After messing around with it a little bit, I came up with this:

If you dont like the Crows Foot notation, you are free to choose one of the available notations. (Crows Foot, Classic, Connect to Columns, UML, IDEF1X). Personally, i like “Connect to Columns” best, because it makes the foreign key relations more clear:

Sadly, when using this notation, the lines are always a direct connection without edges. I imagine this gets really messy with big databases and many tables. This should be improved further.

As you may have seen on the screenshot, the diagram view also allows to create relationships directly in a graphical manner which may be easier for some. - I prefer to define foreign-key relationships in the table-editor. When you add a new foreign-key relation, it immediatly shows up in the diagram. -No need to refresh or anything. Another possibility is to add text-notes, images and layers to the diagram to break things into parts or explain it better. I find this quite handy, allthough you apparently cannot change font-sizes of text-fields. That would be useful for headings, etc.

When you are satisfied with your diagram you may export it into PNG, SVG , PDF or Postscript file.
Exporting to PNG worked flawlessly, exporting to SVG showed many strange lines goeing trough the tables, but that could also be firefox’ fault. I did not investigate that further.
Exporting to PDF also worked great, and has the advantage that it is vector-based, so is easily zoomed and printed in high resolution.

Another way to document your schema is to generate a HTML-Report of it. There are some templates to choose available, this is the basic-frames template:

Allthough nice, this particular template is buggy. When clicking on a table on the left, it will not scroll to the table on the right. - But that should be easy fixable.

Connecting to a database

So what do you do when you created your schema, documented it, and gave it to your boss? - You eventually begin working on the software which uses the schema and want to have a real database based on that schema.

MySQL Workbench allows you to “Forward-Engineer” the schema into a database. First you should validate your schema using “Model -> Validate”. You may also do this during the Forward-Engineer Wizard, if you want.
This shows you common errors like missing indexes, etc. This is a really nice feature, but the output lacks in several ways. The most obvious problem is that you cannot distinguish the found problems from the normal log entries:

When trying to fix the errors one error was not fixable as I was not able to set a correct default value for a boolean column, I tried 0 and false, both lead to an error. The other error just said “Referenced column name is invalid for foreign key customer”. That was not really helpful as i named all foreign key relations to the customer table “customer” so I did not know which table was meant.

When using the Forward-Engineer Wizard there are some options presented (Add inserts, generate drop statements, etc), after validating the schema, the SQL to create the database is generated. You can then copy it and do with it what you like, or continue the wizard and connect to a database and execute the statements directly. However this did not work with my schema as I got the dreaded 121-error:

Error 1005: Can’t create table ‘.\myproject\projects.frm’ (errno: 121)

This happened for all tables with foreign key constraints, and that allthough foreign key checks are disabled while creating the tables through this statement:

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

I already reported some forward engineer wizard related bugs in the past, but apparently there are still some left. In fact I was never able to successfuly forward engineer a database with MySQL Workbench until now. - And that with fairly basic models. For me, this makes Workbench nearly pointless. (Fortunatly I can convert the model to a propel schema for use in PHP and let propel generate the tables. - Propel works better in that regard because it is able to actaually create the same schema flawlessly. )

Since I was not able to successfully create the database, I was not able to test the other features MySQL Workbench offers. These features include creating SQL-ALTER scripts to update an existing database, and directly synchronizing your changes with a database.

Final thoughts

In general MySQL Workbench looks really promising. The available featureset is rich and really helps when developing new databases. However the current release candidate version 5.0.16 is still quite buggy and even fails to do most important features like the forward engineer wizard. Remember: I started with a clean file, added 9 tables with some foreign key constraints, nothing fancy. During normal use I encountered various bugs, where one even leaded to a corrupted file. (Because I said “Save” when Workbench asked to save the document right after the crash. I was first surprised that I could still save the file, but in the end I should have rather said “Forget changes” because the file was busted after saving it.)
So in conclusion it is really likely that you run into similar bugs when *working* with MySQL Workbench, and that is a real pity. I repeat: MySQL Workbench shows great promise, but in the current state it is simply to buggy to be really use. It is also some kind of pointless if important features like creating a database out of the schema simply dont work. But once MySQL figures out the current problems and bugs and succeedes in making this product stable and usable, it will be a great winner for all people working with MySQL.

Things that are possible with MySQL Workbench, but that this review did not cover:

• Working with Views
• Working with Routines
• Defininig Schema privileges for tables & users
• Scripting through the GRT-Shell
• Reverse Engineering existing databases

Features I currently miss in MySQL Workbench:

• Some sort of Revision management with automatic generation/maintenance of SQL ALTER scripts and database versioning
• A Linux/Mac version
• Proper documentation to write plugins

In case you are interested, here is the list of Bugs that I encountered and reported during the creation of this review:

• Crash while editing EER Diagram
• Unhandled Exception when expanding indexes in diagram
• Unknown Exception caught when changing to “Connect to Columns”-notation
• Unhandled Exception when moving mouse after deleting a layer
• Forward Engineer Wizard always fails MySQL-specifc validation.
• Forward Engineer Wizard fails to generate simple databases

What are your experiences with MySQL Workbench? Did you find it similarly buggy, or did it work for you? Have you found out some tricks to work around some bugs? Comment and share your thoughts!