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…

11 Comments

  1. Plutarch

    http://blogs.mysql.com/kaj/2008/01/16/sun-acquires-mysql/

    Sun just bought MySQL. Thoughts? Good/bad?

    Reply
  2. berenerchamion (Post author)

    I would say not good – but I suppose it depends on what they are going to be doing with it. In general I think Sun is the kiss of death for most things.

    Not that surprising – many of the best open-source companies are getting bought up by corporations: Zimbra, Xensource, JBoss, SuSe, etc.

    I think the drivers are different for each case…for example Yahoo is trying to leverage Zimbra to build a more commercial footprint for email services. Citrix is fighting off EMC in the virtualization space with their Xensource purchase.

    beren

    Reply
  3. berenerchamion (Post author)

    A note of caution – although Quest has released this product as freeware there are some issues. My copy has now “timed out” and I cannot get it to run anymore. I’m not sure yet if this is me or something with the software.

    This problem has opened a real weakness with the product – a complete lack of support from Quest and a total lack of a community either. Their forums are desolate and their yahoo groups are non-responsive.

    I’ll update the post with what happens – it is certainly possible that the problem I’m having is related to Vista (yes its true Vista sucks – but what can you do when your company insists on it?).

    Reply
  4. Pingback: Tylenol Please - My “no brainer” is giving me a headache… « Tarn Aeluin

  5. Dwight Fowler

    Tarn,

    I’d like to introduce myself. My name is Dwight Fowler. I am the project leader and one of the developers of Toad for Mysql. I think you might have found Toad for MySQL’s one Achilles’ heal: Windows Vista. The explanation for what happened to you is long and involved, but it boils down to this: Toad for MySQL (TfM) is one member of a software family. It grew out of a shared code-base that has licensing built into its core. We unshackled TfM, but discovered early last December that we did not cut all the chains. There is an issue with Vista (big surprise, eh?). I want to let you know that the time-out bug only happens on the Vista platform. I am sorry you had to run into it. It’s tricky and our licensing guru is trying to tease out a fix for it.

    Your experience with our user group is my fault. I went on vacation right as you were experiencing your problem. Plain and simply, I “flaked out” for four days and did not check the group. I truly apologize for leaving you hanging. This really isn’t the kind of experience that we want any of our Toad users to have. You guys are great. The info and suggestions that you give us are like gold. We can’t really do it without you. I know that when you are excited about a product and really want to see it work, encountering a ‘no-go’ scenario is a major joy kill. It wont happen, again.

    We probably have two strikes in your book, but I hope you will be willing to give us one more try. We are testing a fix for TfM on Vista (http://usdownloads.quest.com.edgesuite.net/Repository/www.toadsoft.com/MySQL/ToadForMySQLFreeware_3.1.0.433.zip), and we need Vista users to help us check it. We want to make sure it works on all possible Vista configurations. Your thoughts and suggestion *do* make a difference, and we need all the sharp-minded users that we can get.

    With apologies,

    Dwight

    Reply
  6. Anif™

    Hi

    I tried this last week myself and ran into the same issue. First time, it did not complete install, but oddly said install was complete. When I tried to launch it, it said the product has expired….made no sense. Second time, it just rolled back on Vista, and did not complete installation.

    I know that TOAD is a probably the best GUI tool on their, cos I use it for Oracle. But I’m doing some MySQL work on the side which I wanted to test with TOAD for MySQL since I can’t find a good GUI tool for it.

    Reply
  7. berenerchamion (Post author)

    Be sure to check out my follow up article Tylenol Please…My No Brainer is giving me a Headache…; also there is version 4 beta out. I find that works good on Vista.

    beren

    Reply
  8. Anif™

    So far Dwight, that fix seems to be working fine on my Vista machine…..but it is still less than 24 hrs, so I’ll see if anything happens here on.

    Reply
  9. Dwight Fowler

    I’ve got an update for all of you that are running into the Windows Vista bug with Toad for MySQL version 3.1. There is a #newer# patch that is still being tested at:

    http://www.toadsoft.com/toadmysql/FreewarePatchDownload.htm

    This patch should eliminate the freeware time-out issue that occurs with
    Vista and another bug that occurs when you try and edit data in the Database Browser. Thanks to everyone for all your help.

    Dwight

    Reply
  10. Alka

    You are wrong when stating that

    “There is no ability to browse and update data directly without writing SQL queries.”

    Just Right-click on a table in the browser, and select Data/Edit. In the grid, you may modify existing data and add rows etc.

    Good article all in all though!

    Reply
  11. berenerchamion (Post author)

    Hi Alka,

    When I right click on a table in the Database Development perspective I have only one entry on the popup menu: “refresh” so I think I’m correct, but would love to be proven incorrect. Am I doing something wrong? Perhaps we are looking at a version issue where some new features are in once version of DTP and not another? What version do you have?

    Reply

Leave a Reply