github/gh-ost

Support triggers [community contribution welcome]

shlomi-noach opened this issue · 6 comments

shlomi-noach commented

Triggers can be supported by gh-ost. This is not on our immediate roadmap, and so it's an ideal opportunity for community contribution.

Some notes:

  • as far as I can see, all sorts of triggers could be supported (before|after, insert|delete|update)
  • throughout the migration process, nothing is really done; the ghost table should NOT have triggers.
  • during the cut-over phase we can copy+paste the triggers from original table to ghost table
  • Preferably the triggers are read at migration start, not at cut-over phase
  • triggers can be applied on the ghost table after the backlog is drained and the tables are in sync (after this.waitForEventsUpToLock())
  • triggers must be removed in any case of rollback, and in such case, before gh-ost resumes writes onto the ghost tables (no problem if original table resumes writes)
  • Note that at this time gh-ost supports two types of cut-over, and both must be supported.

Anyone who is interested to jump on this please let me know beforehand. Thank you!

allixsenos commented

in the meantime, it might be nice to have a --yes-it-has-triggers-ignore-them option... I'm looking at a 300 GB table which has triggers that I am happy to deal with manually... #152 made gh-ost check and bail on presence of triggers. a method of explicitly overriding that behavior would help greatly

  • 👍1
shlomi-noach commented

@allixsenos can you please open a new issue for this so we can track it?

MarkLeith commented

"Preferably the triggers are read at migration start, not at cut-over phase" - why?

Surely reading them at the point you want to recreate them is more correct (what if a trigger is altered during a migration)?

shlomi-noach commented

@MarkLeith you are correct. My initial thought was to save time. The cut-over phase is the time where tables are locked, and I am a bit afraid of spending this time on even more operations.

I can also argue that anyone modifying the triggers while migration is running is on the right track to shooting themselves in the foot.

Having said all that the most ultimatest correctest behavior is to read trigger info while cutting over.

bancer commented

@shlomi-noach Are there any new insights into this issue? I am interested to give it a try.

shlomi-noach commented

@bancer no new insights on my side; I haven't given this thought.