Open Source

What's New In MySQL 5.0

MySQL is an open-source database that's increasingly being used by businesses to build mission-critical applications around. Here's what you'll find in the latest release of MySQL, MySQL 5.0.

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.

0 comments

Editor's Picks