A closer look at Online DDL in MySQL 5.6

MySQL 5.6 introduced online DDL for InnoDB tables, which means that a number of ALTER TABLE statements no longer block access to data modification while running.
I’ve written about this feature before: it was enough to make one of my freelancing customers fall in love with MySQL again. Today I wanted to go through what’s covered and what’s not.

The Manual

The MySQL manual actually does all the work for me, since there is a table under 14.2.12.1. Overview of Online DDL.
The particular column you want to pay attention to is “Allows Concurrent DML?”, which means that means that neither reads or writes are blocked.

Operation	In-Place?	Copies Table?	Allows Concurrent DML?	Allows Concurrent Query?	Notes
`CREATE INDEX`, `ADD INDEX`	Yes*	No*	Yes	Yes	Some restrictions for `FULLTEXT` index; see next row. Currently, the operation is not in-place (that is, it copies the table) if the same index being created was also dropped by an earlier clause in the same `ALTER TABLE` statement.
`ADD FULLTEXT INDEX`	Yes	No*	No	Yes	Creating the first `FULLTEXT` index for a table involves a table copy, unless there is a user-supplied `FTS_DOC_ID` column. Subsequent `FULLTEXT` indexes on the same table can be created in-place.
`DROP INDEX`	Yes	No	Yes	Yes	Modifies `.frm` file only, not the data file.
`OPTIMIZE TABLE`	Yes	Yes	Yes	Yes	Uses `ALGORITHM=INPLACE` as of MySQL 5.6.17. `ALGORITHM=COPY` is used if `old_alter_table=1` or mysqld `--skip-new` option is enabled. `OPTIMIZE TABLE` using online DDL (`ALGORITHM=INPLACE`) is not supported for tables with FULLTEXT indexes.
Set default value for a column	Yes	No	Yes	Yes	Modifies `.frm` file only, not the data file.
Change auto-increment value for a column	Yes	No	Yes	Yes	Modifies a value stored in memory, not the data file.
Add a foreign key constraint	Yes*	No*	Yes	Yes	To avoid copying the table, disable `foreign_key_checks` during constraint creation.
Drop a foreign key constraint	Yes	No	Yes	Yes	The `foreign_key_checks` option can be enabled or disabled.
Rename a column	Yes*	No*	Yes*	Yes	To allow concurrent DML, keep the same data type and only change the column name.
Add a column	Yes	Yes	Yes*	Yes	Concurrent DML is not allowed when adding an auto-increment column. Although `ALGORITHM=INPLACE` is allowed, the data is reorganized substantially, so it is still an expensive operation.
Drop a column	Yes	Yes	Yes	Yes	Although `ALGORITHM=INPLACE` is allowed, the data is reorganized substantially, so it is still an expensive operation.
Reorder columns	Yes	Yes	Yes	Yes	Although `ALGORITHM=INPLACE` is allowed, the data is reorganized substantially, so it is still an expensive operation.
Change `ROW_FORMAT` property	Yes	Yes	Yes	Yes	Although `ALGORITHM=INPLACE` is allowed, the data is reorganized substantially, so it is still an expensive operation.
Change `KEY_BLOCK_SIZE` property	Yes	Yes	Yes	Yes	Although `ALGORITHM=INPLACE` is allowed, the data is reorganized substantially, so it is still an expensive operation.
Make column `NULL`	Yes	Yes	Yes	Yes	Although `ALGORITHM=INPLACE` is allowed, the data is reorganized substantially, so it is still an expensive operation.
Make column `NOT NULL`	Yes*	Yes	Yes	Yes	When `SQL_MODE` includes `strict_all_tables` or `strict_all_tables`, the operation fails if the column contains any nulls. Although `ALGORITHM=INPLACE` is allowed, the data is reorganized substantially, so it is still an expensive operation.
Change data type of column	No	Yes	No	Yes
Add primary key	Yes*	Yes	Yes	Yes	Although `ALGORITHM=INPLACE` is allowed, the data is reorganized substantially, so it is still an expensive operation. `ALGORITHM=INPLACE` is not allowed under certain conditions if columns have to be converted to `NOT NULL`. See Example 14.9, “Creating and Dropping the Primary Key”.
Drop primary key and add another	Yes	Yes	Yes	Yes	`ALGORITHM=INPLACE` is only allowed when you add a new primary key in the same `ALTER TABLE`; the data is reorganized substantially, so it is still an expensive operation.
Drop primary key	No	Yes	No	Yes	Restrictions apply when you drop a primary key primary key without adding a new one in the same `ALTER TABLE` statement.
Convert character set	No	Yes	No	Yes	Rebuilds the table if the new character encoding is different.
Specify character set	No	Yes	No	Yes	Rebuilds the table if the new character encoding is different.
Rebuild with `FORCE` option	Yes	Yes	Yes	Yes	Uses `ALGORITHM=INPLACE` as of MySQL 5.6.17. `ALGORITHM=COPY` is used if `old_alter_table=1` or mysqld `--skip-new` option is enabled. Table rebuild using online DDL (`ALGORITHM=INPLACE`) is not supported for tables with FULLTEXT indexes.
Rebuild with “null“ `ALTER TABLE ... ENGINE=INNODB`	Yes	Yes	Yes	Yes	Uses `ALGORITHM=INPLACE` as of MySQL 5.6.17. `ALGORITHM=COPY` is used if `old_alter_table=1` or mysqld `--skip-new` option is enabled. Table rebuild using online DDL (`ALGORITHM=INPLACE`) is not supported for tables with FULLTEXT indexes.
Set table-level persistent statistics options (`STATS_PERSISTENT`, `STATS_AUTO_RECALC` `STATS_SAMPLE_PAGES`)	Yes	No	Yes	Yes	Modifies `.frm` file only, not the data file.

Update: I have updated the table to reflect changes in MySQL 5.6.17.

Find that hard to remember?

What I also like about MySQL 5.6, is that it also introduces syntax to force ALTER TABLE statements to not run if they can not be performed online. Here is an example of using LOCK=NONE (must allow concurrent DML):

mysql> CREATE TABLE a (id INT NOT NULL PRIMARY KEY, a char(100),
 b char(100));
Query OK, 0 rows affected (0.02 sec)
mysql> ALTER TABLE a ADD INDEX idx_a (a), LOCK=NONE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> ALTER TABLE a DROP PRIMARY KEY,  LOCK=NONE;
ERROR 1846 (0A000): LOCK=NONE is not supported.
 Reason: Dropping a primary key is not allowed without
 also adding a new primary key. Try LOCK=SHARED.

I recommend using this syntax whenever writing migration scripts so you are never surprised by how much impact they will have.

What’s upcoming?

In general I would comment that the number of operations which switched to being online in 5.6 is quite substancial. In MySQL 5.7 two additional improvements to online DDL are available, and are already noted in the MySQL 5.7 manual page:

Extending a VARCHAR column is online.
Renaming an index is online.

Have you used Online DDL in 5.6? Please leave a comment and share your experiences.