pt-online-schema-change uses AFTER triggers to keep the data in the old and new tables in sync, so it can't run on tables that already have AFTER triggers. However it is currently refusing to run on any table with any triggers, even if they are all BEFORE triggers. It should be able to run on tables with BEFORE triggers.
Steps to reproduce:
- Create a simple table with a BEFORE trigger:
use test;
create table foo (id int not null auto_increment primary key, val int);
create trigger tr_foo before insert on foo for each row set new.val = 7;
- Now try running pt-online-schema-change on that table:
pt-online-schema-change --alter "ADD COLUMN c1 INT" D=test,t=foo --execute
And it fails with this message:
The table `test`.`foo` has triggers. This tool needs to create its own triggers, so the table cannot already have triggers.
Suggested fix:
Replace these 3 lines of code:
# There cannot be any triggers on the original table.
my $sql = 'SHOW TRIGGERS FROM ' . $q->quote($orig_tbl->{db})
. ' LIKE ' . $q->literal_like($orig_tbl->{tbl});
With these 3 lines:
# There cannot be any AFTER triggers on the original table.
my $sql = "SHOW TRIGGERS FROM " . $q->quote($orig_tbl->{db})
. " WHERE `Table` = '$orig_tbl->{tbl}' AND `Timing` = 'AFTER'";
I've tested this change on MySQL 5.1, 5.5, and 5.6. I have not tested it on 5.0 yet, but as far as I know SHOW TRIGGERS with WHERE is supported back to 5.0.10.
Hi,
i started a post in the forum yesterday.
Can you give me more news on this assertion?
pt-online-
best regards