MySQL is most often associated with web development in the
Linux environment. It is the “M” in the acronym LAMP (Linux operating
system, Apache web server, MySQL database, and Perl / PHP / Python scripting
languages).

But many people don’t know that MySQL is also available on
Microsoft Windows and Mac OS X platforms. In fact, a recent survey of MySQL
customers revealed that 58% of them have at least one instance of MySQL in
production on a Windows server. And the Windows version includes automated
installation and configuration tools–not found in other versions–that make it
easier to deploy and use.

Because source code is available, MySQL is usually described
as open source. MySQL is actually available under both free and commercial
licenses. MySQL is licensed under the GNU Public License (GPL). This means that
companies may use it in-house, base applications upon it, and even modify it
for their own use, without paying a fee or being required to contribute their
own code to the public. They may also use it as the backend storage for a web
site under the GPL. If, however, they sell or distribute their code to others,
they can open their own source code to the community as the GPL requires, or
purchase a commercial license.

Stored program objects

Probably the most significant addition to the product in
version 5.0 is support for stored program objects: views, stored procedures,
functions, and triggers. These features promote code standardization and reuse.
They also simplify security.

Views

A view is a virtual table: a SELECT statement with a name.
Microsoft SQL Server calls them views as well; Microsoft Access calls them
queries. Selecting from the view name executes the underlying SELECT statement,
and returns the results as columns in the virtual table. MySQL views may be
read only or updateable. A check option can be specified to prevent views from
being updated with rows that they cannot themselves SELECT.

Stored procedures

Stored procedures are created via the CREATE PROCEDURE
statement, and executed via the CALL statement. They may include input, output,
and input-output parameters. MySQL stored procedures follow the SQL Server
model, which permits a rowset to be returned simply by including a SELECT
statement in the procedure. Unlike SQL Server, however, stored procedures in
MySQL are not compiled. They do share many of the same advantages, such as
standardizing code and reducing network traffic by performing business logic
within the server.

A single statement such as a SELECT is coded immediately
after the procedure declaration. Multi-statement procedures must be bracketed
by BEGIN and END statements. Conditional statements available include a simple
IF – THEN – ELSE and a searched form of CASE (all the cases are values which
are matched to determine which case to execute). Loops include WHILE (tests at
the top of the loop), REPEAT (tests at an arbitrary location where an UNTIL
statement is placed), and LOOP (repeats forever until exited via a LEAVE
statement). Exception handlers can be defined to catch and process errors.

As in SQL Server, granting EXECUTE privileges on a stored
procedure grants implicit permission to access objects used in the view. This
means a single EXECUTE privilege can take the place of many individual grants
on individual tables. This is important, as MySQL does not as yet have a roles
model to aggregate permissions.

Functions. Stored functions, like stored procedures, are
objects in the database. The difference is that they return a result value
which must be consumed by the calling program in some way.

Triggers

Triggers are event-driven stored procedures. They are tied
to a specific table, and to an event on that table (INSERT, UPDATE, or DELETE).
When the event occurs, the trigger is executed (or “fired”.)

One key difference between MySQL triggers and those in SQL
Server is that MySQL triggers can be called either before the triggering action
or after it, whereas SQL Server triggers are after only. SQL Server does have
an INSTEAD OF trigger not present in MySQL. Another key difference is the FOR
EACH ROW syntax in MySQL, that will cause the trigger to execute for each row
modified. The prefixes “OLD.” and “NEW.” enable the trigger
body to reference columns before or after being modified. SQL Server triggers
execute once per statement, and must take into account the possibility of
multiple rows being affected.

Storage enhancements

Datatype changes in this release include extending the
VARCHAR to a maximum of 65,532 bytes, and the addition of a new BIT datatype.

MySQL’s architecture uses plug-in storage engines to
implement the physical storage of database tables. Each table may use a
different storage engine; each engine provides a different set of features. The
default storage engine, MyISAM, is very fast but does not have the ability to
capture transactions; the InnoDB storage engine features not only transactions,
but also row-level locking and declarative integrity constraints. It follows
the Oracle model of non-blocking reads. The InnoDB engine uses a more compact
storage format than previously. MySQL AB estimates that simply migrating 3.x or
4.x InnoDB tables to version 5.0 can save 15-20% storage space.

Version 5.0 adds two new storage engine types to the
product: Archive, and Federated. The Archive storage engine compresses all data
stored using it, to save still more space. What’s really unique about this
engine is that it only implements two DML commands: SELECT, and INSERT. Data
goes in, but it cannot be updated or removed without changing what engine the
table uses. This should make it very useful in archiving records for regulatory
compliance with laws such as Sarbanes-Oxley and HIPAA.

The Federated storage engine enables access to remote
tables, similar to a linked server definition in Microsoft SQL Server. In this
release, the engine supports linking to other MySQL databases on the same or
different operating system platform. Future releases may provide support for
non-MySQL links.

Administration tools

