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 14.8, “Online Status for DDL Operations”.
ALTER TABLE partitioning clauses do
not go through the same internal online DDL API as regular
InnoDB tables, and most do not
If you use an
partitioning clause in an
TABLE statement, the partitioned table is
“re-partitioned” using the
COPY algorithm. In other words,
a new partitioned table is created with the new partitioning
scheme. The newly created table includes any changes applied by
ALTER TABLE statement and the
table data is copied into the new table structure.
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.
REMOVE PARTITIONINGsupport the
ALGORITHMclause with the
COPYoption. Both operations require a minimum of
LOCK=SHARED, which permits concurrent queries.