Details
-
Bug
-
Status: Done
-
High
-
Resolution: Fixed
-
3.2.1, 3.3.0
-
None
-
None
-
mysql 5.7
-
Yes
-
No
-
1
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 */