Follow Us

We use cookies to provide you with a better experience. If you continue to use this site, we'll assume you're happy with this. Alternatively, click here to find out how to manage these cookies

hide cookie message

Apps

Software

MySQL 5.0 open source database

Article comments

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.



Share:

More from Techworld

More relevant IT news

Comments

Send to a friend

Email this article to a friend or colleague:

PLEASE NOTE: Your name is used only to let the recipient know who sent the story, and in case of transmission error. Both your name and the recipient's name and address will not be used for any other purpose.


Techworld White Papers

Choose – and Choose Wisely – the Right MSP for Your SMB

End users need a technology partner that provides transparency, enables productivity, delivers...

Download Whitepaper

10 Effective Habits of Indispensable IT Departments

It’s no secret that responsibilities are growing while budgets continue to shrink. Download this...

Download Whitepaper

Gartner Magic Quadrant for Enterprise Information Archiving

Enterprise information archiving is contributing to organisational needs for e-discovery and...

Download Whitepaper

Advancing the state of virtualised backups

Dell Software’s vRanger is a veteran of the virtualisation specific backup market. It was the...

Download Whitepaper

Techworld UK - Technology - Business

Innovation, productivity, agility and profit

Watch this on demand webinar which explores IT innovation, managed print services and business agility.

Techworld Mobile Site

Access Techworld's content on the move

Get the latest news, product reviews and downloads on your mobile device with Techworld's mobile site.

Find out more...

From Wow to How : Making mobile and cloud work for you

On demand Biztech Briefing - Learn how to effectively deliver mobile work styles and cloud services together.

Watch now...

Site Map

* *