| 2017-10-18 14:33:41 |
Manami Nakamura |
description |
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 with rebuilding multiple foreign keys on one child table, which reference the parent 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.
- 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.
===================== |
|
| 2017-10-20 01:32:56 |
Manami Nakamura |
description |
When I alter table with rebuilding multiple foreign keys on one child table, which reference the parent 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.
- 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 with rebuilding multiple foreign keys on one child table, which reference the parent 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.
- 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=parent, 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.
===================== |
|
