no-swap-tables breaks original parent/child constraint

Description

There is a regression when running pt-online-schema-change 3.4.0 with --no-swap-tables on a parent table, it rebuilds the child constraint pointing to the new table. In the end, it can't drop the new table since a foreign key is pointing to it. Please check the example below:

mysql [localhost:8029] {msandbox} (test) > CREATE TABLE parent ( id INT NOT NULL PRIMARY KEY) ;
Query OK, 0 rows affected (0.04 sec)mysql [localhost:8029] {msandbox} (test) > CREATE TABLE child ( id INT NOT NULL PRIMARY KEY, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE) ;
Query OK, 0 rows affected (0.04 sec)

$ ./percona-toolkit-3.4.0/bin/pt-online-schema-change --defaults-file=./sandboxes/msb_8_0_29/my.sandbox.cnf --execute --no-swap-tables --alter-foreign-keys-method rebuild_constraints --recursion-method none --alter "force" D=test,t=parent
...
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Child tables:
  `test`.`child` (approx. 1 rows)
Will use the rebuild_constraints method to update foreign keys.
Altering `test`.`parent`...
Creating new table...
Created new table test._parent_new OK.
Altering new table...
Altered `test`.`_parent_new` OK.
2022-08-26T21:11:32 Creating triggers...
2022-08-26T21:11:32 Created triggers OK.
2022-08-26T21:11:32 Copying approximately 1 rows...
2022-08-26T21:11:32 Copied rows OK.
2022-08-26T21:11:32 Rebuilding foreign key constraints...
2022-08-26T21:11:32 Rebuilt foreign key constraints OK.
Not dropping old table because --no-swap-tables was specified.
2022-08-26T21:11:32 Dropping triggers...
2022-08-26T21:11:32 Dropped triggers OK.
2022-08-26T21:11:32 Dropping new table...
2022-08-26T21:11:32 Error dropping new table `test`.`_parent_new`: DBD::mysql::db do failed: Cannot drop table '_parent_new' referenced by a foreign key constraint '_child_ibfk_1' on table 'child'. [for Statement "DROP TABLE IF EXISTS `test`.`_parent_new`;"] at ./percona-toolkit-3.4.0/bin/pt-online-schema-change line 9445.To try dropping the new table again, execute:
DROP TABLE IF EXISTS `test`.`_parent_new`;

General log shows it re-created the constraint:

2022-08-26T22:12:11.115828Z       78 Query    SET foreign_key_checks=0
2022-08-26T22:12:11.115888Z       78 Query    ALTER TABLE `test`.`child` DROP FOREIGN KEY `child_ibfk_1`, ADD CONSTRAINT `_child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `test`.`_parent_new` (`id`) ON DELETE CASCADE
2022-08-26T22:12:11.128020Z       78 Query    SET foreign_key_checks=1

pt-online-schema-change 3.3.1 has a different behavior when using --no-swap-tables, it does not rebuild the constraint:

$ ./percona-toolkit-3.3.1/bin/pt-online-schema-change --defaults-file=./msb_8_0_29/my.sandbox.cnf --execute --no-swap-tables --alter-foreign-keys-method rebuild_constraints --recursion-method none --alter "force" D=test,t=parent
...
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Child tables:
  `test`.`child` (approx. 1 rows)
Will use the rebuild_constraints method to update foreign keys.
Altering `test`.`parent`...
Creating new table...
Created new table test._parent_new OK.
Altering new table...
Altered `test`.`_parent_new` OK.
2022-08-26T22:16:58 Creating triggers...
2022-08-26T22:16:58 Created triggers OK.
2022-08-26T22:16:58 Copying approximately 1 rows...
2022-08-26T22:16:58 Copied rows OK.
Error updating foreign key constraints: I need a old_tbl argument at ./percona-toolkit-3.3.1/bin/pt-online-schema-change line 11119.
2022-08-26T22:16:58 Dropping triggers...
2022-08-26T22:16:58 Dropped triggers OK.
Not dropping new table because FKs processing has failed.
Altered `test`.`parent` but there were errors or warnings.
    (in cleanup) I need a old_tbl argument at ./percona-toolkit-3.3.1/bin/pt-online-schema-change line 11119.

There are many users running pt-osc with --no-swap-tables as a realistic dry-run.

Environment

None

AFFECTED CS IDs

CS0029747

Smart Checklist

Activity

Details

Assignee

Reporter

Priority

Affects versions

Fix versions

Regression Issue

Yes

Smart Checklist

Created August 26, 2022 at 10:34 PM
Updated May 17, 2024 at 1:51 PM