About the Author

Arjen Lentz is a former Community Relations Manager at MySQL. He is currently the owner of Open Query and lives in Brisbane, Australia.

We are always looking for interesting articles about MySQL! Have you written something and would like to it published here? Please contact us via feedback form »

Evaluating MySQL 5.0 Key Features - Helping the QA Process

By Arjen Lentz

As I'm sure you're aware, MySQL 5.0 is currently in its beta phase. It may appear obvious, but software does not magically become stable (production quality) just by waiting a while! Next to our crack QA (Quality Assurance) Team, the QA process relies on you, the many users of the MySQL software. Since 5.0 adds an extensive set of new features, lots of testing is vital. We want to make sure that MySQL 5.0 attains the highest quality, as soon as possible.

Of course we have quite a few tests to check basic functionality, but nothing beats the real world! Each of you runs MySQL in a slightly different environment, be it in terms of operating system, or simply the way your application code uses the MySQL Server.

So, our chief QA engineer Omer BarNir would like to ask you to assist the QA process by testing some specific key features in 5.0: stored procedures, views, triggers, and also the data dictionary. You may already be familiar with similar features from other RDBMSs, but just in case, this article also provides a bit of an introduction with links to where you can find lots of additional information - everything to get you off to a good start!

Now, we aren't suggesting you immediately start using MySQL 5.0 on your production servers - it's not yet ready for that. But perhaps you can install MySQL 5.0 on some of your development machines? This also gives you a nice head start for when MySQL 5.0 goes production.

If you operate a larger installation with a replication setup and multiple slave servers, one cool way of testing new MySQL versions is to just run it on a few of the slaves. Then you can see how it performs relative to the older version, and if something goes wrong, your load balancing setup will ensure that your system as a whole will remain functional.

If you can do something like this, that would be fantastic! But different/smaller environments are very useful too. Any contribution to the MySQL 5.0 QA process is greatly appreciated. See also the MySQL 5.0 Beta Challenge at the end of this article!

Helping the QA Process

You will need to have a MySQL 5.0 server installed and running on your system. Binaries for most platforms are available, including Windows, Mac OS X, Linux and most other Unix including various 64-bit varieties. If you haven't got MySQL 5.0 yet, you can download it from here: http://dev.mysql.com/downloads/mysql/5.0.html

It is possible to install MySQL 5.0 alongside an existing other version without any conflicts. On Windows this is very easy, simply make sure you choose a different service name and port number. On Unix, the easiest way is to use for instance the RPM install for production, and a binary tarball install for testing. The directory layout is completely different and keeps everything separated.

You can test the new features by for instance checking the syntax in the MySQL Reference Manual and just trying some things - if you get the syntax wrong somehow, that's still a good test: MySQL should report an appropriate error. If, for any reason, MySQL crashes, you've definitely found a bug! But any other kind of incorrect or random behaviour can point to bugs too.

You can report bugs via bugs.mysql.com. This is very important. Simply spotting a bug is one thing, but our developers can only fix it if they know about the problem. The MySQL bugs system is the place for this. Just sending an email is not, because it's unstructured (some vital information may be missing) and tracking may not be possible. See also the reference at the end of this article for more information about reporting bugs in MySQL.

Another way to test things could be to try out some SQL that was originally written for other RDBMS. Particularly with stored procedures and views this may be very effective, as you would be able to create slightly more complex test cases with relatively little effort. Perhaps you have some SQL test/validation suite or benchmark code that you can try, too.

It would be great if you could try the new features in any existing or new applications that you are developing. Either just code for the new version, or check the MySQL version in your application (you can use the SELECT VERSION() SQL command), and choose an appropriate execution path. You may currently do some processing inside your application that, with MySQL 5.0, you could do via a stored procedure. Likewise, a VIEW could replace multiple separate queries.

I'm sure you get the idea. Be creative! And by all means, try weird things. The real world is like that, we want to test as many situations as possible. If you have any questions, I've also listed links to online forums for each of the features. Experienced users as well as MySQL developers are there to help.

What do you get out of all this? A new MySQL production release of excellent quality - and best of all, you will know beforehand that your application works well with MySQL 5.0 when it gets to the production stage, because you've already tried it in your particular environment. And along the way you've updated your own skills, becoming familiar with the new features.

