Programming

What’s New in MySQL 5.5

    Craig Buckler
    Share

    MySQL’s future has been uncertain since Oracle acquired Sun Microsystems in April 2009. Fortunately, all those who doubted the company’s intentions can relax a little: MySQL 5.5 has been released.

    There have been impressive claims about MySQL 5.5, and you’ll find reports detailing speed improvements of up to 4,000%. Take those with a large pinch of salt, but you can generally expect version 5.5 to be faster.

    Let’s take a closer look at the best of the new features…

    InnoDB Becomes The Default Storage Engine

    It’s about time. There are many, many, many reasons why InnoDB is a better choice for most applications, yet MyISAM had up to now remained the default. From MySQL 5.5, developers will have out-of-the-box reliability and stability with ACID-compliant transactions, foreign key support and crash recovery.

    In addition, the latest InnoDB storage engine offers better performance, scalability and recovery from catastrophic failure.

    Better Availability

    Performance has been improved in multi-server replicated systems with:

    • Semi-synchronous replication
      To improve the reliability of failover, the master can wait until at least one slave has logged the transaction.
    • Replication heartbeat
      The master sends a message to all slave nodes at frequent intervals. Slaves therefore know when the master has failed, and it helps estimate the delay between master and slave updates.

    New LOAD XML Command

    LOAD XML reads data from an XML file into a table. Three different formats are supported:

    
<row column1="value1" column2="value2" .../>

    or

    
<row>
	<column1>value1</column1>
	<column2>value2</column2>
</row>

    or

    
<row>
	<field name="column1">value1</field>
	<field name="column2">value2</field>
</row>

    New SIGNAL/RESIGNAL Commands

    Both commands allow you to implement exception handling within stored procedures, functions, events, triggers.

    SIGNAL is effectively the same as the ‘throw’ command in PHP and other languages. It allows you to pass back an error number, SQLSTATE value and message to the calling code.

    RESIGNAL lets you propagate a SIGNAL exception, perhaps after further doing further work such as data clean-ups. RESIGNAL can pass the original or a modified version of the error.

    New TO_SECONDS() Function

    Handling dates and times is not always as easy as you’d expect. The file system, PHP interpreter and MySQL itself have different methods of storing times, so many developers resort to the UNIX timestamp or other integer formats.

    TO_SECONDS() could help resolve some issues; given any date or datetime value, it returns the number of seconds since year 0 (or NULL if you pass any other value).

    Pluggable Authentication

    Until now, a client connecting to the MySQL server had to pass a user name and password which was authenticated against a record in the mysql.user table. It’s now possible to create a plugin which handles authentication using your own systems and credentials.

    Improved Performance on Windows

    Many businesses install MySQL on Windows servers. This often makes sense; they already have IT administrators with Windows expertise, but need a cost-effective database solution or want to install a web application which requires MySQL.

    The MySQL development team has tidied the system and improved performance on Win32 and Win64 platforms.

    Have you evaluated MySQL 5.5? Have you experienced any performance gains? Will you be upgrading? Has the 5.5 release reassured you about Oracle’s commitment to the database?