pt-online-schema-change and documentation confusion

Polyglot Projects Percona Toolkit
1

In the documentation, --alter-foreign-keys-method says that using drop_swap will drop the original table before renaming the new one in to place. However, it also says that --no-drop-old-table will be enforced, which suggests that the old table won’t be dropped.

As I understand it, the documentation suggests that the process happens as such:

LOCK TABLES table WRITE;SET FOREIGN_KEY_CHECKS = 0;DROP TABLE table;RENAME TABLE table_new TO table;SET FOREIGN_KEY_CHECKS = 1;UNLOCK TABLES table;

However, if the table is very large (say, hundreds of GBs) then the drop operation could take quite a long time and block whilst it’s being performed. Unless there’s something I’m missing with regards to foreign keys, surely it would be much better to obey the --no-drop-old-table, not perform the drop (and rename the original table to _old instead), and let us drop the old table afterwards when it won’t block?

2

We can’t rename the table to _old because the foreign keys would then point to _old.

3

I thought that might be the case. Is there any way we can perform online changes to large tables with foreign keys? As far as I can tell either modifying the foreign keys or dropping the table will block for an unacceptably large amount of time (assuming the delete operation will take a few minutes to perform), and because of the nature of pt-online-schema-change we can’t “schedule” the drop for any particular time.