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

Bug #1315130 reported by Rick Balsano on 2014-05-01
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit
Medium
Frank Cizmich

Bug Description

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.

Related branches

lp:~percona-toolkit-dev/percona-toolkit/pt-online-schema-change-not-properly-detecting-foreign-keys-1315130
Frank Cizmich: Pending requested 2014-07-03
Rick Balsano (rick-v) wrote :
tags: added: pt-online-schema-change
Frank Cizmich (frank-cizmich) wrote :

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

Changed in percona-toolkit:
status: New → Confirmed
importance: Undecided → Medium
assignee: nobody → Frank Cizmich (frank-cizmich)
Rick Balsano (rick-v) wrote :

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.

Changed in percona-toolkit:
milestone: none → 2.2.9
Changed in percona-toolkit:
status: Confirmed → Fix Committed
Changed in percona-toolkit:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers