Support tables with triggers and foreign keys
bancer opened this issue · 2 comments
https://github.com/facebookincubator/OnlineSchemaChange/wiki/Limitation
- Both original and new schema must NOT have existing TRIGGER
- Both original and new schema must NOT referencing or being referenced by a FOREIGN KEY
Would it be possible to add support for tables with triggers and foreign keys? Almost all our tables have foreign keys as we use MySQL Innodb engine and some of the huge tables have triggers.
Hi @bancer, thanks for the note. I'll address this in two parts for each limitation.
Supporting this style of schema changes for tables with foreign key checks enabled is not possible because in the process of rebuilding the table with the new schema, we need to re-insert the data it contained which mean it will not be able to satisfy those foreign key checks against tables that are not being rebuilt. Any method to get around this has to come from knowledge of the specific application and data semantics and is not something that a tool like OSC can account for. If we disabled the foreign key checks for the duration of the schema change, we would have no assurances that the data in the new table satisfies all the foreign key constraints because MySQL does not scan and cross-reference them and all the data they point to when enabling it.
Existing triggers are not supported because OSC uses a few triggers to track changes to the table it's rebuilding. Existing triggers could conflict with the triggers OSC has to put in place during its operation, without those we can't rebuild the table with the new schema while still being able to catch up to the changes the table has had while this was all happening.
Github released their online mysql schema changing system, "gh-ost" which uses "ROW" format binlogs to do the catchup instead of triggers like OSC uses, which gets around that limitation at least. You can learn more about it here: https://github.com/github/gh-ost.
For OSC we have it in mind to use row-based binlogs for the catchup (as described in issue #11) so someday OSC and gh-ost will at least have that in common. Until then there isn't anything we can do to address the limitations you bring up.
@adregner Thanks. I am aware about gh-ost project. There are similar issues there: github/gh-ost#153, github/gh-ost#507. It seems both problems are solvable there.