Small MySQL Workbench review


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.

MySQL Workbench - Editing a table

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.

MySQL Workbench - Adding foreign key constraints

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:

MySQL Workbench - Editing special indexes

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:

MySQL Workbench - Adding standard inserts for a 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:

MySQL Workbench - The automatically generated EER 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:

MySQL Workbench - The improved EER Diagram

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:

MySQL Workbench - The EER Diagram with “Connect to Columns”-Notation

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:

MySQL Workbench - HTML-Report of the database.

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:

MySQL Workbench - Validating the schema

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;

MySQL Workbench - Not able to forward engineer simple databases.

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:

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!

Information and Links

Join the fray by commenting, tracking what others have to say, or linking to it from your blog.


Other Posts

Reader Comments

Hi,
Great work on the review! Thanks for pointing out some of the open issues. The team is already onto them and we expect to fix those during the next few days to make sure everything is working fine in RC3.

Mike

Interesting tool i’ll sure try out to save me lots of time maybe and to get me some nice SVG pictures as a bonus

Ever tried DBDesigner for forward engeneering? Basic features but pretty useful. Find it here:
http://www.fabforce.net/dbdesigner4/

Yes, i tried DBDesigner4 earlier, but since MySQL Workbench is its natural successor, I thought it should be better. – And it is better. Forward Engineering is working fine now (after fixing some bugs, and improving the validation tests).

hi,

just wondering what is the difference between Workbench Standard and Sparx EA?

is WB head and shoulders better?
please advise…we need to make a decision this week.

also is their a product MATRIX out there??

thanks

artur

Since I dont know Sparx EA, I cannot comment on this and recommend one or the other.
But I would recommend you to try out both. For Sparx EA, there is a 30 day Trial-Edition, and for MySQL Workbench, just use the OSS Edition, and you will see which additional features you get by buying the Standard Edition.

Here you find a product comparision matrix between MySQL Workbench OSS and SE: http://dev.mysql.com/workbench/?page_id=11

Hope that helps.

FYI, as of mid-July 2008, using Workbench OSS v5.0.23, it’s still far too buggy for any real use. Crashes abound, and the forward engineer create/alter features are full of serious bugs.

I’ve been meticulously filing serious bug reports since the first day I started using it a month ago, and I keep finding new ones.

Although not labelled as such, this remains beta-quality software.

by the way, i had the same problem

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

for my case was having duplicated constraints key names on diferent tables.

ElmoVil