Not the answer you need?
Register and ask your own question!

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

sairamsairam EntrantCurrent User Role Beginner
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.

Comments

  • morganmorgan Entrant Inactive User Role Beginner
    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.