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

Can I safely use --set-vars foreign_key_checks=0 with rebuild constraints

gregersgregers Current User Role Novice

Hello!

Sorry if this has been asked! I've taken a look around and could not find any questions

My team is trying to figure out how to apply a migration to a large table which has a couple other large dependent child tables (Via foreign key relationships of course).

In this case running with rebuild constraints in our production environment appears to involve an unacceptable amount of locking when it comes time to rebuild the constraints.

As the question states I'm wondering if it would be reasonable to pass --set-vars foreign_key_checks=0 into the tool?

I've done some testing of this and it appears to instantly rebuild the constraints as if they were not there and any deletes will cascade as expected once the migration completes.

If I understand correctly this would be quite similar to using the drop_swap option for https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html#cmdoption-pt-online-schema-change-alter-foreign-keys-method... in that you lose any guarantee of data consistency during the operation... But without the drawback of opening yourself up to losing a table if something goes wrong during the swap!

Just wondering if there are any other hidden gotchas that we might be missing? I've looked around on the forum and online and have not seen mention of this approach so figured best to check!

Ofc let me know if I can provide any more info / context here

Thanks so much for any advice insight!

Cheers

Greg

Answers

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.