I'll also make sure that users who are particularly helpful to the QA process receive appropriate recognition. See the MySQL 5.0 Beta Challenge at the end of this article. Credit where credit is due - this is a community effort, and we are very grateful for your assistance!

Now, let's take a quick look at the individual features that we would like you to check out.

Stored Procedures

MySQL 5.0 implements stored procedures and functions according to the SQL:2003 standard. These are blocks of SQL commands, which you can define once and store inside the MySQL server. Then you can call them from your application when you need them, either as a separate command (procedures) or from within another query (functions).

The main difference between a procedure and a function is that a function always returns some value. But you can also use output parameters to retrieve data from a stored procedure, or even have it return a complete additional result set to your application!

In the current implementation, no external languages can be used. So it is pure SQL:2003 syntax, but there are a number of conditional, iteration and error handling constructs available.

Note: There used to be another limitation, a stored function could not access any table. That limitation was resolved in March 2005.

Commonly, some business logic is put into stored procedures for security or portability reasons. You could give a user the ability to call a stored procedure, and not give direct access to any of the underlying tables.

Views

A view is like a table. The contents of this table are defined dynamically from a SELECT statement. Views can be processed using MySQL's temporary table algorithm, or act as a kind of macro so that when you access a view, your query is actually transformed inside the server to incorporate the view definition. In the latter case, a view can be updatable - that means that you can even use it with INSERT, UPDATE and DELETE commands! Of course, it is physically impossible for a view to be updatable if you use certain grouping or aggregate functions in the view definition, as the result would not refer to a single row.

Applications often repeat similar joins in different queries. By defining a view, you can make your application code easier to read and maintain, particularly when queries get more complex. Views can also be used to keep a legacy application happy, while you have actually changed the structure of the underlying real tables. Last but not least, views allow you to implement row-level security: you could give an account manager access only to customer records that have a certain area code, or are designated to that particular account manager.

Triggers

A trigger is an SQL command (or block of commands) that is "fired" when a particular operation (such as an UPDATE or DELETE) occurs on the table to which the trigger belongs. The trigger can access the original (old) as well as the new data for the row in question.

Now for the bad news: triggers cannot yet access other tables. Our developers are working hard to lift this restriction also. So at present, triggers are quite limited in functionality and we appreciate that you will have less use for them right now. Still, they can do some handy things already, and when the limitation is lifted you can dive right in!

Data Dictionary (INFORMATION_SCHEMA)

I am noting this feature here primarily for completeness, as it can come in handy when using the other features. If you look at the list of databases in MySQL 5.0, you will spot a new database called INFORMATION_SCHEMA. It contains a fair number of tables, each providing details of specific aspects of the MySQL server, table definitions, and so on. Basically, lots of metadata, which you can now access through queries (including with joins!)

The INFORMATION_SCHEMA database, another SQL:2003 feature, does not actually exist in a physical form: the server dynamically presents the information. In previous versions of MySQL, some of this data was already available through the various SHOW commands - however, it was not possible to use those in regular queries with joins. That's the real power of INFORMATION_SCHEMA.

Additional Resources

This article provides detailed information about the MySQL release process, version naming, and how best to report bugs:
Choosing the Right MySQL Version and Reporting Bugs.

There's also another external resource with lots more information about the features discussed in this article: http://mysql.gilfster.com/. This site is maintained by Andrew Gilfrin, a very active and helpful MySQL Community member - thanks, Andrew!

The MySQL 5.0 Beta Challenge

There will be cool MySQL goodies (T-shirts, mugs, books) for users who are the most helpful either directly with 5.0 testing, or indirectly by informing other users about 5.0. For instance:

The best or most (5.0 feature related) bug reports at bugs.mysql.com
The most helpful people on related MySQL forums at forums.mysql.com
The best MySQL 5.0 related articles (magazine or online) or dedicated web site.
The most informational/inspiring blog entries on MySQL 5.0 topics.

To tell us about published articles and web sites or your blog feed URL, use the community feedback form.

Rules:

All bugs reported, articles published, etc by July 30th (note: this date has no relation to the 5.0 release schedule.)
All winners announced at the sole discretion of MySQL AB. Arjen Lentz (Community Relations Manager) and Brian Aker (Director of Architecture) will be among the judges.

Your contribution is highly appreciated! Go for it & good luck.

Update 25 October 2005

Roland Bouman is the winner of the MySQL 5.0 Beta Challenge, for his excellent Diagram of the MySQL INFORMATION_SCHEMA.