pt-online-schema-change not properly detecting foreign keys

[Rick Balsano]

Tool version: 2.2.7
MySQL: 5.1.45

Pt-online-schema-change does not find child tables as expected. It can incorrectly locate tables which reference a table with the same name in a different schema and can miss tables referencing the altered table if they are in a different schema. To reproduce:

In mysql:

create database db1;
use db1;
create table parent_table (a int unsigned primary key);
create table child_table_in_same_schema (a int unsigned primary key, constraint a_fk foreign key a_fk (a) references parent_table(a));
create database db2;
use db2;
create table child_table_in_second_schema (a int unsigned primary key, constraint a_fk foreign key a_fk (a) references db1.parent_table(a));
create table parent_table (a int unsigned primary key);
create table db1.child_table_in_same_schema_referencing_second_schema (a int unsigned primary key, constraint a_fk2 foreign key a_fk2 (a) references db2.parent_table(a));

On commandline:

$ pt-online-schema-change --alter 'add column c varchar(16)' D=db1,t=parent_table --alter-foreign-keys-method auto --dry-run

Expected:

pt-online-schema-change detects only the following child tables:
* db1.child_table_in_same_schema
* db2.child_table_in_second_schema

Actual:
* db1.child_table_in_same_schema (correct)
* db1.child_table_in_same_schema_referencing_second_schema (incorrect, references 'parent_table' in db2, not db1)
* missing db2.child_table_in_second_schema

The query at fault is:

my $sql = "SELECT table_schema, table_name "
         . "FROM information_schema.key_column_usage "
         . "WHERE constraint_schema='$tbl->{db}' "
         . "AND referenced_table_name='$tbl->{tbl}'";

I believe "constraint_schema" above should be changed to "referenced_table_schema".

Full output:

$ pt-online-schema-change --alter 'add column c varchar(16)' D=db1,t=parent_table --alter-foreign-keys-method auto --dry-run
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Child tables:
  `db1`.`child_table_in_same_schema` (approx. 1 rows)
  `db1`.`child_table_in_same_schema_referencing_second_schema` (approx. 1 rows)
Will automatically choose the method to update foreign keys.
Starting a dry run. `db1`.`parent_table` will not be altered. Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table db1._parent_table_new OK.
Altering new table...
Altered `db1`.`_parent_table_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
Not determining the method to update foreign keys because this is a dry run.
Not swapping tables because this is a dry run.
Not updating foreign key constraints because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
2014-05-01T15:35:24 Dropping new table...
2014-05-01T15:35:24 Dropped new table OK.
Dry run complete. `db1`.`parent_table` was not altered.

[Frank Cizmich (frank-cizmich)]

Confirmed.
Patch works well too.
Thank you Rick!
Will merge to trunk soon.

[Rick Balsano (rick-v)]

Great! Thanks for pulling this in.

Perhaps not totally appropriate, but can I also plug my blueprint request too?

https://blueprints.launchpad.net/percona-toolkit/+spec/pt-online-schema-change-limit-key-search

That's definitely a feature (& not a bug), but it's related to this same foreign key search.

Regardless, thanks again.

[Shahriyar Rzayev (rzayev-sehriyar)]

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