Triggers dropped after pt-osc usage

Good day,I’m facing strange issue, not for a first time. Sometimes it appears, sometimes it does not. I’m expecting it’s related to “drop_swap” method. Table stats_table2 is having 3 triggers.

after running, all looks mint:

At this moment table stats_table2 is not having 3 triggers. I’m always dumping triggers before running pt-osc - and in case of loosing triggers I’m importing them back again.Anyone faced similar behaviour, am I missing something in parameters? Any advice is highly appreciated! Thanks!

1 Like

If pt-online-schema-change drops triggers this would be bad bug.    Would you mind creating repeatable test case and filing it here ?
https://jira.percona.com/projects/PT/summary

1 Like

Thanks! Will try my best to cut related tables/dataset from our system! Appreciating quick answer!

1 Like

I have the same issue.

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' 
*/

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%')
    ;

update aaa set z=id where id=3;
SELECT * FROM bbb; -- modification_date should appear on 2 more rows
1 Like