1 # Tests which involve triggers and transactions
2 # (or just InnoDB storage engine)
3 --source include/have_innodb.inc
6 drop table if exists t1;
9 # Test for bug #18153 "OPTIMIZE/ALTER on transactional tables corrupt
10 # triggers/triggers are lost".
12 create table t1 (a varchar(16), b int) engine=innodb;
14 create trigger t1_bi before insert on t1 for each row
16 set new.a := upper(new.a);
17 set new.b := new.b + 3;
20 select trigger_schema, trigger_name, event_object_schema,
21 event_object_table, action_statement from information_schema.triggers
22 where event_object_schema = 'test' and event_object_table = 't1';
23 insert into t1 values ('The Lion', 10);
26 select trigger_schema, trigger_name, event_object_schema,
27 event_object_table, action_statement from information_schema.triggers
28 where event_object_schema = 'test' and event_object_table = 't1';
29 insert into t1 values ('The Unicorn', 20);
31 alter table t1 add column c int default 0;
32 select trigger_schema, trigger_name, event_object_schema,
33 event_object_table, action_statement from information_schema.triggers
34 where event_object_schema = 'test' and event_object_table = 't1';
35 insert into t1 values ('Alice', 30, 1);
37 # Special tricky cases allowed by ALTER TABLE ... RENAME
38 alter table t1 rename to t1;
39 select trigger_schema, trigger_name, event_object_schema,
40 event_object_table, action_statement from information_schema.triggers
41 where event_object_schema = 'test' and event_object_table = 't1';
42 insert into t1 values ('The Crown', 40, 1);
44 alter table t1 rename to t1, add column d int default 0;
45 select trigger_schema, trigger_name, event_object_schema,
46 event_object_table, action_statement from information_schema.triggers
47 where event_object_schema = 'test' and event_object_table = 't1';
48 insert into t1 values ('The Pie', 50, 1, 1);
53 --echo Bug#26141 mixing table types in trigger causes full
54 --echo table lock on innodb table
56 --echo Ensure we do not open and lock tables for the triggers we do not
60 drop table if exists t1, t2, t3;
61 drop trigger if exists trg_bug26141_au;
62 drop trigger if exists trg_bug26141_ai;
64 # Note, for InnoDB to allow concurrent UPDATE and INSERT the
65 # table must have a unique key.
66 create table t1 (c int primary key) engine=innodb;
67 create table t2 (c int) engine=myisam;
68 create table t3 (c int) engine=myisam;
69 insert into t1 (c) values (1);
72 create trigger trg_bug26141_ai after insert on t1
75 insert into t2 (c) values (1);
76 # We need the 'sync' lock to synchronously wait in connection 2 till
77 # the moment when the trigger acquired all the locks.
78 select release_lock("lock_bug26141_sync") into @a;
79 # 1000 is time in seconds of lock wait timeout -- this is a way
80 # to cause a manageable sleep up to 1000 seconds
81 select get_lock("lock_bug26141_wait", 1000) into @a;
84 create trigger trg_bug26141_au after update on t1
87 insert into t3 (c) values (1);
91 # Establish an alternative connection.
92 --connect (connection_aux,localhost,root,,test,,)
93 --connect (connection_update,localhost,root,,test,,)
95 connection connection_aux;
96 # Lock the wait lock, it must not be locked, so specify zero timeout.
97 select get_lock("lock_bug26141_wait", 0);
102 # Run the trigger synchronously
104 select get_lock("lock_bug26141_sync", /* must not be priorly locked */ 0);
105 # Will acquire the table level locks, perform the insert into t2,
106 # release the sync lock and block on the wait lock.
107 send insert into t1 (c) values (2);
109 connection connection_update;
110 # Wait for the trigger to acquire its locks and unlock the sync lock.
111 select get_lock("lock_bug26141_sync", 1000);
113 # This must continue: after the fix for the bug, we do not
114 # open tables for t2, and with c=4 innobase allows the update
115 # to run concurrently with insert.
116 update t1 set c=3 where c=1;
117 select release_lock("lock_bug26141_sync");
118 connection connection_aux;
119 select release_lock("lock_bug26141_wait");
126 # Drops the trigger as well.
127 drop table t1, t2, t3;
128 disconnect connection_update;
129 disconnect connection_aux;
132 # Bug#34643: TRUNCATE crash if trigger and foreign key.
136 DROP TABLE IF EXISTS t1;
137 DROP TABLE IF EXISTS t2;
140 CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=innodb;
141 CREATE TABLE t2(b INT, FOREIGN KEY(b) REFERENCES t1(a)) ENGINE=innodb;
143 INSERT INTO t1 VALUES (1);
145 CREATE TRIGGER t1_bd BEFORE DELETE ON t1 FOR EACH ROW SET @a = 1;
146 CREATE TRIGGER t1_ad AFTER DELETE ON t1 FOR EACH ROW SET @b = 1;
155 INSERT INTO t1 VALUES (1);
164 --echo End of 5.0 tests
167 --echo Trigger fired multiple times leads to gaps in auto_increment sequence
168 create table t1 (a int, val char(1)) engine=InnoDB;
169 create table t2 (b int auto_increment primary key,
170 val char(1)) engine=InnoDB;
171 create trigger t1_after_insert after
172 insert on t1 for each row insert into t2 set val=NEW.val;
173 insert into t1 values ( 123, 'a'), ( 123, 'b'), ( 123, 'c'),
174 (123, 'd'), (123, 'e'), (123, 'f'), (123, 'g');
175 insert into t1 values ( 654, 'a'), ( 654, 'b'), ( 654, 'c'),
176 (654, 'd'), (654, 'e'), (654, 'f'), (654, 'g');
177 select * from t2 order by b;
178 drop trigger t1_after_insert;