Long-awaited MySQL 5.0 makes its debut

MySQL 5.0 has finally been released to much anticipation from the open source community. The new version includes some important enhancements that make an upgrade mandatory for 4.1 users, including improved security and stored procedures. All in all, MySQL 5.0 is a respectable open source database, but it’s still a few steps below the “big four” databases (IBM DB2, Microsoft SQL Server, Oracle, and Sybase) when it comes to management and development tools.

Free IT resource TechNet: More ways to know it, share it, and keep it running. Sponsored by Microsoft Free IT resource Attend the SOA Executive Forum: Breaking SOA Bottlenecks SOAExecForum.com/may2007 Sponsored by InfoWorld MySQL 5.0 MySQL, mysql.com Good  7.7 criteria score weight Management 7 25% Performance 8 25% Availability 8 20% Scalability 8 20% Value 7 10% Cost: Free Platforms: Linux; Solaris, HP-UX, AIX, SCO Unix; FreeBSD; Mac OS; Windows 2000, 2003, XP; Novell NetWare Bottom Line: The long-awaited MySQL 5.0 release performs very fast and scales well. Despite a couple of management bugs, stored procedures perform well and are quite stable. Views and triggers are comparable to those of the big four database vendors (DB2, Microsoft, Oracle, Sybase). Unfortunately, the management and development tools are still rather basic and leave a lot to be desired. About our Reviews and Scoring Methodology

Upgrades All Around
There are four major new features in MySQL 5.0: stored procedures, views, security upgrades, and triggers.

Stored procedures are a database standard that MySQL is only now taking advantage of, and it’s a good move. Stored procedures allow you to define pre-compiled SQL code that is stored inside the database and can be called with parameters by anyone with permissions; they can be long and contain very complex logic.

Interestingly, the stored procedure implementation in MySQL 5.0 is different from that of other databases: MySQL’s stored procedures aren’t precompiled; they’re just stored in the database, making them closer to named queries in Excel than traditional stored procedures.

Because of this lack of precompiling, the performance benefits you see from stored procedures in MySQL come from reduced network traffic. The stored procedures were generally stable in my tests, but this being MySQL’s first implementation, there were a few bugs. For example, I had some trouble with the server freezing up when I tried to open some of my larger procedures. Although not a show-stopper, this behavior could be a problem if you have a busy database.

Another database standard, views, are also new to MySQL 5.0. A view is a stored query that doesn’t accept parameters, and one of its biggest benefits is obscuring query logic from users. An admin could define a view with a very complicated where clause and multiple joins, but the end user would simply select the query from the entire view without needing any knowledge of the underlying tables. MySQL’s implementation of views is fairly complete, and, in this case, the functionality is similar to any other database.

MySQL did enhance security in an unexpected -- but pleasantly surprising -- way. In previous releases, a hacker or rogue system admin could gain control of your MySQL database by using Notepad or any other text editor to view the file that stored the user accounts and manually change information; he could also access the table itself to alter data or create and delete tables.

Thankfully, this is no longer possible in MySQL 5.0, as log-in accounts are now stored in the database account. This change greatly increases my comfort with implementing MySQL in a production environment, as I’m sure it will for many other MySQL admins.

The fourth new feature, triggers, works very much as they do in other databases. Triggers are event-driven stored procedures that are attached to a specific table; the trigger code will fire for any write, even one that occurs on that table.

Again, the triggers have a couple of bugs due to this being the first implementation by MySQL. The two most significant trip-ups are that a MySQL 5.0 table’s trigger isn’t dropped when the table is, and altering a table with a trigger on it can cause database corruption. You can avoid these situations, but be aware of them before you implement triggers in your code. MySQL also doesn’t support “instead of” triggers like the major databases -- not a huge shortcoming, but it would be a nice tool to have.

Database Determination
So, where does version 5.0 put MySQL in the global database market? MySQL has made tremendous strides in bringing its database to standard, but it's only now implementing features that the big four have had for over a decade. MySQL also doesn’t have integrated Windows security, something I consider a prerequisite for running a database on Windows with any level of safety.

MySQL 5.0 comes up short in the administration and development department, too. It has no debugger, so finding mistakes in long code requires trial and error. The admin utility freezes up a lot and has trouble refreshing. It provides simple performance stats, but it doesn’t install any performance counters. The development utility has no editing features and also suffers from quirky performance; it provides few useful error messages, so the engine doesn’t offer much error-finding help.

Of course, MySQL is a nice alternative to other vendors’ high license fees, and as a quick way to get a Web site or other simple app up and running, MySQL 5.0 is the perfect solution. Being free, however, doesn’t give MySQL an automatic pass on value. If you need a quick, free database, both Microsoft and Oracle have one, but the size is capped at 4GB; for larger databases, SQL Server Workgroup edition comes in at under $1,000. All of these databases are stable, offer more features, and have a clear upgrade path.

MySQL 5.0 may not be on the same competitive enterprise playing field as the established big four databases yet, but MySQL admins would be foolish not to implement version 5.0. The increased security, and with the inclusion of stored procedures, triggers, and views, make this a necessary, and welcome, upgrade.