Not the answer you need?
Register and ask your own question!

pt-online-schema-change and documentation confusion

WheresWardyWheresWardy ContributorInactive User Role Beginner
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;</pre>


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?

Comments

  • xaprbxaprb Mentor Inactive User Role Beginner
    We can't rename the table to _old because the foreign keys would then point to _old.
  • WheresWardyWheresWardy Contributor Inactive User Role Beginner
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.