Allow table modification/arbitrary commands during the cutover
- Dominant language
- Go
- Stars
- 13.6k
- Forks
- 1.4k
- Avg merge
- 2h 31m
- Merged PRs (30d)
- 4
Description
We are in need for the implementation of the triggers support], since we have a core triggers usage, so we've put some thoughts into it before proceeding on the implementation
A complication that came up, and it's not considered in the [related issue](https://github.com/github/gh-ost/issues/153), is the use case where Gh-ost is used to change a column that is used by an underlying trigger.
Simple use case structure:
- a table with an `utf8` column (say, `mytable.myutf8col`);
- a trigger the on the table, that performs a comparison on `myutf8col`, using the `utf8_bin` collation (eg. `NOT OLD.myutf8col <=> NEW.myutf8col COLLATE utf8_bin`).
when the user will perform the `utf8mb4` conversion™, a basic triggers support will cause this to fail.
Now, I can't talk for the diffusion of such cases, but I think it's reasonable to assume that a trigger working on a column being changed, is not a far-fetched use case.
I don't think there's an automated solution to this; although triggers are not simple blobs of text from the MySQL server perspective, they are from the libraries/tools.
A hypothetical functionality that would cover this case is to allow the user to execute, during the cutover, arbitrary SQL. This would (need to) be paired with a triggers ignoring functionality, leading to a gh-ost invocation like:
```sh
$ trigger_creation_sql="
CREATE TRIGGER mytrigger
AFTER UPDATE ON _mytable_gho
...
"
$ gh-ost [...] \
--table="mytable" \
--alter="MODIFY myutf8col CHAR CHARACTER SET utf8mb4" \
--ignore-triggers \
--invoke-statement "$trigger_creation_sql"
```
Now, this is a more generic functionality than the more focused, so there's certainly the argument of implementing the narrowest and most functionality in order to accomplish a task, however, a straight implementation of #153 would not cover the above use case.
Contributor guide
Assessment
This issue has not been assessed yet.