pt-online-schema-change dropping FK limitation isn't documented

[Nick Hatch]

Using pt-online-schema-change v2.1.2.

Dropping a foreign key constraint fails, probably due to the change in 2.1 that moves away from CREATE TABLE LIKE, in favor of recreating the new table explicitly with renamed FK constraints.

(Docs say, "Due to a limitation in MySQL, foreign keys will not have the same names after the ALTER that they did prior to it. The tool has to rename the foreign key when it redefines it, which adds a leading underscore to the name.")

# Steps to reproduce:
CREATE DATABASE osc_fk_testing;
use osc_fk_testing;

CREATE TABLE `foo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
);

CREATE TABLE `bar` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`foo_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `foo_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`)
);

INSERT INTO `foo` VALUES(1), (2), (3), (4), (5);
INSERT INTO `bar` (foo_id) VALUES(1), (2), (3), (4);

pt-online-schema-change h=host.example.com,D=osc_fk_testing,t=bar -uroot -pXXXXXX --alter 'DROP FOREIGN KEY foo_ibfk_1;' --nodrop-old-table --print --execute

# Result:

Altering `osc_fk_testing`.`bar`...
Creating new table...
CREATE TABLE `osc_fk_testing`.`_bar_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `foo_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `foo_ibfk_1` (`foo_id`),
  CONSTRAINT `_foo_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
Created new table osc_fk_testing._bar_new OK.
Altering new table...
ALTER TABLE `osc_fk_testing`.`_bar_new` DROP FOREIGN KEY foo_ibfk_1;
Dropping new table...
DROP TABLE IF EXISTS `osc_fk_testing`.`_bar_new`;
Dropped new table OK.
`osc_fk_testing`.`bar` was not altered.
Error altering new table `osc_fk_testing`.`_bar_new`: DBD::mysql::db do failed: Error on rename of './osc_fk_testing/_bar_new' to './osc_fk_testing/#sql2-709e-ca' (errno: 152) [for Statement "ALTER TABLE `osc_fk_testing`.`_bar_new` DROP FOREIGN KEY foo_ibfk_1;"] at /usr/bin/pt-online-schema-change line 5912.

Expected result:
pt-online-schema changes uses the modified name of the new constraint (`_foo_ibfk_1`) when modifying the new table, and the constraint is removed successfully.

MySQL: 5.5.12, on Amazon RDS.

[Baron Schwartz (baron-xaprb)]

I think we will need to 'fix' this one by documenting it, because we don't parse the ALTER statement, so we don't know its contents. The documentation can mention that the user should modify the DROP FOREIGN KEY clause to refer to the key's modified name.

[Nick Hatch (nicholas-hatch)]

Thanks, Baron.

Adding this caveat to the documentation seems reasonable, I can confirm that the workaround of referring to the modified name works fine.

[Rares Mirica (mrares)]

Sadly the documentation still does not prominently mention the need to prepend the foreign key name with an _ (underscore) when dropping foreign keys. (thankfully, it's obvious in code)

[Shahriyar Rzayev (rzayev-sehriyar)]

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