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

Polyglot Projects Percona Toolkit
1

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?.

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

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:


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”: [url]http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html[/url]