14.2.11.8 Online DDL for Partitioned InnoDB Tables

With the exception of ALTER TABLE partitioning clauses, online DDL operations for partitioned InnoDB tables follow the same rules that apply to regular InnoDB tables. Online DDL rules are outlined in Table 14.5, “Summary of Online Status for DDL Operations”.

ALTER TABLE partitioning clauses do not go through the same internal online DDL API as regular non-partitioned InnoDB tables, and are only allowed in conjunction with ALGORITHM=DEFAULT and LOCK=DEFAULT.

If you use an ALTER TABLE partitioning clause in an ALTER TABLE statement, the partitioned table will be re-partitioned using the ALTER TABLE COPY 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 ALTER 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 INPLACE ALTER TABLE operations are performed on each partition, there will be increased demand on system resources due to operations being performed on multiple partitions.

Even though partitioning clauses of the ALTER 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:

Note

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 18.6.2, “Partitioning Limitations Relating to Storage Engines”.