MySQL, today’s contender

In May 2003, I wrote an article entitled MySQL–Yesterday’s
Toy, Tomorrow’s Contender
. I followed it up a year later with MySQL,
Still tomorrow’s contender?
Two years have now passed, and, since it is May
once again, I thought it is time to follow up and see where MySQL stands today.

With MySQL 5.0 now well established, the prevalence of the toy database
brigade has greatly diminished. MySQL has a rich feature set catering to a much
greater proportion of users, and recent moves by Oracle have shown that they
too take MySQL seriously.

MySQL’s roadmap 2003-2006 – how did they fare?

So how did MySQL fare in adding the new features they had aimed to over the
last few years? Or put another way, absolving MySQL of all blame, how accurate
were my predictions?

Of the enhancements laid out in the 2003
article
, up until MySQL 5 everything went pretty much as planned. Two key
features are not part of MySQL’s 5.1 release however. MySQL now aims to have
foreign key support for all storage engines in version 5.2, while talk of a
proper online backup feature has gone disconcertingly quiet. One plus, however,
is that the addition of views, that oft-requested feature which was
only planned for MySQL 6, was included a full version number early in MySQL
5.0.

Progress in certain areas has disappointed. A full two years after I
suggested that interoperability between MaxDB and MySQL would be sure to
improve, and users would be able to use standard MySQL tools such as mysql and mysqldump
to interact with MaxDB, this has not happened. This is all the more
disappointing for MySQL with what has since transpired with Oracle.

MySQL Cluster though, which two years ago was less than the v in vaporware,
has come along in leaps and bounds and has made itself a significant part of
any serious MySQL DBA’s repertoire. It now also integrates well with ordinary
replication.

New features in MySQL 5.1

At the time of writing, MySQL 5.1 is now in beta, and not that far away from
being a stable release. I covered this much more thoroughly in my March article
devoted to the changes in MySQL 5.1, entitled MySQL
5.1 – the next generation
, but for the purposes of this article, here’s an
overview of the new features.

  • Row-based replication, that allow non-deterministic statements to
    be accurately replicated.

  • The ability to write log files to tables instead of files (and
    therefore to query them with SQL statements).

  • More control over how tables are physically stored, with partitioning.

  • Better replication of and between MySQL clusters.

  • A flexible plugin API allowing components to be loaded and
    unloaded while the server is running.

  • A load emulator application, mysqlslap.

  • The ability to dump tablespaces with mysqldump.

  • The event scheduler, which confers the ability to run
    statements based on time.

  • A bigger, better metadata database.

  • A number of XML functions.

  • A script to ease updating to a new version, mysql_upgrade.

  • New features for the little-known instance manager,
    allowing one to list all log files, return part of a log file, and supply
    configuration options.

  • The ability to write triggers, which contain direct references to
    tables by name.

New features in MySQL 5.2

So far, the documentation about MySQL 5.2 is more about what won’t
be there, rather than what will. There are a number of statements or constructs
that are likely to be deprecated or removed altogether (some are already deprecated).
These include:

  • The table_type variable, as well as the TYPE
    definition and the SHOW TABLE TYPES statement, since what were once
    called table types are now referred to as storage engines.

  • The log_bin_trust_routine_creators variable.

  • The ability to define (n) in TIMESTAMP(n). The TIMESTAMP field
    will rather always store and return the full DATETIME.

  • The BACKUP TABLE and RESTORE TABLE statements.
    Reading between the lines, this does not mean MySQL has left you at the mercy
    of the hard drive gods. Rather, it is likely to indicate that a proper online
    backup facility is imminent, although concrete plans are sketchy at present.
    Currently, these statements only work for MyISAM tables, and lock each table
    one at a time. Getting a clean backup of multiple tables requires issuing LOCK
    statements, which for large or busy tables has a significant impact. The mysqlhotcopy
    script is suggested instead for now.

  • The SHOW LOGS and SHOW BDB LOGS statements. The
    much more specific synonym SHOW ENGINE BDB LOGS is preferred.

  • The LOAD TABLE FROM MASTER statement. This is not
    particularly useful (LOAD DATA FROM MASTER is more commonly used), and
    practically this statement is mostly used for debugging LOAD DATA, and is
    rather likely to cause havoc if used on existing slaves.

  • The SHOW INNODB and SHOW MUTEX statements.
    Preferred now are the much clearer SHOW ENGINE INNODB STATUS and SHOW
    ENGINE INNODB MUTEX
    statements.

