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

Using foreign_key_checks=0 with rebuild_constraints

gregersgregers Current User Role Poster

Hello!

My team is trying to figure out how to ALTER a large table in production which has a couple large related tables (via fk constraints)

We have done some testing with the rebuild_constraints option and found that during the rebuild constraints portion of the migration that the table locking would not be acceptable for running in our production environment

We are also aware of the drop_swap option which should allow us to get around this but we are nervous of course about the warning that failures to rename the table could mean you lose the table.

One option we have found that appears to sidestep this is to pass --set-vars foreign_key_checks=0 to the command. As we understand it this would mean:

  • No guarantee of data consistency (as with the drop_swap approach)
  • However no risk of being stuck in limbo if the rename fails

Just curious to see if anyone has experience with this type of approach or can see better alternatives? I've poked around on the forums and online and have not seen references to using foreign_key_checks=0 along with rebuild_constraints.

Happy to provide more info / context if required and very much appreciate any experience / insight the community here might have!

Thanks again!

Greg

Answers

  • DGBDGB Percona Staff Role
    Hi Greg

    To be honest I wouldn’t recommend to disable the FK check unless you are planning to check data relationships after the operation and know how to deal with potential invalid constraints. 

    Another approach that I would suggest is, if possible, run the ALTER directly but doing it first on the replicas and for the Primary execute a failover and when the older primary is now a replica, execute the alter there. The “risk” of the failover are potentially less and somehow more easy to manage 
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.