pt-online-schema-change will not run on tables with BEFORE triggers

[Ike Walker]

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.

[br56 (brravon)]

Hi,

i started a post in the forum yesterday.

Can you give me more news on this assertion?

pt-online-schema-change uses AFTER triggers to keep the data

best regards

[br56 (brravon)]

I've tried pt-online-schema-change and that's right, this tool put 3 triggers in AFTER on the online table !
So with your patch we can start this tool and keep our before triggers.

[Nilnandan Joshi (nilnandan-joshi)]

Verified locally. After that patch, we can able to add before triggers in table.

nil@Dell-XPS:~/sandboxes/msb_5_5_36$ pt-online-schema-change --alter "ADD COLUMN c1 INT" D=test,t=foo --user=msandbox --password=msandbox --socket=/tmp/mysql_sandbox5536.sock --execute
No slaves found. See --recursion-method if host nilnandan-Dell-XPS has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
The table `test`.`foo` has triggers. This tool needs to create its own triggers, so the table cannot already have triggers.
nil@Dell-XPS:~/sandboxes/msb_5_5_36$

nil@Dell-XPS:~/sandboxes/msb_5_5_36$ vim /usr/bin/pt-online-schema-changenil@Dell-XPS:~/sandboxes/msb_5_5_36$ sudo vim /usr/bin/pt-online-schema-change

------ADD PATCH FOR BEFORE TRIGGER

nil@Dell-XPS:~/sandboxes/msb_5_5_36$ pt-online-schema-change --alter "ADD COLUMN c1 INT" D=test,t=foo --user=msandbox --password=msandbox --socket=/tmp/mysql_sandbox5536.sock --execute
No slaves found. See --recursion-method if host nilnandan-Dell-XPS has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`foo`...
Creating new table...
Created new table test._foo_new OK.
Altering new table...
Altered `test`.`_foo_new` OK.
2014-05-07T10:13:07 Creating triggers...
2014-05-07T10:13:07 Created triggers OK.
2014-05-07T10:13:07 Copying approximately 1 rows...
2014-05-07T10:13:07 Copied rows OK.
2014-05-07T10:13:07 Swapping tables...
2014-05-07T10:13:07 Swapped original and new tables OK.
2014-05-07T10:13:07 Dropping old table...
2014-05-07T10:13:07 Dropped old table `test`.`_foo_old` OK.
2014-05-07T10:13:07 Dropping triggers...
2014-05-07T10:13:07 Dropped triggers OK.
Successfully altered `test`.`foo`.
nil@Dell-XPS:~/sandboxes/msb_5_5_36$

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-637