CODEWITHSUNDEEP
mysqlforeign-keysinnodb
Simon
Simon

Reputation: 25993

Can I change the table that a mysql foreign key references without locking anything?

We have a large schema migration to run, and we don't want the site to go down for ages, so we're trying to do it on the fly and without read locking the tables involved. The tables involved only get written to at specific times of the day, so we have a window in which we can afford to have write locks; but while a lot of the mysql docs claim ALTER TABLE should only write lock tables, in our experience anything nontrivial such as adding an index read locks it.

So, we've had to do the migrations by hand. We've had some success with the following procedure:

  1. CREATE TABLE new_mytable LIKE mytable;
  2. apply the migration to new_mytable
  3. mysqldump -t mydb mytable > mytable.sql
  4. use sed to change mytable.sql to insert into the correct columns of new_mytable
  5. mysql mydb < mytable.sql
  6. drop foreign keys on mytable - they're not copied by CREATE TABLE ... LIKE ...
  7. add foreign keys to new_mytable
  8. RENAME TABLE mytable TO old_mytable, new_mytable TO mytable
  9. run a few sanity checks
  10. DROP TABLE old_mytable

As I say, the tables involved only get written to at specific times of the day, so we're certain that the data stays consistent - otherwise we wouldn't dream of trying something like that. Even so, we've hit a problem. Until now, we've only tried it on tables where the FKs point outwards. Today I tried it on a table which was referred to by several other tables. When I dropped the old table, I was told that a foreign key constraint failed, and was horrified to see that the referring tables now refer to the old table.

Now, we could drop the FKs and re-add them to the new one, but the process is fragile enough as it is; there are a lot of tables pointing to this one; and re-adding the keys may give us read locks. We're certain - certain - that the new table contains all the same data as the first one, so what would be nice would be to change the referent of those FKs without dropping and adding them. Is there a way to manipulate the DB such that the existing FKs are altered to refer to the new table?

Upvotes: 2

Views: 1362

Answers (1)

Johan
Johan

Reputation: 76713

Provided you know how to alter the tables themselves.

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

...Alter the tables and change the keys

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

Upvotes: 2

Related Questions