no-swap-tables breaks original parent/child constraint
General
Escalation
General
Escalation
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.
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.