4 ms·

This is an unfortunate by product due to the way that gh-ost is implemented. It is simply not possible to run it with a FK constraint. The reason is that since

by pwnna 8y ago

This is an unfortunate by product due to the way that gh-ost is implemented. It is simply not possible to run it with a FK constraint. The reason is that since it replays the binlogs on the ghost table while the ghost table is not fully populated, the FK constraint will cause some of statements to fail. The data move from the original to the ghost table cannot be completed.

codedokode 8y ago
Cannot it add FK constraints after the ghost table is fully populated?
- perlgeek 8y ago
  The 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 ago
  Yeah. 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.

by pwnna 8y ago

codedokode 8y ago

Cannot it add FK constraints after the ghost table is fully populated?

perlgeek 8y ago
The 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 ago
Yeah. 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.