Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-1919

drop_swap can drop triggers

Details

    • Bug
    • Status: Done
    • High
    • Resolution: Fixed
    • 3.2.1, 3.3.0
    • 3.3.1
    • None
    • None
    • mysql 5.7

    • 1
    • 04 - Core
    • Yes
    • No

    Description

      Calling a ptosc migration with drop_swap mode (or auto mode which could choose drop swap) has deleted some triggers.

      (3.2.1 on mysql 5.7)

       

      drop table if exists bbb;
      drop table if exists aaa;
      CREATE TABLE aaa (
       id int NOT NULL AUTO_INCREMENT,
       c int DEFAULT NULL,
       z int DEFAULT NULL,
       PRIMARY KEY (`id`),
       CONSTRAINT `FK_z` FOREIGN KEY (`z`) REFERENCES aaa (`id`)
      );
      CREATE TABLE bbb (
       id int NOT NULL AUTO_INCREMENT,
       aaa_id int DEFAULT NULL,
       modification_date datetime(3),
       PRIMARY KEY (`id`),
       CONSTRAINT `FK_aaa_id` FOREIGN KEY (`aaa_id`) REFERENCES aaa (`id`)
      );
      
      CREATE TRIGGER before_aaa_upd BEFORE UPDATE ON aaa FOR EACH ROW set new.c = old.c+1;
       DELIMITER $$
      CREATE TRIGGER after_aaa_upd AFTER UPDATE ON aaa FOR EACH ROW BEGIN UPDATE bbb SET modification_date = UTC_TIMESTAMP(3) WHERE aaa_id = NEW.id; END;
      $$
      DELIMITER ;
      
      select definer, trigger_name name, action_timing timing, event_manipulation manip, event_object_table tbl, action_statement stmt
      -- , "]---[", T.* 
      from information_schema.triggers T
      where trigger_schema = database() and (event_object_table like 'aaa%' or event_object_table like 'bbb%') ;
      
      insert into aaa (c) values (10),(20),(30),(40),(50);
       SELECT * FROM aaa;
       insert into bbb (aaa_id) values (2), (2), (3), (3);
       SELECT * FROM bbb;
       update aaa set z=id where id=2;
       SELECT * FROM bbb; -- modification_date should appear on 2 rows
      
      

       

      run ptosc migration

       ./ptosc/bin/pt-online-schema-change -h _________ -P 3306 -u _______ -p ________
       D=mydatabase,t=aaa 
       --execute --statistics --print --no-version-check
       --preserve-triggers 
       --alter-foreign-keys-method drop_swap 
       --alter 'ADD COLUMN f2 int(11) default 42' 
      

       

      check:

      select definer, trigger_name name, action_timing timing, event_manipulation manip, event_object_table tbl, action_statement stmt 
      from information_schema.triggers T
      where trigger_schema = database() and (event_object_table like 'aaa%' or event_object_table like 'bbb%') ;
      
      update aaa set z=id where id=3;
      SELECT * FROM bbb; /* modification_date should appear on 2 more rows */
      
      

       

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              bob2021 bob
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Smart Checklist