InnoDB DDL: kudos to quick responders on

October 18, 2012

Continuing my experiments with 5.6 InnoDB online DDL, a bug which I've opened, and another which I commented on were quickly answered and explained by the Oracle/MySQL team.

On both accounts I'm happy to acknowledge the issue is resolved; in both cases I failed to produce a real bug scenario. Good lesson. Kudos for quick and informative responses!

What's left of my experiment, then? Still a lot to check.

I am mainly still confused with which operations exactly can use LOCK=NONE (allowing for updated to table while ALTERing). So far I am only able to produce ALTERs with LOCK=SHARED, meaning table is readable, but cannot be updated.

I will want to test speeds. I've so far been content with slow response times for queries over altered tables. How well will that endure under heavy load?

  • Bascially only index creation and deletion can be done online without locks.

    This includes adding/dropping foreign keys online, but only if you set foreign_key_checks=0.

  • When the manual says that "some other operations are non-blocking", I think they mean they don't block reads, even though they require a table copy.

    This should probably be clarified by the docs team.


  • @Justin,
    Thanks for the quick info. That' I must say, is not nearly as I was expecting.

  • Which version have you been testing? Version 5.6.7 and even the upcoming 5.6.8 will contain some bugs in this area. I have been busy fixing them before the GA release. 🙂

    The point of online ALTER TABLE is that writes are allowed, even when the operation requires that the table be copied.

  • @Marko,

    node1 (sakila) > select version();
    | version()    |
    | 5.6.7-rc-log |
Powered by Wordpress and MySQL. Theme by