What’s New in MySQL 5.5

Tweet

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?

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • tbela99

    I have tried it since it was in the beta stage, and I have to admit it is faster

  • EastCoast

    I’m not convinced about innoDBs supposed crash recovery capabilities – had a server crash recently where afterwards mysqld wouldn’t restart because every single innodb table had an issue, whereas pretty much every myisam table was fine, a couple single line repair commands were all that was needed there. The innodb data recovery took a week and major effort with 3rd party recovery tools for the so-called crash resilient innodb tables.

    • http://www.lunadesign.org awasson

      What kind of backup do you employ? If you have a healthy backup within a recent time frame (like 12 – 24 hours), then it stands to reason you would be able to restore to the backup copy and then rebuild the remaining data. Perhaps that’s what you did and it was just the sheer amount of data that caused the week of work. I wonder if a redundant mirror would have been a solution.

      • slawek22

        He talks about being crash-resistant and rebuilding broken tables, not backups.

  • Anonymously

    Why is Oracle keeping MySQL alive? (This is not a comment about MySQL, but in fact the opposite MySQL takes money from other Oracle products.)

    • http://www.optimalworks.net/ Craig Buckler

      I’m not convinced MySQL takes money from Oracle. The reason businesses buy Oracle (or MySQL commercial editions) is for the support and enterprise tools. In addition, Oracle is in a better position to promote services to MySQL’s user base.

      I still have my doubts regarding the Oracle acquisition, but they’ve continued to support MySQL. Let’s hope it remains that way.

      • slawek22

        What if Oracle locked mysql? You’d have to switch to Postgre. After postgre there are no other free options.

        You would have to buy MSSQL / Oracle DB. That’s billions of dollars of lost revenue (yearly) to split between 2 companies.

        Oracle could kill mysql easily and they can make Postgre illegal in the US. That’s not hard. You just need some money to “lobby” creating / obeying some ridiculous “intellectual property” rights. Probably there’s no right “weather” for such actions yet… people in the EU don’t believe in “intellectual property” bullshit as much as you americans do.

        It’d only generate some hatered against Oracle… nothing more as many open source projects are already moving to Europe because of “software patents” :)

        But markets/laws are undergoing constant standarization processes… if we choose to obey US IP laws in the EU it’s likely that Oracle will make mysql and postgre illegal in the future.

      • david.leedom@gmail.com

        @slawek22

        “…After postgre there are no other free options…..” what about Firebird?

      • W2ttsy

        @craig I still have my doubts regarding the Oracle acquisition, but they’ve continued to support MySQL. Let’s hope it remains that way.

        Im actually surprised that they haven’t put more into creating a transitional product between mysql and oracle. Inevitably some companies are going to outgrow mysql and to have a commercial to enterprise pathway would go a long way.

        Also, as Spring found out, the best way to make money from open source is through training. I’d be interested to see if it takes many more months for Oracle to start offering MySql courses and the like.

      • http://www.optimalworks.net/ Craig Buckler

        @slawek22
        And don’t forget that both Microsoft and Oracle provide free editions of their databases.

        I think you’ve painted a gloomy picture and it’s pretty unlikely scenario. Oracle cannot kill MySQL. It’s open source and that’s why MariaDB is available — it’s a MySQL fork.

        And MySQL is not the only option, only the most widespread. Even if PostgreSQL was somehow made illegal in the US, it won’t be elsewhere — you could move your hosting offshore.

      • slawek22

        The only option now is US/EU … you don’t have good connectivity in other parts of the world. Remember my worlds … if politicians enforce software patents and US-like Intellectual Property rights – all databases but from big three will be dead. And the whole IT market will be on mercy of US corporations and troll patent holders.
        As i said Oracle could kill ANY open source competitor in the US using software patents. MS/IBM just have enough money to defend themselves.
        Gloomy picture? May be. Microsoft tried to delegalize linux pumping funds into SCO’s lawsuits. People in US were paying SCO to use linux even when the trial wasn’t yet in court, in EU they didn’t care. And MS is very “friendly” corportation comparing to Oracle or Apple.

      • http://www.optimalworks.net/ Craig Buckler

        Good connectivity isn’t available outside the US and EU? Are you serious? South Korea’s average broadband speed is almost 4x better! The western nations have some of the worst telecoms infrastructures in the world.

        But I think you’re being a little dramatic. Are you saying developers should stop using open source DBs and Linux on the off-chance they become illegal in the US? Sure, it could happen. Then again, it might not. If we worried about legal wrangles we’d never get anything done and might as well become lawyers!

  • EastCoast

    The backup copy in this case was missing some important data from some recent transactions, but was enough to get up and running. However I needed to recover the data lost and was surprised at how difficult it was, particularly in light of how innoDB sells itself as a more robust database engine. For anybody else that finds themselves in a similar situation this is useful : https://launchpad.net/percona-innodb-recovery-tool

  • basvanmeurs

    You seem to forget about what in my opinion is the most important improvement in MySQL 5.5: the hughe (up to 350%) general efficiency gain when having a lot of simultanious connections. This all of a sudden makes MySQL a much more usable database system for ‘big’ websites.

  • Anker Berg-Sonne

    You have to remember that 5.5 has been in the works for a long time, most of it before the Oracle purchase, so the 5.5 release doesn’t say much about Oracle’s commitment.
    I did benchmark 5.5 against MariaDB 5.2. MariaDB is still 30% faster with much more consistent performance. MySQL has a 10 second cycle where the transaction rate goes up and down by more than 50%. Its a known issua and I am surprised MySQL/Oracle hasn’t fixed it.