0

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.

1
  • Do you have an FULLTEXT index on the table? – Rick James Jul 12 at 23:34
1

I use the AFTER clause

That gives an error, thereby forcing you to use ALGORITHM=COPY.

More

On the page https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html , see

Table 14.12 Online DDL Support for Column Operations

It says that "Adding column" and "Reordering columns" are done "In place" and "Rebuilds table".

I take that to mean that was not technically a "copy", but still a costly operation. (I find the page to be clumsy to read and interpret.)

As for the temp file you observed, I don't have an answer.

2
  • Isn't it supposed to send an error if the requested algorithm cannot be used? The documentation gives the example ERROR 0A000: ALGORITHM=INPLACE is not supported – FBB May 10 at 14:31
  • @FBB - yes; I phrased it poorly. – Rick James May 10 at 16:52
2

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.

1
  • Hello, while apparently it's true, I don't see what part of the documentation you cite says that the table will be copied if the new columns are not appended to the end. – FBB May 1 at 13:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.