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.

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.