Toad for MySQL – A True No Brainer Decision

I’m using Drupal with MySQL on the back-end. So far I’ve been doing all the MySQL tasks from the command line, which is empowering since I can still hack it from the command line, but after a while I start getting PL/SQL flashbacks that induce headaches that only a few good beers can resolve. I’ll hold off on the discussion of good beer and instead focus on what I’ve found to be a hands-down no-brainer. If you want to use MySQL then you really should use Quest’s Toad for MySQL. I’ll talk about why and show you some of the things I really like.

Quest has been around for a while. I got involved with Quest because of their very strong Oracle tools for SQL developers. They supply a full range of tools for development, debugging, and tuning of Oracle. These are packaged into SQL Navigator and Toad. No clue why they have both…there’s a lot of overlap between the two. A couple of years ago they released a “freeware” version of Toad for working with MySQL and quickly after that Oracle released their SQL Developer with MySQL support.

At the time I wasn’t using MySQL so I blew it off thinking “only losers use MySQL anyway”. Well I guess now I’m one of these “losers” 🙂 Ha! I was checking this out over the weekend and looking for some decent GUI tools that could do the following:

1. Have a decent and easy GUI to learn that performs reasonably well on a “decent” machine.
2. Connect to databases and present a navigation of tables, procedures, indexes, etc.
3. Have a decent editor for creating and executing SQL code including color highlighting, syntax checking, and “code assist” features.
4. Have functions that allow for “quick browsing” entire tables.
5. Have support for a full range of SQL queries.

After looking out on the net and eliminating anything that cost $$$ I came down to three tools:

1. Quest Toad for MySQL 3.1.0.325: “Toad”
2. Oracle SQL Developer 12.: “OSD”
3. Eclipse Europa Data Tools Platform 1.5.x: “DTP”

Just to set some expectations – I’m reviewing this stuff from my personal perspective as a developer. My opinions are based on how I’m going to use the tool. I’m not trying to create the “measured from every angle and with no impartiality or bias” type thing. Hopefully you’ll still find this useful.

I’m going to be testing these tools on different machines…here’s the basics:

1. Toad is being tested on a Lenovo T60p Vista laptop with a dual core Intel processor and 2 GB of RAM.
2. Oracle SQL Developer 1.2 is being tested on a Windows 2000 Pro Dell workstation with 2 CPU, 2 GB of RAM, nice SCSI drives (no RAID), and a decent ATI graphics card. If you’re paying attention my screen captures from from machine #1…I screwed them up and redid the shots on my laptop at home.
3. Europa is being tested on Red hat Linux Workstation 5 on a dual CPU Dell workstation with 2 GB of RAM, a decent set of SCSI drives, and a nice ATI graphics card.

I’m doing this because its faster and easier…I think its fair. Now onto the review!

Eclipse Europa Data Tools Platform

For regular readers of my blog you will of course realize that I’m a big fan of Eclipse. Naturally then my first inclination was to just stick with this tool for database work. Europa (Eclipse 3.3) was already installed on my linux box and in fact I had the DTP already installed, but just for kicks I updated everything to the very latest versions.

Using DTP is a bit different from other Eclipse plugins in that there is no “project” concept per se. What you need to do is simply switch to the Database Development perspective and start adding connections. In my case this meant I also had to grab the MySQL jConnect drivers and install these manually. This is easy to do, but a pain. Download them and all you really need is the main .jar file in there. Drop it somewhere on your box (I put it in my JDK lib directory) and then you are good to go.

Startup Eclipse and switch to the DTP perspective. Then in the Data Source Explorer right click on databases and fire up the wizard to create a connection. You’ll need to use the generic JDBC driver and then configure that by telling Eclipse where it is. This stuff is pretty simple. If you go thru this and get an error saying that your connection failed because a password was set to “YES” then hold on – you gotta fix something in your database. With the MySQL JDBC Driver the user attempting to make the connection cannot have a host identifier of “%”. You need to go and update this to “localhost” for the account you are using. Once you clear that up you will be good to go.

DTP in my opinion is a little strange. Once you connect you will get a screen that approximates what you see below:

Database parts are in the explorer and nothing is in the main pane. Clicking down into the colums, right clicking on stuff, etc. makes no difference. That’s strange to me. There is no “browse data” function, but if you squint and carefully notice there is a little “scrapbook” icon on the main tool bar: . If you click that you will get query window of sorts that you can now use to do stuff. Pick off your database again and you can write a query. Results appear down in the window at the bottom crammed in there. I could not figure how how to make that window bigger.

