Renaming an InnoDB table without updating foreign key references to it?

Question

I am trying to replace an InnoDB table with a new table, and I want all foreign key references that point to the old table to point to the new table.

So I tried this:

SET foreign_key_checks = 0;
ALTER TABLE foo RENAME foo_old;
ALTER TABLE foo_new RENAME foo;

Unfortunately, even with foreign_key_checks disabled, all references pointing to foo are changed to point to foo_old. Now I am looking for either

• a way to change the foreign key references back without rebuilding the entire table, OR
• a way to rename a table without updating foreign key references.

I tried dropping the foreign keys and recreating them, but since the tables are huge, it takes hours. The whole point of replacing the table was to make a schema change with limited downtime.

Answer 1

Old questions, but following is a possible way around. Basically move the data rather than renaming the tables. You need to of course make sure the new data adhere to the foreign key rules.

SET foreign_key_checks = 0;
CREATE TABLE IF NOT EXISTS foo_old LIKE foo;
INSERT INTO foo_old SELECT * FROM foo;
TRUNCATE foo;
INSERT INTO foo SELECT * FROM foo_new;

Make sure you run it as one query so the foreign_key_checks applies to the whole thing. Hope this helps.

Answer 2

Unfortunately I don't think that there's a way around your problem without dropping the foreign keys first and they re-creating them.

This is minor but I spotted something with your RENAME commands too. You can chain them together and unless all steps were successful it rolls back all of the other renames. Here's the syntax:

RENAME TABLE foo TO foo_old, foo_new TO foo;

Answer 3

On MySQL 5.6 with innodb_file_per_table=ON allows you to swap the table spaces on the fly. This can't be done completely using SQL as file operations need to be performed separately. First prepare the foo_new table to be copied and drop the foo data:

SET foreign_key_checks = 0;
ALTER TABLE foo DISCARD TABLESPACE;
FLUSH TABLES foo_new FOR EXPORT;

At this point you need to copy the relevant InnoDB files to correct name. Files are stored in your data directory. On Debian, for example, they are by default in /var/lib/mysql/yourdatabase and files are foo_new.ibd, foo_new.cfg and foo_new.frm. Copy them to foo.ibd, foo.cfg and foo.frm, respectively. For example:

$ cp foo_new.ibd foo.ibd
$ cp foo_new.frm foo.frm
$ cp foo_new.cfg foo.cfg

Pay attention that MySQL has access to the new files (e.g. they have correct owner, access rights). Once done you can import the table again and enable foreign keys:

UNLOCK TABLES;
ALTER TABLE foo IMPORT TABLESPACE;
SET foreign_key_checks = 1;

This only copies foo_new to foo. Repeat the steps if you need to copy foo to foo_old.

Answer 4

InnoDB uses the internal pointer of the table in the foreign key, so no matter the name given to this table (using RENAME), constraints will be preserved including when you use SET foreign_key_checks = 0.

a way to change the foreign key references back without rebuilding the entire table

Using innodb_file_per_table=ON would be the nearest we can go (see @vhu answer).

a way to rename a table without updating foreign key references.

The solution which will imply the lowest downtime and effort, and does not require shell access to the server, might simply be to work with two databases, and switch them on due time, if the data does not change very much

It could be even faster to synchronize every other table than the big one, or temporary duplicate mysql command (delete, update, insert) in your application until the switch if you do have some changes.

Answer 5

I found a way around this... you just drop the source table instead of renaming it.

For this example, we'll call the table 'mytbl'.

1. create copy of the source table, e.g. 'mytbl_new'
2. copy data into the new table
3. drop the source table 'mytbl'
4. rename 'mytbl_new' to 'mytbl'

The only downside is you can't keep a backup of your original table, but you could mysqldump it before hand. Alternatively, you could create an additional table copy if you wanted a verbatim copy of the original table.