However, there is something positive planned for MySQL 5.2 –
foreign keys. Yes, you read right, foreign keys! They have been supported by
the InnoDB storage engine since the days of MySQL 3.23, and the syntax
has been supported by the other storage engines for a while, so most developers
have hardly noticed their omission. However, from version 5.2 all storage
engines, including the MyISAM engine will fully support them. MyISAM was
originally designed as a light, fast, storage engine. With all the new features
added since its humble beginnings, it will be interesting to see how its
performance suffers.

What’s missing?

The big difference between the new features being added in upcoming versions
of MySQL, and those that were still being added two or three years ago, is how
less likely an experienced DBA, who is a newcomer to MySQL, will be surprised
that the feature is not there. Before, absolutely vital features such as views,
triggers, subqueries and stored procedures were missing. All of these
necessities are now there, and the new features are more from the realm of nice-to-haves
for most developers. If you are ardently disagreeing at this point, claiming
the absolute necessity of something like the event scheduler, look around, you
are the exception that proves the rule.

There is little missing in terms of features now. Instead, MySQL’s status as
a contender is now more dependant on 3rd-party support, and integration into
enterprise applications, where it still lags significantly. However, the doors
are now open, and there is little technically stopping more widespread
adoption. The murky realms of marketing and deal making will determine MySQL’s
future in that arena.

Recognition as a contender

Perhaps the greatest recognition that MySQL has arrived (wherever
exactly that would be) has come from Oracle. Oracle, still broadly perceived as
the leading database vendor, have purchased
Innobase OY
, who are responsible for MySQL’s most advanced storage engine, InnoDB.
Shortly after, they purchased
Sleepycat
, who are responsible for the BDB storage engine. Both these
storage engines add much-needed functionality, without which MySQL again reverts
to toy database status. Moreover, most indicative of all, Oracle made an offer
to purchase MySQL, which was turned down.

However, MySQL has responded
in the way one would expect
. They’ve looked at securing the expertise to
build their own transactional storage engine, and have done so by buying Jim
Starkey’s Netfrastructure, and securing the services of Jim Starkey. Starkey is
highly-regarded as the father of Interbase, which later forked into
Firebird. MySQL have also secured a multi-year deal with Oracle renewing their InnoDB
licensing, which ensures stability for a while.

Falcon

At the same time, Starkey and others have clearly been hard at work. A new
storage engine, Falcon, should be ready for beta testing soon. Falcon will be a
fully-featured transactional MySQL storage engine, based on the mature
Netfrastructure engine, which has been in use of over 4-years, and has now been
integrated into MySQL. As Starkey made clear in a presentation at the 2006
MySQL Users Conference in Santa Clara in late April, Falcon is not an InnoDB
clone, a Firebird clone, a standalone DBMS or Netfrastructure. It’s a fully-featured
storage engine that’s likely to become the de facto standard for MySQL.

Conclusion

MySQL today is in a healthy position. The company supporting it is making
good revenue, its technical development has come along well to the point where it
is sufficient for the vast majority of the market, and there is great expertise
continuing to develop it. It is positioning itself so that even if Oracle’s
moves are threatening, it will come through without much disruption. The
biggest risk for its future is probably if MySQL AB accepts a buyout offer from
Oracle, and as MySQL continues to make inroads into Oracles database market,
Oracle will can respond by throwing money at MySQL to make the problem go away
(which they’ve already tried), as well as positioning themselves as more than
just a database company, which they are doing relatively successfully.

»


See All Articles by Columnist
Ian Gilfillan

Ian Gilfillan
Ian Gilfillan lives in Cape Town, South Africa. He is the author of the book 'Mastering MySQL 4', published by Sybex, and has been working with MySQL since 1997. These days he develops mainly in PHP and MySQL, although confesses to starting out with BASIC and COBOL, way back when, and still has a soft spot for Perl. He developed South Africa's first online grocery store, and has developed and taught internet development and other technical courses for various institutions. He has majors in Programming and Information Systems, as well as English and Philosophy. For 5 years he was Lead Developer and IT Manager for Independent Online, South Africa's premier news portal. However, he has now 'retired' from fulltime work, and is hoping that his next book will be more in the style of William Blake and Allen Ginsberg.

Latest Articles