79616

SQL Syntax for Online DDL

Typically, you do not need to do anything special to enable online DDL when using the ALTER TABLE

statement for innoDB tables. See Table 5.9, “Summary of Online Status for DDL Operations" for the

kinds of DDL operations that can be performed in-place, allowing concurrent DML, or both. Some

variations require particular combinations of configuration settings or

ALTER TABLE clauses.

You can control the various aspects of a particular online DDL operation by using the lock and

ALGORITHM clauses of the ALTER TABLE statement. These clauses come at the end of the statement,

separated from the table and column specifications by commas. The lock clause is useful for finetuning

the degree of concurrent access to the table. The algorithm clause is primarily intended for

performance comparisons and as a fallback to the older table-copying

behavior in case you encounter

any issues with existing DDL codę. For example:

•    To avoid accidentally making the table unavailable for reads, writes, or both, you could specify

a clause on the alter table statement such as lock=none (allow both reads and writes) or

lock=shared (allow reads). The operation halts immediately if the requested level of concurrency is not available.

•    To compare performance, you could run one statement with

algorithm=inplace and another with

ALGORITHM=COPY, as an alternative to setting the old_alter_table [531] configuration option.

•    To avoid the chance of tying up the server by running an alter table that copied the table, you

could include algorithm=inplace so the statement halts immediately if it cannot use the in-place