Drop-FK-pt-online-schema-change.

[sairam]

Hi,

When i try to drop a foreign key using pt-online-schema-change, it says :

Error altering new table dbname._tablename_new: DBD::mysql::db do failed: Error on rename of ‘./dbname/_tablename_new’ to ‘./db/#sql2-678b-a31’ (errno: 152) at ./pt-online-schema-change line 5438

On analysis, found there is no FK with the specified name in the new table which pt-osc creates to alter. ( It adds a leading underscore to the FK names in the new table ). Is this the default behaviour of pt-osc for drop fk or am i missing some thing ? If this the default behaviour, what are the other possible ways to drop Fks using pt-osc ? please help.

[morgan]

The error you are seeing is the very unhelpful error message that MySQL issues when you try to drop an invalid FK name.

The reason that the name you are using is invalid is due to the way pt-osc handles FKs.
This is specifically referred to in the documentation here:

“DROP FOREIGN KEY constraint_name requires specifying _constraint_name rather than the real constraint_name. Due to a limitation in MySQL, pt-online-schema-change adds a leading underscore to foreign key constraint names when creating the new table. For example, to drop this contraint:”

Docs:
http://www.percona.com/doc/percona-toolkit/2.1/pt-online-sch ema-change.html

TL;DR; Prefix an underscore to the constraint name when trying to drop it with this tool.

Regards,
Morgan