Debian Codegraph @

mysql-test/suite/ndb/t/ndb_trigger.test

raw
1 # Tests which involve triggers and NDB storage engine
2 --source include/have_multi_ndb.inc
3
4 #
5 # Test for bug#18437 "Wrong values inserted with a before update
6 # trigger on NDB table". SQL-layer didn't properly inform handler
7 # about fields which were read and set in triggers. In some cases
8 # this resulted in incorrect (garbage) values of OLD variables and
9 # lost changes to NEW variables.
10 # You can find similar tests for ON INSERT triggers in federated.test
11 # since this engine so far is the only engine in MySQL which cares
12 # about field mark-up during handler::write_row() operation.
13 #
14
15 create table t1 (id int primary key, a int not null, b decimal (63,30) default 0) engine=ndb;
16 create table t2 (op char(1), a int not null, b decimal (63,30)) engine=ndb;
17 --disable_warnings
18 create table t3 engine=ndb select 1 as i;
19 --enable_warnings
20 create table t4 (a int not null primary key, b int) engine=ndb;
21 create table t5 (a int not null primary key, b int) engine=ndb;
22
23 delimiter //;
24 create trigger t1_bu before update on t1 for each row
25 begin
26 insert into t2 values ("u", old.a, old.b);
27 set new.b = old.b + 10;
28 end;//
29 create trigger t1_bd before delete on t1 for each row
30 begin
31 insert into t2 values ("d", old.a, old.b);
32 end;//
33 create trigger t4_au after update on t4
34 for each row begin
35 update t5 set b = b+1;
36 end;
37 //
38 create trigger t4_ad after delete on t4
39 for each row begin
40 update t5 set b = b+1;
41 end;
42 //
43 delimiter ;//
44
45 insert into t1 values (1, 1, 1.05), (2, 2, 2.05), (3, 3, 3.05), (4, 4, 4.05);
46 insert into t4 values (1,1), (2,2), (3,3), (4, 4);
47 insert into t5 values (1,0);
48
49 # Check that usual update works as it should
50 update t1 set a=5 where a != 3;
51 select * from t1 order by id;
52 select * from t2 order by op, a, b;
53 delete from t2;
54 # Check that everything works for multi-update
55 update t1, t3 set a=6 where a = 5;
56 select * from t1 order by id;
57 select * from t2 order by op, a, b;
58 delete from t2;
59 # Check for delete
60 delete from t1 where a != 3;
61 select * from t1 order by id;
62 select * from t2 order by op, a, b;
63 delete from t2;
64 # Check for multi-delete
65 insert into t1 values (1, 1, 1.05), (2, 2, 2.05), (4, 4, 4.05);
66 delete t1 from t1, t3 where a != 3;
67 select * from t1 order by id;
68 select * from t2 order by op, a, b;
69 delete from t2;
70 # Check for insert ... on duplicate key update
71 insert into t1 values (4, 4, 4.05);
72 insert into t1 (id, a) values (4, 1), (3, 1) on duplicate key update a= a + 1;
73 select * from t1 order by id;
74 select * from t2 order by op, a, b;
75 delete from t2;
76 # Check for insert ... select ... on duplicate key update
77 delete from t3;
78 insert into t3 values (4), (3);
79 insert into t1 (id, a) (select i, 1 from t3) on duplicate key update a= a + 1;
80 select * from t1 order by id;
81 select * from t2 order by op, a, b;
82 delete from t2;
83 # Check for replace
84 replace into t1 (id, a) values (4, 1), (3, 1);
85 select * from t1 order by id;
86 select * from t2 order by op, a, b;
87 delete from t1;
88 delete from t2;
89 # Check for replace ... select ...
90 insert into t1 values (3, 1, 1.05), (4, 1, 2.05);
91 replace into t1 (id, a) (select i, 2 from t3);
92 select * from t1 order by id;
93 select * from t2 order by op, a, b;
94 delete from t1;
95 delete from t2;
96 # Check for load data replace
97 insert into t1 values (3, 1, 1.05), (5, 2, 2.05);
98 load data infile '../../../std_data/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (id, a);
99 select * from t1 order by id;
100 select * from t2 order by op, a, b;
101 update t4 set b = 10 where a = 1;
102 select * from t5 order by a;
103 update t5 set b = 0;
104 delete from t4 where a = 1;
105 select * from t5 order by a;
106 drop trigger t4_au;
107 drop trigger t4_ad;
108
109 drop table t1, t2, t3, t4, t5;
110 # Test for bug#26242
111 # Verify that AFTER UPDATE/DELETE triggers are executed
112 # after the change has actually taken place
113
114 CREATE TABLE t1 (
115 id INT NOT NULL PRIMARY KEY,
116 xy INT
117 ) ENGINE=ndbcluster;
118
119 INSERT INTO t1 VALUES (1, 0);
120
121 DELIMITER //;
122 CREATE TRIGGER t1_update AFTER UPDATE ON t1 FOR EACH ROW BEGIN REPLACE INTO t2 SELECT * FROM t1 WHERE t1.id = NEW.id; END //
123 DELIMITER ;//
124
125 CREATE TABLE t2 (
126 id INT NOT NULL PRIMARY KEY,
127 xy INT
128 ) ENGINE=ndbcluster;
129
130 INSERT INTO t2 VALUES (2, 0);
131
132 CREATE TABLE t3 (id INT NOT NULL PRIMARY KEY) ENGINE=ndbcluster;
133
134 INSERT INTO t3 VALUES (1);
135
136 CREATE TABLE t4 LIKE t1;
137
138 DELIMITER //;
139 CREATE TRIGGER t4_update AFTER UPDATE ON t4 FOR EACH ROW BEGIN REPLACE INTO t5 SELECT * FROM t4 WHERE t4.id = NEW.id; END //
140 DELIMITER ;//
141
142 CREATE TABLE t5 LIKE t2;
143
144 UPDATE t1 SET xy = 3 WHERE id = 1;
145 SELECT xy FROM t1 where id = 1;
146 SELECT xy FROM t2 where id = 1;
147
148 UPDATE t1 SET xy = 4 WHERE id IN (SELECT id FROM t3 WHERE id = 1);
149 SELECT xy FROM t1 where id = 1;
150 SELECT xy FROM t2 where id = 1;
151
152 INSERT INTO t4 SELECT * FROM t1;
153 INSERT INTO t5 SELECT * FROM t2;
154 UPDATE t1,t4 SET t1.xy = 3, t4.xy = 3 WHERE t1.id = 1 AND t4.id = 1;
155 SELECT xy FROM t1 where id = 1;
156 SELECT xy FROM t2 where id = 1;
157 SELECT xy FROM t4 where id = 1;
158 SELECT xy FROM t5 where id = 1;
159
160 UPDATE t1,t4 SET t1.xy = 4, t4.xy = 4 WHERE t1.id IN (SELECT id FROM t3 WHERE id = 1) AND t4.id IN (SELECT id FROM t3 WHERE id = 1);
161 SELECT xy FROM t1 where id = 1;
162 SELECT xy FROM t2 where id = 1;
163 SELECT xy FROM t4 where id = 1;
164 SELECT xy FROM t5 where id = 1;
165
166 INSERT INTO t1 VALUES (1,0) ON DUPLICATE KEY UPDATE xy = 5;
167 SELECT xy FROM t1 where id = 1;
168 SELECT xy FROM t2 where id = 1;
169
170 DROP TRIGGER t1_update;
171 DROP TRIGGER t4_update;
172
173 DELIMITER //;
174 CREATE TRIGGER t1_delete AFTER DELETE ON t1 FOR EACH ROW BEGIN REPLACE INTO t2 SELECT * FROM t1 WHERE t1.id > 4; END //
175 DELIMITER ;//
176
177 DELIMITER //;
178 CREATE TRIGGER t4_delete AFTER DELETE ON t4 FOR EACH ROW BEGIN REPLACE INTO t5 SELECT * FROM t4 WHERE t4.id > 4; END //
179 DELIMITER ;//
180
181 INSERT INTO t1 VALUES (5, 0),(6,0);
182 INSERT INTO t2 VALUES (5, 1),(6,1);
183 INSERT INTO t3 VALUES (5);
184 SELECT * FROM t1 order by id;
185 SELECT * FROM t2 order by id;
186 DELETE FROM t1 WHERE id IN (SELECT id FROM t3 WHERE id = 5);
187 SELECT * FROM t1 order by id;
188 SELECT * FROM t2 order by id;
189
190 INSERT INTO t1 VALUES (5,0);
191 UPDATE t2 SET xy = 1 WHERE id = 6;
192 TRUNCATE t4;
193 INSERT INTO t4 SELECT * FROM t1;
194 TRUNCATE t5;
195 INSERT INTO t5 SELECT * FROM t2;
196 SELECT * FROM t1 order by id;
197 SELECT * FROM t2 order by id;
198 SELECT * FROM t4 order by id;
199 SELECT * FROM t5 order by id;
200 DELETE FROM t1,t4 USING t1,t3,t4 WHERE t1.id IN (SELECT id FROM t3 WHERE id = 5) AND t4.id IN (SELECT id FROM t3 WHERE id = 5);
201 SELECT * FROM t1 order by id;
202 SELECT * FROM t2 order by id;
203 SELECT * FROM t4 order by id;
204 SELECT * FROM t5 order by id;
205
206 INSERT INTO t1 VALUES (5, 0);
207 REPLACE INTO t2 VALUES (6,1);
208 SELECT * FROM t1 order by id;
209 SELECT * FROM t2 order by id;
210 REPLACE INTO t1 VALUES (5, 1);
211 SELECT * FROM t1 order by id;
212 SELECT * FROM t2 order by id;
213
214 DROP TRIGGER t1_delete;
215 DROP TRIGGER t4_delete;
216 DROP TABLE t1, t2, t3, t4, t5;
217
218 # Test for bug#36658
219 # Verify that rename table
220 # doesn't remove triggers
221
222 connection server1;
223 create table t1(a int, b varchar(10), c date) engine=ndb;
224 delimiter //;
225 CREATE TRIGGER trg1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
226 SET new.c = date(now());
227 End //
228 delimiter ;//
229 select trigger_name,event_object_table from information_schema.triggers where
230 trigger_name='trg1';
231
232 connection server2;
233 delimiter //;
234 CREATE TRIGGER trg1 BEFORE UPDATE ON t1 FOR EACH ROW BEGIN
235 SET new.c = date(now());
236 End //
237 delimiter ;//
238 select trigger_name,event_object_table from information_schema.triggers where
239 trigger_name='trg1';
240
241 connection server1;
242 rename table t1 to t2;
243 select trigger_name,event_object_table from information_schema.triggers where
244 trigger_name='trg1';
245
246 connection server2;
247 select trigger_name,event_object_table from information_schema.triggers where
248 trigger_name='trg1';
249
250 connection server1;
251 drop table t2;
252
253 # bug#38034: last_insert_id() value not shown in table with trigger
254 #
255 # testing that last_insert_id is correct in ongoing transaction.
256
257 create table t1(c1 int(11) not null auto_increment primary key, c2 int(11) not null) engine=ndb;
258
259 delimiter |;
260
261 create trigger bi_t1 before insert on t1
262 FOR EACH ROW BEGIN
263 SET new.c2 = last_insert_id();
264 End
265 |
266
267 delimiter ;|
268
269
270 INSERT INTO t1 (c2) VALUES (17);
271 INSERT INTO t1 (c2) VALUES (17);
272 INSERT INTO t1 (c2) VALUES (17);
273 select c1 from t1 where c1=last_insert_id();
274 select * from t1 order by c1;
275 start transaction;
276 INSERT INTO t1 (c2) VALUES (17);
277 INSERT INTO t1 (c2) VALUES (17);
278 select c1 from t1 where c1=last_insert_id();
279 select * from t1 order by c1;
280 rollback;
281 select c1 from t1 where c1=last_insert_id();
282 select * from t1 order by c1;
283 start transaction;
284 INSERT INTO t1 (c2) VALUES (17);
285 INSERT INTO t1 (c2) VALUES (17);
286 select c1 from t1 where c1=last_insert_id();
287 select * from t1 order by c1;
288 commit;
289 select c1 from t1 where c1=last_insert_id();
290 select * from t1 order by c1;
291 drop trigger bi_t1;
292 drop table t1;