pt-osc foreign-keys-method=none breaks constraints

Bug #1329422 reported by Bill Karwin
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to
Fix Released
Frank Cizmich

Bug Description

The alter-foreign-keys-method "none" changes FK constraints in a way that is hard to recover from.


create table (foo_id int primary key);
insert into foo values (42);
create table (foo_id int, foreign key (foo_id) references foo (foo_id));
insert into bar values (42);

Now try to use pt-osc, but choose "none" as the foreign key method:

$ pt-online-schema-change h=localhost,D=test,t=foo --alter="engine=InnoDB" --alter-foreign-keys-method="none" --execute

Following this command, the `bar` table is left in an unusable state:

  `foo_id` int(11) DEFAULT NULL,
  KEY `foo_id` (`foo_id`),
  CONSTRAINT `bar_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `_foo_old` (`foo_id`)

The FK constraint references the renamed _foo_old table that existed briefly during the alter. But then that _foo_old table was dropped while FOREIGN_KEY_CHECKS=0 was in effect.

The consequence is that we cannot insert or update any row in bar, because it can't check the value against a non-existant parent table.

mysql> insert into bar values (42);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`bar`, CONSTRAINT `bar_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `_foo_old` (`foo_id`))

We can fix the constraint by manually using ALTER TABLE, but we cant use pt-osc to do it, because pt-osc tries to copy the table definition including the broken FK constraint.

$ pt-online-schema-change h=localhost,D=test,t=bar --alter="drop foreign key _bar_ibfk_1, add foreign key (foo_id) references foo (foo_id)" --execute
. . .
Creating new table...
`test`.`bar` was not altered.
Error creating new table: DBD::mysql::db do failed: Cannot add foreign key constraint [for Statement "CREATE TABLE `test`.`_bar_new` (
  `foo_id` int(11) DEFAULT NULL,
  KEY `foo_id` (`foo_id`),
  CONSTRAINT `_bar_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `_foo_old` (`foo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1"] at /usr/bin/pt-online-schema-change line 9628.

I'd call this table an "orphan" but that's not exactly descriptive enough. Since it's referencing something that isn't there, let's call it a "conspiracy theorist" (joke).

I realize that the manual clearly describes the consequence of using the method "none". But what's missing from pt-online-schema-change is any way to fix the broken table while preserving continuous access to the table.

What's also missing is a stronger warning about the consequences of method "none" for users, either written in the manual or output by the tool. Perhaps the tool should even require a more explicit "force" option beyond --execute so that users don't get caught.

Another fix would be to eliminate the method "none" and take it out of the tool. It's not clear what if any legitimate use there is for this method.

Related branches

tags: added: pt-online-schema-change
Changed in percona-toolkit:
status: New → In Progress
assignee: nobody → Frank Cizmich (frank-cizmich)
importance: Undecided → Wishlist
Revision history for this message
Frank Cizmich (frank-cizmich) wrote :

Agreed, it's not clear what purpose this method has.
According to the docs:
"This method of handling foreign key constraints is provided so that the database administrator can disable the tool’s built-in functionality if desired."
Will include a stronger warning from the tool for now, and consider eliminating it in the future.

Changed in percona-toolkit:
milestone: none → 2.2.9
status: In Progress → Fix Committed
Revision history for this message
Frank Cizmich (frank-cizmich) wrote :

Added a warning and confirmation request when using alter-foreign-keys-method "none"
This can be overidden using the --force option

Changed in percona-toolkit:
status: Fix Committed → Fix Released
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to:

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.