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.
http://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html
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
@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,