MySQL 5.0 open source database

MySQL is by far the best known, and most popular Open Source database engine. It ships with most Linux distributions (not to mention commercial Unix incarnations such as Mac OS X) and has become immensely popular over the years because it's an ideal way to run a decent-sized database at minimal cost.

As a developer, though, I've had to work around some fundamental flaws over the years. My main gripe about MySQL so far has been that it hasn't supported stored procedures. These are user-defined procedures that you build into the database itself and then call from client-end applications – so you define stuff once and can then call it from a number of client platforms. Buzzword enthusiasts call this a "multi-tier" approach; think of it as "write once, use many".

My other moan has been that you couldn't create views ("virtual tables" that provide a window on to real tables and make the developer's job easier). Version 5 solves these problems – and brings a bunch of other new features too,

The package is available in binary form (i.e. as a simple installer) for a shedload of platforms: Linux (on various architectures, not just x86), Solaris, FreeBSD, Mac OS X, HP-UX, NetWare, SCO, Windows, the list goes on. It's worth mentioning that the Windows release, which used to be a bit of a kludgy afterthought, is now done properly – so MySQL runs as a service and you get a proper control applet for starting/stopping/configuring it.

Installation is a simple job of running the installer (e.g. on Windows) or adding the package with the OS's in-built package manager (e.g. "rpm" on Red Hat Linux). It's no great problem to install 5.0 over a previous version – on my Windows XP development machine it happily opened the databases from the existing 4.1 installation.

Because MySQL stores its system information in a set of tables of its own (actually a database called "mysql") you have to update these tables to make them compatible with the new version, but this is a simple case of running a script that's provided with the system (hint for Windows users: make sure you tell it to install the optional "scripts" component, or the script you need won't be installed!). Oh, and if you're a replication user, beware that a pre-5.x slave can't talk to a 5.x master.

Once the package is installed, you can use it via the provided command-line utility or, if you're like me and get fed up with the limitations of a command line, via a third-party GUI tool (I use MySQL Query from http://www.mysqltools.com/). Aside from a few proprietary commands for creating databases and the like, you'll mainly be typing SQL commands at the system, so in that respect it's not really much different from a lot of DBMSs.

We've mentioned already that stored procedures new to MySQL. Actually you have both stored procedures and functions: these are very alike with the exception that a stored procedure can have both "input" and "output" parameters but no return values, whereas a function has only "input" parameters but returns a value and can thus be used in an assignment statement (of the form "x = myfunction(param1,param2)").

We've also mentioned that views are now supported – although not quite completely (e.g. you can't use subqueries in the FROM clause yet). It's amazing that they haven't been included before, though I guess that they're more of a convenience than a necessity – that is, the developer has always been able to program around the omission, albeit with the result that queries have been more complex.

Other new bits in 5.0 include a BIT data type (so you can represent binary properly instead of having to use an eight-bit INT or similar), server-side cursors (for iterating data retrieval within a stored procedure), a more standard way of digging into the database schema (in the form of the INFORMATION_SCHEMA object), improved precision mathematics support (particularly with regard to small decimal fractions, which are always a pain in the arse to implement electronically), and a couple more storage engine types ("archive", a write-only, unindexed repository, and "federated", for access to remote databases). My favourite data type change, though, is that the VARCHAR type now has a sensible length limit of over 65,000 characters, rather than the previous 255 – and about time too.

And the list goes on. Triggers are supported for the first time (a trigger is an action which "fires" when the object it's related to is amended), albeit to a limited extent. XA (distributed) transaction support is also there for the first time. Finally, they've worked on the query optimiser quite a lot, and have made some changes which, on paper at least, look eminently sensible (the nature of query optimisers is, I find, that only time and esoteric applications will tell whether it's improved performance in the average case, but it looks OK so far).

Interfacing to MySQL in client-server applications has traditionally been very simple, and nothing's changed in this respect. ODBC/JDBC are a no-brainer, and for a while now there has been a native .NET interface to keep Windows developers like me happy. No problems for developers in this respect, then.

MySQL 5.0 is a great deal more functional than its predecessor. A cynic like me would probably rant on that some of the new stuff is long overdue, but to be fair at least they've got there in the end. The query optimisation still has a few little foibles (it seems to dislike queries where you join a table to itself, for instance), but does genuinely seem better than the one in 4.x.

Let's be fair, though. MySQL is free, supports advanced concepts such as replication, and performs extremely well in the average case. It's always been popular, and version 5.0 fills a few glaring holes in 4.1. The developer interfaces are there, and the documentation (not to mention feedback in the real world via Web sites and the like) is very, very good.

Just a quick word of warning, though: keep an eye on the release notes on the MySQL Web site. Changes are already creeping into "minor release" updates – for instance, triggers weren't in 5.0 but appeared in 5.0.2. So keep an eye on the precise release number you've downloaded, and bear it in mind as you work through the documentation.