pt-osc foreign-keys-method=none breaks constraints
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Fix Released
|
Wishlist
|
Frank Cizmich |
Bug Description
The alter-foreign-
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-
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_
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-
. . .
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/
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-
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
- Percona Toolkit developers: Pending requested
-
Diff: 43 lines (+15/-1)2 files modifiedbin/pt-online-schema-change (+14/-0)
t/pt-online-schema-change/basics.t (+1/-1)
tags: | added: pt-online-schema-change |
Changed in percona-toolkit: | |
status: | New → In Progress |
assignee: | nobody → Frank Cizmich (frank-cizmich) |
importance: | Undecided → Wishlist |
Changed in percona-toolkit: | |
milestone: | none → 2.2.9 |
status: | In Progress → Fix Committed |
Changed in percona-toolkit: | |
status: | Fix Committed → Fix Released |
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.