News for hackers. Stuff that matters.
Re:GitHub's online schema migration for MySQL by codedokode on Thursday May 03, 2018 @02:11AM (#16983708)
Cannot it add FK constraints after the ghost table is fully populated?
Re:GitHub's online schema migration for MySQL by perlgeek on Thursday May 03, 2018 @02:18AM (#16983740)
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.

Re:GitHub's online schema migration for MySQL by zmoazeni on Thursday May 03, 2018 @01:33PM (#16987947)
> 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-...

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)

Re:GitHub's online schema migration for MySQL by zmoazeni on Thursday May 03, 2018 @01:33PM (#16987952)
Yeah. See https://github.com/github/gh-ost/issues/507#issuecomment-338... on how to do it in a performant way.