5 ms·
Cannot it add FK constraints after the ghost table is fully populated?
by codedokode 8y ago
Cannot it add FK constraints after the ghost table is fully populated?
- perlgeek 8y agoThe problem is that adding FK constraints is another schema change, which causes MySQL to copy the whole table, and lock the table during this time -- which is precisely what gh-ost tries to avoid. Worse, foreign keys from other tables to the one that is being changed would need to be updated as well, blocking those tables in turn.
- zmoazeni 8y ago> which causes MySQL to copy the whole table This is wrong on a couple levels. First it doesn't copy the whole table: https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-... However, it can take a while if MySQL is evaluating the consistency. But you can disable that with `SET FOREIGN_KEY_CHECKS = 0` which turns it into a metadata change (nearly instantaneous). You still will need to check for violations, but you can do that in a more friendly-to-load manner, and of course will need to deal with any violations manually. But that strategy is a good middle ground to all-or-nothing FKs. Edit: Whoops, looks like I was wrong on the table-copy part. Per "Otherwise, only the COPY algorithm is supported." So it does copy the data when `FOREIGN_KEY_CHECKS=1` (the default)
- zmoazeni 8y agoYeah. See https://github.com/github/gh-ost/issues/507#issuecomment-338725563 https://github.com/github/gh-ost/issues/507#issuecomment-338... on how to do it in a performant way.