Currently pt-online-schema-change cannot run if table already has AFTER triggers. But it can, if add an option which instruct the tool to do following (see pseudo-code):
The workflow to add triggers would be as follows (from our own experiences doing this with trigger-based tools):
- lock the table in question (standard mysql lock tables write)
- pull the existing trigger code, call that block 1
- log trigger code into temporary file to avoid trigger definition loss in case of pt-osc failure
- generate the trigger code for pt-osc, call that block 2
- drop the existing trigger
- create a new trigger with block 1 and block 2 appended, wrap them in /* BLOCK 1 START */ comments or similar if that makes it easier to parse out
- repeat for other events (cover all of ins,upd,del)
- unlock table
To remove triggers, the reverse:
- lock the table with write lock
- pull the trigger code and parse out block 1
- drop the trigger
- create the trigger with only code from block 1
- repeat for other event
- unlock table
Do not make this option default and add a warning about database can be in inconsistent state if the tool or MySQL Server is killed during execution.
Current behavior is not to work with tables which have any kind of trigger, so this is a bit of a leap.
Many possibilities for things to go wrong, IMHO.
Will continue to analyze feasibility and perhaps prepare an experimental version.