GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

pt-online-schema-change and documentation confusion

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • pt-online-schema-change and documentation confusion

    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.

    Comment


    • #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.

      Comment


      • #4
        No, foreign keys are a real pain. You have to choose one type of pain or another, sadly.

        Comment

        Working...
        X