When I alter table rebuild foreign keys which reference the table to be altered, by using option "--alter-foreign-keys-method=rebuild_constraint", a warning message "child has no foreign key constraints referencing _parent_old" is logged for second foreign key.
- Parent table definition which is altered by pt-online-schema-change
=====================
CREATE TABLE `parent` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
=====================
- Child table definition which has multiple foreign keys referencing the parent table
=====================
CREATE TABLE `child` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` int(10) unsigned NOT NULL,
`merged_into_parent_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `child_IX1` (`parent_id`),
KEY `child_IX2` (`merged_into_parent_id`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `child_ibfk_2` FOREIGN KEY (`merged_into_parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
=====================
- Complete command‐line used to run the tool
=====================
/usr/bin/pt-online-schema-change \
--nodrop-old-table --ask-pass \
--alter-foreign-keys-method rebuild_constraints \
--alter 'ADD COLUMN `column` int(11) unsigned' \
h=host, D=database, t=table, u=pt-osc \
--dry-run \
=====================
- Tool version
pt-online-schema-change 3.0
- MySQL version of all servers involved
5.6
- Output from the tool including STDERR
=====================
Swapping tables...
Swapped original and new tables OK.
Rebuilding foreign key constraints...
`database`.`child` has no foreign key constraints referencing `database`.`_parent_old`.
Rebuilt foreign key constraints OK.
=====================
When I alter table rebuild foreign keys which reference the table to be altered, by using option "--alter- foreign- keys-method= rebuild_ constraint" , a warning message "child has no foreign key constraints referencing _parent_old" is logged for second foreign key.
- Parent table definition which is altered by pt-online- schema- change ======= ======= ======= =======
=======
CREATE TABLE `parent` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
=======
- Child table definition which has multiple foreign keys referencing the parent table ======= ======= into_parent_ id` int(10) unsigned NOT NULL, into_parent_ id`), into_parent_ id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ======= =======
=======
CREATE TABLE `child` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` int(10) unsigned NOT NULL,
`merged_
PRIMARY KEY (`id`),
KEY `child_IX1` (`parent_id`),
KEY `child_IX2` (`merged_
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `child_ibfk_2` FOREIGN KEY (`merged_
) ENGINE=InnoDB;
=======
- Complete command‐line used to run the tool ======= ======= pt-online- schema- change \ foreign- keys-method rebuild_constraints \ ======= =======
=======
/usr/bin/
--nodrop-old-table --ask-pass \
--alter-
--alter 'ADD COLUMN `column` int(11) unsigned' \
h=host, D=database, t=table, u=pt-osc \
--dry-run \
=======
- Tool version schema- change 3.0
pt-online-
- MySQL version of all servers involved
5.6
- Output from the tool including STDERR ======= ======= .`_parent_ old`. ======= =======
=======
Swapping tables...
Swapped original and new tables OK.
Rebuilding foreign key constraints...
`database`.`child` has no foreign key constraints referencing `database`
Rebuilt foreign key constraints OK.
=======