MySQL, Still tomorrow’s contender?

Looking back

Just under a year ago, I wrote an article entitled MySQL–Yesterday’s Toy, Tomorrow’s Contender. This article looked at the MySQL’s roadmap as it was then, and asserted that, looking at the upcoming features, MySQL was likely to become a serious contender to the established proprietary databases. I had planned for this month’s column to be about Stored Procedures, but as I was investigating MySQL’s implementation, I began to cast my mind back to that roadmap. Since then, there have been a number of large developments that I could not have predicted, so I thought it would be worthwhile to write an update to that article.

MySQL 4

MySQL 4 was pronounced stable in March 2003, and is the version anyone implementing a new MySQL-based application would most likely use. It does not support stored procedures, triggers, server-side cursors or views. It is a step-up from MySQL 3.23, useful mainly for websites, but it is still not an enterprise-ready database.

MySQL 4.1

Most of the features I mentioned last year have already been included (subqueries, UTF-8, geographical data support, derived table support, multi-line queries). MySQL 4.1 is getting close to beta (when no new features are due to be added), as there are only three features outstanding:

  • Stable OpenSSL support (MySQL 4’s is not completely tested)
  • More testing of prepared statements.
  • More testing of multiple character sets for one table.

MySQL 5.0

MySQL 5.0 is already out as a developer’s preview. Being in an early alpha stage, it is only for the hardcore who want to experiment, as many features will be changed and added, and it is by no means stable. I expect it to be released as stable between 12 and 18 months from now (from mid to late 2005).

  • The most desired enhancement in version 5 is that of stored procedures. Last year I said that the syntax was going to be similar to Oracle PL/SQL and T-SQL. Instead, it turns out that MySQL supports the standard SQL:2003 syntax (also used by DB2), but may at a later stage support the other syntaxes.
  • Triggers (basically stored procedures that respond to an event) will only be available in 5.1
  • Cursor support will only be elementary
  • Internationalization enhancements

MySQL 5.1

Besides the previously mentioned enhancements (triggers, foreign key support for all table types, online backup, failsafe replication, a true BIT type and column-level constraints) there have been a few more features added to the to-do list:

  • Full outer join (joins where each record from table A, including those with no matching record in table B is returned, along with each record from table B, including those with no match in table A)
  • Constraints
  • A change to the internal file interface that will make file handling much more general as well as making it easier to add extensions such as RAID.
  • The RENAME DATABASE statement.
  • The ability to get progress reports on long running commands

New developments

There have been a number of interesting new developments in the last year, not associated with a particular version of MySQL.

MySQL clustering

Clustering is a hugely significant feature. MySQL’s replication has, by their own admission, never been failsafe, and using it to improve availability and redundancy has some problems. A clustering solution takes care of this, by running multiple machines in parallel, with the result that should any fail, the others can almost instantaneously take over. In October last year, MySQL AB acquired Alzato, a company started by Ericcson. Alzato developed a clustering product for the telecoms market, and MySQL will release MySQL Cluster, based upon this product, in the 3rd quarter. There is supposedly a preview version available on the MySQL site, but I couldn’t find it. Hopefully it will be there by the time you read this. MySQL has an annoying habit (at least to my developer’s sensibility) of making well-publicised announcements before there’s an actual product to see (the same happened with MySQL Administrator).

There is no doubt that when this feature is released, and if it works as promised, MySQL will have taken a huge step closer to the enterprise.

The basic features are:

  • High availability. If one server fails, another will take over the load. There is no single point of failure.
  • High scalability. Simply add more servers as the need arises.
  • High performance. MySQL claims the solution can replicate 100 000 transactions per second on a standard setup with four dual-CPU nodes.

MaxDB

SAP gave MySQL their SAPDB database, which has now been rebranded as MaxDB. This is a mature database, used in enterprise ERP environments, with all the features users have been complaining MySQL does not yet reliably support, such as stored procedures, views and the ability to automatically switch to a backup server should the main server fail. The problem for existing MySQL users is that this is an entirely different product, and a user considering this may well change to any other product. Currently MaxDB is not interoperable with MySQL. However, in the future this will change, and users will be able to use standard MySQL tools such as mysql and mysqldump to interact with MaxDB.

MaxDB has another side to it, in that it reduces the pressure on MySQL AB to claim that MySQL is ready for the enterprise. With a fully-featured enterprise-ready product being supported, and generating income for the company, development on the MySQL database can continue at its own pace. Once MySQL data can easily be imported into MaxDB, it matters less that MySQL does not yet support all the necessary enterprise features.

Main MaxDB Features not supported in MySQL:

  • Views
  • Server-side cursors
  • Stored procedures and triggers
  • Automatic failover (to a standby server)
  • Scheduling and automatic messaging on alerts
  • Snapshots
  • Archive tables
  • Synonyms

MySQL Administrator

Soon to meet the demands of Windows users in particular is MySQL Administrator, a graphical tool for performing regular administrative tasks (the tool is still in alpha, although and there are currently Windows and Linux binaries available for download). Administering MySQL is already fairly easy, and MySQL Administrator makes it even easier. Disk space, number of queries, memory usage, backups, server status and a centralized view of all logs, are among the features all easily accessible from a graphical interface making this a useful addition. While some of the other features are an appeal to the enterprise market, there are still a significant number of novice Windows users uncomfortable with the command line, and this tool will appeal to them. There is even the slightly gimmicky ‘feature’ of being able to assign icons to users! Nevertheless, even experienced users appreciate common tasks being made easier.

Conclusion

MySQL is getting more difficult to typecast these days, as MySQL AB, the company that controls MySQL, now adds MaxDB and other products to the mix. While the development of MySQL continues apace, and that product reaches full functionality by today’s (and tomorrow’s) standards, the arrival of MaxDB as an enterprise-ready DBMS means that MySQL can develop at a more leisurely pace, and hopefully the code will be better for this. As a developer I know what happens under the hood when deadline pressure becomes unreasonable, and I hope that these two products can slowly converge in a healthy way. MySQL AB’s prediction of a commoditized DBMS market cannot be far way, and Oracle, IBM and Microsoft should be worried about the future viability of their proprietary products. As Gandhi’s old adage goes, “First they ignore you, then they ridicule you, then they fight you, then you win.” Right now they’re starting to fight…

»


See All Articles by Columnist
Ian Gilfillan

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.
Previous article
Next article

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles