InnoDB constraints break table_migrator

[morganchristiansson]

After running a migration with table_migrator, tables which reference foreign keys on the migrated table now reference the old renamed table (using :migration_name => false):

CONSTRAINT item_lists_ibfk_user_id FOREIGN KEY (user_id) REFERENCES users_old (id)

This also prevents dropping the users_old table:

mysql> DROP TABLE users_old;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

The solution/workaround seems to be to identify all foreign keys and replace them with references to the newly created table?

Thanks!
Morgan

[morganchristiansson]

After further research it's very difficult to work around this.

Updating the FOREIGN KEYS that reference the table being rebuilt requires full table rebuilds of on each table that reference the table.

Looking at the documentation/article for Facebooks Online Schema Change tool
http://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932

They mention:

Assumptions that are validated in the code
2. No foreign keys should exist. Otherwise an error is returned.

In summary: to migrate you need to rebuild all tables which have foreign keys referncing the main table. If they are small that might be ok. If they are large you could use the swap trick on those too in which case tables which reference foreign keys to that table also need full table rebuilds.
Which will then propagate further as long as there are tables too large to be rebuild quickly.

[freels]

Yeah, I would say foreign key constraints are out of scope for this. You basically can't even rename tables with f-key constraints in mysql atomically, which would be necessary for this to work.

[morganchristiansson]

We are going to eventually remove foreign keys from our MySQL server and replace them with application level checks.

It would be nice if the documentation mentioned this or even validated this in the code.

Thanks,
Morgan