The query functions are pretty bare boned, but there is color highlighting and decent “code assist” functions. If you are into manual query writing then you should be set with this tool. There is no ability to browse and update data directly without writing SQL queries. DTP is pretty fast on my machine with MySQL. you’ll notice that I have an Oracle DB connection too…that is definitely NOT fast…painfully slow. Neither database is on my local machine.

Overall DTP does work and you can get going right away once you figure out the quirks in the UI. The best thing I can say for DTP is that it runs in Eclipse. If you use Eclipse then its a decent deal. The other benefit is that it runs everywhere Eclipse runs and that is a lot of places.

Oracle SQL Developer 1.2

It sure seems odd to me that Oracle would make a developer tool for MySQL people after all isn’t open-source a major threat? Well I guess we don’t need to think about it too much here, but suffice it to say that SQL Developer is pretty nice. There is a fantastic guide to installation of this tool here. Roland has a lot of good points too on the product. If you’re stuck follow his install instructions – mine are pretty spartan in comparison.p

Installation of the basic product is straightforward. As with DTP you need to get the MySQL drivers and install them – see Roland’s guide. You also need to make sure the account you are connecting with has a true “host” associated with it just like DTP (FYI – it is a MySQL JDBC quirk not a problem with either tool).

Once you clear up these issues you will have a simple, clean, and intuitive interface for doing basic SQL programming tasks with MySQL:

In this view you can see the basic layout of db objects on the left. Click on something to see more details in the pain on the right. Its easy to step in and use this tool. Performance is pretty nice too – shows that a Java app can be fast if you build it right. As in the above shot if you click on a table you can see details on the columns or click the data tab to see the data. You cannot update data from this view however.

In the next shot you can see the query editor and color highlighting. I didn’t see any “code assist” features as I was doing things.

Overall SQL Developer is a surprisingly decent tool (sorry Oracle if I sound shocked). It is Java so it runs on a lot of platforms, performs well, but overall its a little light on features with no code assist and really only the ability to browse tables and write SQL. It also has a ton of greyed out tabs that I suppose are targeted at pointing out all the goodies you could have if you were using an Oracle RDBMS. The UI is nice too – very easy to use. Additional enticements for migrating MySQL stuff to Oracle are a heavy feature…and now we know why they are making this… 🙂

Quest Toad for MySQL 3.1.0.325

So guess which I like best? Well hey its hard to put a cliffhanger in a silly blog post, but yes I find that Toad is the killer MySQL tool. The product installs everything you need in one shot, connects to MySQL right away (no screwing with user accounts), and you are presented with a very nice intuitive UI that supports a full range of features:

The UI is very intuitive. Just click across the tabs above the display area to see everything about this table. Need a SQL Editor? The icon is right on the toolbar. Pretty slick. Full color highlighting and code assist features are there as well.

Here’s my favorite part. If you look at the above shot you’ll see I’m looking a table’s data view. I can type in the field and it will actually save the change back to the database. This is awesome! Why? Because if you want to test weird funky values and see what happens back in forth in your app, then this is how you can easily do it without coding SQL all day long. Very very cool. Also notice the data browsing buttons at the bottom – nice. Its also nice for fixing data problems when in production.

Unlike the other tools, Toad goes well beyond the mark and offers editors and support for procedure/function coding, trigger coding, and easy export script generation for tables and well everything. I could go on about visual query builders, object browsers, expert analyzers, etc., but I hopefully you get the point. This is essentially their full-blown product tied down to MySQL. Like Oracle they have a play here: get you to like it enough so that if you at some point start using Oracle then you will insist on it.

Ok – this is too good, what’s wrong? Ok well – Toad for MySQL is a Windows only product. D’oh…well for me this is ok because I do most of my development on a Windoze laptop, but it is a bummer that you can’t run this on linux too.

Conclusion

Toad is a no-brainer. It just has too many features the other guys don’t, but the other tools are still good. SQL Developer is good if you are primarily an Oracle shop, but also occasionally using MySQL. If you are a heavy Eclipse user and you must have cross-platform tools then Eclipse is ok, but it is a little quirky and lacking in features. On the good side Eclipse supports a slew of databases and not just MySQL or MySQL + Oracle.

Hopefully this was useful for you. Please post any comments/questions/corrections.

Update 18-Jan-2008: Please check out my update. Since my original post things have changed…