GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

pt-osc and Error dropping the old table with foreign key constraint fails.

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

  • pt-osc and Error dropping the old table with foreign key constraint fails.

    Hello:

    When I run an alter on a table that is referenced, the pt-osc creates its temporary tables and rename the table originates as '_tabla_old'. This makes the child table to change its reference table '_tabla_old'. The problem is when you try to delete the table '_tabla_old' error because this throws me being referenced. What to do in this scenario?.

    Code:
    pt-online-schema-change --alter "engine='InnoDB'" h=server,D=database,t=table --ask-pass --progress time,10 --print --nocheck-replication-filters --recursion-method=none --set-vars="SQL_LOG_BIN=OFF" --max-load "Threads_connected=900" --alter-foreign-keys-method=drop_swap --execute
    Code:
    Copying `database`.`tabla`:  90% 12:17 remain
    2014-06-05T22:58:21 Copied rows OK.
    2014-06-05T22:58:21 Swapping tables...
    RENAME TABLE `database`.`tabla` TO `database`.`_tabla_old`, `database`.`_tabla_new` TO `database`.`tabla`
    2014-06-05T22:58:22 Swapped original and new tables OK.
    2014-06-05T22:58:22 Dropping old table...
    DROP TABLE IF EXISTS `database`.`_tabla_old`
    2014-06-05T22:58:22 Dropping triggers...
    DROP TRIGGER IF EXISTS `database`.`pt_osc_database_tabla_del`;
    DROP TRIGGER IF EXISTS `database`.`pt_osc_database_tabla_upd`;
    DROP TRIGGER IF EXISTS `database`.`pt_osc_database_tabla_ins`;
    2014-06-05T22:58:28 Dropped triggers OK.
    Altered `database`.`tabla` but there were errors or warnings.
    2014-06-05T22:58:22 Error dropping the old table: DBD::mysql::db do failed: Cannot delete or update a parent row: a foreign key constraint fails [for Statement "DROP TABLE IF EXISTS `database`.`_tabla_old`"] at /usr/bin/pt-online-schema-change line 9269.
    .
    ​Thanks.

  • #2
    Hi There,

    I want to double check with you that you used --alter-foreign-keys-method=drop_swap, and get the above output? What version of the tool are you using? What version of MySQL server?

    With "drop_swap", the original table would not be renamed, instead dropped. I tested it with pt-osc 2.2.8, and here is part of the output:

    Code:
    pt-online-schema-change --alter "engine='InnoDB'" D=test,t=parent --progress time,10 --print --nocheck-replication-filters --recursion-method=none --set-vars="SQL_LOG_BIN=OFF" --max-load "Threads_connected=900" --alter-foreign-keys-method=drop_swap --execute
    ...
    2014-06-13T21:10:45 Copied rows OK.  2014-06-13T21:10:45 Drop-swapping tables...
      SET foreign_key_checks=0
      DROP TABLE IF EXISTS `test`.`parent`
      RENAME TABLE `test`.`_parent_new` TO `test`.`parent`
      2014-06-13T21:10:45 Dropped and swapped tables OK.
    ...
    Please check the documentation for risks with the "drop_swap" method and alternative method of "rebuild_constraints": http://www.percona.com/doc/percona-t...ma-change.html

    Comment

    Working...
    X