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!
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
Fyi it was identified that BEFORE triggers are not restored by 3.3.0 (the latest as of now).
You’d have to revert to 3.2.x if that affects you.
The next version should contain a fix of course, and perhaps even an optimization to use IN-PLACE rebuild constraints (by disabling FK checks, which is safe given it locks tables at that time).