With the exception of
partitioning clauses, online DDL operations for partitioned
InnoDB tables follow the same rules that apply
InnoDB tables. Online DDL rules are
outlined in Table 15.10, “Online Status for DDL Operations”.
ALTER TABLE partitioning clauses do
not go through the same internal online DDL API as regular
InnoDB tables, and are only
allowed in conjunction with
If you use an ALTER TABLE partitioning clause in an
ALTER TABLE statement, the
partitioned table is “re-partitioned” using the
algorithm. In other words, a new partitioned table is created with
the new partitioning scheme. The newly created table includes any
changes applied by the
statement and the table data is copied into the new table
If you do not change the table partitioning using
ALTER TABLE partitioning clauses or
perform any other partition management in your
ALTER TABLE statement,
ALTER TABLE uses the
INPLACE algorithm on each table partition. Be
aware, however, that when
ALTER TABLE operations are
performed on each partition, there is increased demand on system
resources due to operations being performed on multiple
Even though partitioning clauses of the
TABLE statement do not go through the same internal
online DDL API as regular non-partitioned
InnoDB tables, MySQL still attempts to minimize
data copying and locking where possible:
DROP PARTITIONfor tables partitioned by
LISTdo not copy any existing data.
TRUNCATE PARTITIONdoes not copy any existing data, for all types of partitioned tables.
Concurrent queries are allowed during
COALESCE PARTITIONfor tables partitioned by
LIST. MySQL copies the data while holding a shared lock.
REBUILD PARTITION, or
COALESCE PARTITIONfor a table partitioned by
LIST, concurrent queries are allowed. Data from the affected partitions is copied while holding a shared metadata (read) lock at the table level.