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.7, “Summary of 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 will be “re-partitioned” using the
algorithm. In other words, a new partitioned table is created with
the new partitioning scheme. The newly created table will include
any changes applied by the
TABLE statement and the table data will be copied into
the new table structure.
If you do not change the table's partitioning using
ALTER TABLE partitioning clauses or
perform any other partition management in your
ALTER TABLE statement,
ALTER TABLE will use the
INPLACE algorithm on each table partition. Be
aware, however, that when
ALTER TABLE operations are
performed on each partition, there will be 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.
Full-text search (FTS) and foreign keys are not supported by
InnoDB partitioned tables. For more
information, see Section 12.9.5, “Full-Text Restrictions” and
Section 19.6.2, “Partitioning Limitations Relating to Storage Engines”.