pt-online-schema-change trigger problem

Question

I need to run an alter with Percona tool but it gives error:

The table gts.xml_bookings has triggers. This tool needs to create its own triggers, so the table cannot already have triggers

So I checked the triggers like this:

show triggers like 'xml_bookings'\G;

output:

Trigger: booking_status_update2
           Event: UPDATE
           Table: xml_bookings
       Statement: BEGIN
IF (OLD.backofficestatus <> NEW.backofficestatus
       AND left(OLD.booktime,10) <> curdate()) THEN
   insert into booking_status_changes
       set processid=new.processid,
           oldstatus=OLD.backofficestatus,
           newstatus=NEW.backofficestatus,
           website=NEW.website;
END IF;
END

         Timing: BEFORE
         Created: NULL
         sql_mode:
         Definer: root@localhost
         character_set_client: utf8
         collation_connection: utf8_general_ci
         Database Collation: latin1_swedish_ci
         1 row in set (0.00 sec)

ERROR: No query specified

What are the options I have? Eventually I need to use pt-online-schema-change to altering.

Answer 1

As you may know, pt-online-schema-change works in this way:

• Creates a new table identical to the original one, but empty
• Alters the new table
• Creates triggers on the original table to start keeping the tables in sync (update/delete of rows already copied, insert of new rows)
• Copies the rows in bunches
• Swap the table names

So triggers are a fundamental part of the process (unless the table is readonly or you are definitely sure that no one is going to modify it now). The problem is that, before MySQL 5.8 and MariaDB 10.3, you can have only one triggers for each combination of table + timing + event. For example, your table can have only one trigger BEFORE UPDATE.

One thing you can try is, turn your existing BEFORE UPDATE triggers to AFTER UPDATE - unless you already have an AFTER UPDATE trigger, this will do the trick. And do the same with INSERT and DELETE triggers.

Or try to use gh-ost from GitHub, as someone already suggested. This would work because this tool doesn't create any triggers: instead, it reads the binary log and applies the changes to the new table manually.

Answer 2

Have you posted the question on the Percona forums? Someone there should know for sure if there's a way to make it work with existing triggers.

Are you using replication? If you can get the trigger disabled on the master, but enabled on the slave, the application could read booking_status_changes from the slave.

Can the application change so the trigger isn't needed?

Have you looked at gh-ost as an alternative to pt-online-schema-change? https://github.com/github/gh-ost. It's replication based, so having exiting triggers wouldn't be a problem.

Good Luck!

Edit: I'll rephrase as statements...

You hit a limitation in pt-online-schema-change. Your options:

• Take downtime for a normal alter
• Alter the table on all the slaves first, and perform slave promotion
• Change the application to not require the trigger
• Use gh-ost instead of pt-online-schema-change, since it does not rely on triggers
• Disable the trigger on the master, and let the trigger fire on a Slave, reading the queued changes from the slave. Probably not ideal.

Answer 3

From Percona documentation, the pt-online-schema-change has a limitation working with triggers. "The use of triggers means that the tool will not work if any triggers are already defined on the table."