PHPBuilder - MySQL, today's contender

RSS Twitter

MySQL, today's contender

by: Ian Gilfillan
May 15, 2007

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.

Next Page »

Comment and Contribute

Your comment has been submitted and is pending approval.

Ian Gilfillan



(Maximum characters: 1200). You have characters left.