Mysql – pt-online-schema-change trigger problem

MySQLmysql-5.6perconatrigger

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.

Best Answer

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.

Related Question