MySQL InnoDB – Table Copied Despite Using ALGORITHM=INPLACE

alter-tableinnodbMySQLmysql-5.7

I run an ALTER STATEMENT on an InnoDB table on a MySQL server version 5.7 that:

  • drop some foreign key constraints
  • drop some indexes (non unique)
  • add some columns (without index or foreign key constraint, but they are not added at the end of table, I use the AFTER clause)

I use the ALGORITHM=INPLACE option. But the table is still being copied, I see the temp file created in the database directory. According to the DDL operations documentation, all these operations support INPLACE modifications. Why is my table being copied anyway? I didn't get any error message.

Best Answer

From the documentation:

INPLACE: Operations avoid copying table data but may rebuild the table in place. An exclusive metadata lock on the table may be taken briefly during preparation and execution phases of the operation. Typically, concurrent DML is supported.

The table is being rebuilt because the order of the columns is changing. As you said in your question:

[The columns] are not added at the end of table, I use the AFTER clause

If you need to avoid table rebuilds as much as possible, the new columns will need to be appended to the table definition.

Related Question