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

[Bill Karwin]

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

Example:

create table test.foo (foo_id int primary key);
insert into foo values (42);
create table test.bar (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:

mysql> SHOW CREATE TABLE bar\G
CREATE TABLE `bar` (
  `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;

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.

[Frank Cizmich (frank-cizmich)]

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.

[Frank Cizmich (frank-cizmich)]

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

[Shahriyar Rzayev (rzayev-sehriyar)]

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