pt-online-schema-change outputs "child_table has no foreign key constraints referencing _parent_table_old" when a child_table has multiple foreign keys referencing to the parent_table

[Manami Nakamura]

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

[Carlos Salguero (carlos-salguero)]

Hi,

Could you clarify which table are you trying to alter? parent or child?
The command line you posted says 'table'

[Manami Nakamura (mnmandahalf)]

Hi Carlos,

The table I tried to alter is below, `parent`.
fixed my command.

>
> - 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;
> =====================

[Manami Nakamura (mnmandahalf)]

https://raw.githubusercontent.com/percona/percona-toolkit/3.0/bin/pt-online-schema-change

It seems each content of "@$child_tables" is not unique,
but pt-osc rebuilds all constraints "@rebuilt_constraints" on one table in one loop.

===========
 CHILD_TABLE:
   foreach my $child_tbl ( @$child_tables ) {
      my $table_def = $tp->get_create_table(
         $cxn->dbh(),
         $child_tbl->{db},
         $child_tbl->{tbl},
      );
      my @constraints = $table_def =~ m/$constraint/g;
      if ( !@constraints ) {
         warn ts("$child_tbl->{name} has no foreign key "
            . "constraints referencing $old_tbl->{name}.\n");
         next CHILD_TABLE;
      }

      my @rebuilt_constraints;
      foreach my $constraint ( @constraints ) {
         PTDEBUG && _d('Rebuilding fk constraint:', $constraint);
     . . .
============

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-1450