Details
-
New Feature
-
Status: Done
-
Medium
-
Resolution: Fixed
-
3.0.2
-
None
-
None
Description
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.
Launchpad: [https://bugs.launchpad.net/percona-toolkit/+bug/1491133
]
Acceptance Criteria for params:
Scenario The --preserve-triggers feature needs to delete the old table and triggers and recreate them into for new table. Since it is not possible to have duplicated trigger names, the old table and triggers MUST be deleted and recreated Feature: --preserve and --no-drop-old-table params When --preserve-triggers param is specified And --no-drop-old-table was specified Then Exit with an error message Feature: --preserve triggers and --no-swap-tables params When --preserve-triggers param is specified And --no-swap-tables was specified Then exit with an error message Feature: --preserve triggers and --no-drop-triggers params When --preserve-triggers param is specified And --no-drop-triggers was specified Then exit with an error message