In the past, MySQL development and administration has been
done either at the command line, or by using third party tools such as phpMyAdmin. With the 5.0 release, MySql now
includes a set of graphical user interfaces for common administration and
development tasks. These are similar to those found in Microsoft SQL Server,
although being their first release, are not nearly as comprehensive. Still,
they do make basic administration tasks easier by hiding the underlying SQL
syntax under a façade of form controls. The tools include:

  • MySQL Instance Configuration Wizard – This
    tool is currently available only in the Windows version. It is automatically
    invoked after installation, but can also be run from the Start menu. As the
    name implies, this tool is a step-by-step guide to configuring an instance of
    MySQL. Specifically, it creates the my.ini file, a text file containing startup
    configuration parameters.
  • MySQL Query Browser – Available for
    Windows, Linux and Mac OS X versions of MySQL, this tool can be used to build
    queries and test them. It’s similar to Query Analyzer in SQL Server 7.0 and
    2000, with a schemata browser
  • MySQL Administrator – This console is
    also available for Windows, Linux, and Mac OS X. It lists all the installed
    MySQL services and the database catalogs each contains. Common administrative
    tasks such as creating, altering, and dropping tables in a database can be
    performed visually via the Table Editor. Indexes and constraints such as
    foreign keys can also be defined here.
  • MySQL System Tray Monitor – Similar to
    the Service Manager in SQL Server 2000, this tool puts an icon in the Windows
    SysTray to display the status of the MySQL Instance. Right-clicking the icon
    brings up a context menu that enables starting or stopping the instance, or
    opening tools such as MySQL Administrator or MySQL Query Browser.

Another manageability tool is the INFORMATION_SCHEMA
database, one of three standard databases in a default MySQL 5.0 installation.
INFORMATION_SCHEMA is defined by the ANSI SQL standard as a way to access
metadata (definitions) about the objects in a database. The various views in
this schema enable queries to obtain a list of tables, views, columns,
triggers, and other objects. For example:

 

SELECT table_name from INFORMATION_SCHEMA.TABLES

WHERE table_schema = 'test';

will display all the tables defined in the test database.

Support and certification

For open source products like MySQL to succeed in the enterprise
market, they have to have more than low cost on their side. Businesses have
been reluctant to trust their systems to products that have only a community
bulletin board for support. It is the number one concern of businesses
regarding open source products, according to a Forrester Research report
released in January 2004.

As a result, the major Linux vendors have developed support
programs on a subscription basis, such as Red Hat Network for the Red Hat
flavor of Linux. MySQL has done the same, with its MySQL Network subscription.

MySQL Network includes a stable, less-frequently released
edition of MySQL called Pro Certified Server; access to a Knowledge Base
online; and various levels of production support (Basic, Silver, Gold, Platinum
levels). MySQL AB is also a member of the Technical Support Alliance Network
(TSANet), which networks MySQL support engineers with those in other companies
such as Red Hat, Microsoft, Cisco, Dell and HP to help solve complex issues
involving multiple vendors.

Just as businesses need a support system for the software
they purchase, they also need tools for IT skills management. With commercial
vendors, the primary tool to date has been some form of certification program:
a series of examinations that test a candidate’s knowledge and skills with a
particular software product.

Certification programs have not caught on as well in the
open source community as with proprietary software vendors. Discussions
critical of “paper” certifications are staple fare on community sites
such as Slashdot. However, certification
programs are more common in larger firms. MySQL created certifications at the
4.0 level, and has updated them for the 5.0 release to be more aligned with an
IT skills management focus.

Whereas the old exams focused on product features (MySQL 4
Core, and MySQL 4 Professional), the new exams are mapped to job function
(MySQL 5 Developer, MySQL 5 Database Administrator). The skill sets tested
relate to these job functions: for example, the Database Administrator exam
covers topics such as installing MySQL, using its performance monitoring tools,
and doing backups.

Even more in 5.1

Even more enterprise-related features are available in beta
release for version 5.1. Although the exact mix won’t be known until the
Generally Available (GA) release, here are some of the more significant ones in
the beta:

  • Table partitioning – MySQL is following the Oracle model here,
    enabling a single table to reside over multiple tablespaces using a relatively
    transparent partitioning structure. Five different partitioning algorithms are
    planned for the 5.1 release: by range, by hash, by key (similar to hash, but
    with system generated identifiers), by list, and composites of these.
  • Background scheduler – Similar to SQL
    Server Agent or Oracle’s Job Scheduler, this tool will enable automatic
    execution of SQL commands at regular intervals. This will ease regular data
    loading and administration tasks, now done via scripts written in languages
    such as Perl and executed via operating system schedulers.
  • XML support – XML, which has been notably
    missing from MySQL to date, will gain ExtractValue() and UpdateXML() functions
    in 5.1. These functions will use XPath expressions to retrieve any specific
    fragment of an XML-formatted document, or to replace one XML fragment with
    another one.
  • Load emulator – The mysqlslap program
    will enable developers to simulate large user loads on their database
    configuration, to test whether the application as designed will scale to production.

The Bottom Line

MySQL 5.0 is a very significant release for MySQL AB.
Although some of its features are not as comprehensive as DBAs and developers
are used to in other platforms, it shows the company’s intent to play in the
same space.