Debian Codegraph @

mysql-test/suite/rpl/t/rpl_row_triggers.test

raw
1 -- source include/have_binlog_format_row.inc
2 -- source include/have_innodb.inc
3 -- source include/master-slave.inc
4
5 -- echo # Test of row replication with triggers on the slave side
6
7 connection master;
8 CREATE TABLE t1 (C1 CHAR(1) primary key, C2 CHAR(1)) engine=innodb;
9 SELECT * FROM t1;
10
11 sync_slave_with_master;
12
13 connection slave;
14 SET @old_slave_exec_mode= @@global.slave_exec_mode;
15 SET @old_slave_run_triggers_for_rbr= @@global.slave_run_triggers_for_rbr;
16 SET @@global.slave_exec_mode= IDEMPOTENT;
17 SET @@global.slave_run_triggers_for_rbr= YES;
18 SELECT * FROM t1;
19 create table t2 (id char(2) primary key, cnt int, o char(1), n char(1));
20 insert into t2 values
21 ('u0', 0, ' ', ' '),('u1', 0, ' ', ' '),
22 ('d0', 0, ' ', ' '),('d1', 0, ' ', ' '),
23 ('i0', 0, ' ', ' '),('i1', 0, ' ', ' ');
24 create trigger t1_cnt_b before update on t1 for each row
25 update t2 set cnt=cnt+1, o=old.C1, n=new.C1 where id = 'u0';
26 create trigger t1_cnt_db before delete on t1 for each row
27 update t2 set cnt=cnt+1, o=old.C1, n=' ' where id = 'd0';
28 create trigger t1_cnt_ib before insert on t1 for each row
29 update t2 set cnt=cnt+1, n=new.C1, o=' ' where id = 'i0';
30 create trigger t1_cnt_a after update on t1 for each row
31 update t2 set cnt=cnt+1, o=old.C1, n=new.C1 where id = 'u1';
32 create trigger t1_cnt_da after delete on t1 for each row
33 update t2 set cnt=cnt+1, o=old.C1, n=' ' where id = 'd1';
34 create trigger t1_cnt_ia after insert on t1 for each row
35 update t2 set cnt=cnt+1, n=new.C1, o=' ' where id = 'i1';
36 SELECT * FROM t2 order by id;
37
38 connection master;
39 --echo # INSERT triggers test
40 insert into t1 values ('a','b');
41
42 sync_slave_with_master;
43
44 connection slave;
45 SELECT * FROM t2 order by id;
46
47 connection master;
48
49 --echo # UPDATE triggers test
50 update t1 set C1= 'd';
51
52 sync_slave_with_master;
53
54 connection slave;
55 SELECT * FROM t2 order by id;
56
57 connection master;
58 --echo # DELETE triggers test
59 delete from t1 where C1='d';
60
61 sync_slave_with_master;
62
63 connection slave;
64 SELECT * FROM t2 order by id;
65
66 --echo # INSERT triggers causing DELETE + INSERT (on unique key conflict)
67 insert into t1 values ('0','1');
68
69 SELECT * FROM t2 order by id;
70
71 connection master;
72
73 insert into t1 values ('0','1');
74
75 sync_slave_with_master;
76
77 connection slave;
78 SELECT * FROM t2 order by id;
79
80
81 --echo # INSERT triggers which cause also DELETE test
82 --echo # (insert duplicate row in table referenced by foreign key)
83 insert into t1 values ('1','1');
84
85 connection master;
86
87 CREATE TABLE t3 (C1 CHAR(1) primary key, FOREIGN KEY (C1) REFERENCES t1(C1) ) engine=innodb;
88
89 insert into t1 values ('1','1');
90
91 sync_slave_with_master;
92
93 connection slave;
94 SELECT * FROM t2 order by id;
95
96 connection master;
97
98 drop table t3,t1;
99
100 sync_slave_with_master;
101
102 connection slave;
103 SET @@global.slave_exec_mode= @old_slave_exec_mode;
104 SET @@global.slave_run_triggers_for_rbr= @old_slave_run_triggers_for_rbr;
105 drop table t2;
106
107 --connection master
108
109 CREATE TABLE t1 (i INT) ENGINE=InnoDB;
110 CREATE TABLE t2 (i INT) ENGINE=InnoDB;
111
112 --sync_slave_with_master
113
114 SET @old_slave_run_triggers_for_rbr= @@global.slave_run_triggers_for_rbr;
115 SET GLOBAL slave_run_triggers_for_rbr=YES;
116
117 CREATE TRIGGER tr AFTER INSERT ON t1 FOR EACH ROW
118 INSERT INTO t2 VALUES (new.i);
119
120 --connection master
121
122 BEGIN;
123 INSERT INTO t1 VALUES (1);
124 INSERT INTO t1 VALUES (2);
125 COMMIT;
126
127 --sync_slave_with_master
128 select * from t2;
129 SET @@global.slave_run_triggers_for_rbr= @old_slave_run_triggers_for_rbr;
130
131 --connection master
132 drop tables t2,t1;
133
134 --sync_slave_with_master
135
136 -- echo # Triggers on slave do not work if master has some
137
138 connection master;
139 CREATE TABLE t1 (C1 CHAR(1) primary key, C2 CHAR(1)) engine=innodb;
140 SELECT * FROM t1;
141
142 create trigger t1_dummy before delete on t1 for each row
143 set @dummy= 1;
144
145 sync_slave_with_master;
146
147 connection slave;
148 SET @old_slave_exec_mode= @@global.slave_exec_mode;
149 SET @old_slave_run_triggers_for_rbr= @@global.slave_run_triggers_for_rbr;
150 SET @@global.slave_exec_mode= IDEMPOTENT;
151 SET @@global.slave_run_triggers_for_rbr= YES;
152 SELECT * FROM t1;
153 create table t2 (id char(2) primary key, cnt int, o char(1), n char(1));
154 insert into t2 values
155 ('u0', 0, ' ', ' '),('u1', 0, ' ', ' '),
156 ('d0', 0, ' ', ' '),('d1', 0, ' ', ' '),
157 ('i0', 0, ' ', ' '),('i1', 0, ' ', ' ');
158 create trigger t1_cnt_b before update on t1 for each row
159 update t2 set cnt=cnt+1, o=old.C1, n=new.C1 where id = 'u0';
160 create trigger t1_cnt_ib before insert on t1 for each row
161 update t2 set cnt=cnt+1, n=new.C1, o=' ' where id = 'i0';
162 create trigger t1_cnt_a after update on t1 for each row
163 update t2 set cnt=cnt+1, o=old.C1, n=new.C1 where id = 'u1';
164 create trigger t1_cnt_da after delete on t1 for each row
165 update t2 set cnt=cnt+1, o=old.C1, n=' ' where id = 'd1';
166 create trigger t1_cnt_ia after insert on t1 for each row
167 update t2 set cnt=cnt+1, n=new.C1, o=' ' where id = 'i1';
168 SELECT * FROM t2 order by id;
169
170 connection master;
171 --echo # INSERT triggers test
172 insert into t1 values ('a','b');
173
174 sync_slave_with_master;
175
176 connection slave;
177 SELECT * FROM t2 order by id;
178
179 connection master;
180
181 --echo # UPDATE triggers test
182 update t1 set C1= 'd';
183
184 sync_slave_with_master;
185
186 connection slave;
187 SELECT * FROM t2 order by id;
188
189 connection master;
190 --echo # DELETE triggers test
191 delete from t1 where C1='d';
192
193 sync_slave_with_master;
194
195 connection slave;
196 SELECT * FROM t2 order by id;
197
198 --echo # INSERT triggers which cause also UPDATE test (insert duplicate row)
199 insert into t1 values ('0','1');
200
201 SELECT * FROM t2 order by id;
202
203 connection master;
204
205 insert into t1 values ('0','1');
206
207 sync_slave_with_master;
208
209 connection slave;
210 SELECT * FROM t2 order by id;
211
212
213 --echo # INSERT triggers which cause also DELETE test
214 --echo # (insert duplicate row in table referenced by foreign key)
215 insert into t1 values ('1','1');
216
217 connection master;
218
219 CREATE TABLE t3 (C1 CHAR(1) primary key, FOREIGN KEY (C1) REFERENCES t1(C1) ) engine=innodb;
220
221 insert into t1 values ('1','1');
222
223 sync_slave_with_master;
224
225 connection slave;
226 SELECT * FROM t2 order by id;
227
228 connection master;
229
230 drop table t3,t1;
231
232 sync_slave_with_master;
233
234 connection slave;
235 SET @@global.slave_exec_mode= @old_slave_exec_mode;
236 SET @@global.slave_run_triggers_for_rbr= @old_slave_run_triggers_for_rbr;
237 drop table t2;
238
239 --echo #
240 --echo # MDEV-5513: Trigger is applied to the rows after first one
241 --echo #
242
243 --connection master
244
245 create table t1 (a int, b int);
246 create table tlog (a int);
247
248 set sql_log_bin=0;
249 create trigger tr1 after insert on t1 for each row insert into tlog values (1);
250 set sql_log_bin=1;
251
252 sync_slave_with_master;
253 --connection slave
254
255 set @slave_run_triggers_for_rbr.saved = @@slave_run_triggers_for_rbr;
256 set global slave_run_triggers_for_rbr=1;
257 create trigger tr2 before insert on t1 for each row set new.b = new.a;
258
259 --connection master
260
261 insert into t1 values (1,10),(2,20),(3,30);
262
263 --sync_slave_with_master
264
265 select * from t1;
266
267 # Cleanup
268
269 set global slave_run_triggers_for_rbr = @slave_run_triggers_for_rbr.saved;
270
271 --connection master
272
273 drop table t1, tlog;
274
275 sync_slave_with_master;
276
277
278 --echo #
279 --echo # MDEV-8411 Assertion `is_stat_field || !table || (!table->write_set ||
280 --echo # bitmap_is_set(table->write_set, field_index) ||
281 --echo # bitmap_is_set(table->vcol_set, field_index))'
282 --echo # failed in Field_timestamp::store_TIME_with_warning
283 --echo #
284 --echo #
285 --echo # Create table on master, replicate it on slave.
286 --echo #
287 --connection master
288 set @binlog_row_image.saved = @@binlog_row_image;
289 set binlog_row_image = MINIMAL;
290 create table t1 (pk int primary key, f int);
291
292 --sync_slave_with_master
293 --echo #
294 --echo # Create a trigger on the slave.
295 --echo #
296 create trigger tr before update on t1 for each row set new.f = 1000;
297 set @old_slave_run_triggers_for_rbr = @@global.slave_run_triggers_for_rbr;
298 set global slave_run_triggers_for_rbr = YES;
299
300 --connection master
301 --echo #
302 --echo # Update the table to have the trigger fire on the slave.,
303 --echo #
304 insert into t1 values (1,1),(2,2);
305 update t1 set pk=pk+10;
306 select * from t1;
307
308 --sync_slave_with_master
309 --echo #
310 --echo # Check to see if slave has the table updated.
311 --echo #
312 select * from t1;
313
314 --echo #
315 --echo # Cleanup
316 --echo #
317
318 set global slave_run_triggers_for_rbr = @old_slave_run_triggers_for_rbr;
319
320 --connection master
321 set binlog_row_image = @binlog_row_image.saved;
322 drop table t1;
323
324 --sync_slave_with_master
325
326 --echo #
327 --echo # enterprise 10.4 tests start
328 --echo #
329
330 --echo #
331 --echo # MENT-607 : Make slave_run_triggers_for_rbr enforce triggers to run
332 --echo # on slave, even when there are triggers on the master
333 --echo #
334
335 --echo # Triggers on slave WILL work (with ENFORCE) if master has some
336
337 connection master;
338 CREATE TABLE t1 (C1 CHAR(1) primary key, C2 CHAR(1)) engine=innodb;
339 SELECT * FROM t1;
340
341 create trigger t1_dummy before delete on t1 for each row
342 set @dummy= 1;
343
344 sync_slave_with_master;
345
346 connection slave;
347 SET @old_slave_exec_mode= @@global.slave_exec_mode;
348 SET @old_slave_run_triggers_for_rbr= @@global.slave_run_triggers_for_rbr;
349 SET @@global.slave_exec_mode= IDEMPOTENT;
350 SET @@global.slave_run_triggers_for_rbr= ENFORCE;
351 SELECT * FROM t1;
352 create table t2 (id char(2) primary key, cnt int, o char(1), n char(1));
353 insert into t2 values
354 ('u0', 0, ' ', ' '),('u1', 0, ' ', ' '),
355 ('d0', 0, ' ', ' '),('d1', 0, ' ', ' '),
356 ('i0', 0, ' ', ' '),('i1', 0, ' ', ' ');
357 create trigger t1_cnt_b before update on t1 for each row
358 update t2 set cnt=cnt+1, o=old.C1, n=new.C1 where id = 'u0';
359 create trigger t1_cnt_ib before insert on t1 for each row
360 update t2 set cnt=cnt+1, n=new.C1, o=' ' where id = 'i0';
361 create trigger t1_cnt_a after update on t1 for each row
362 update t2 set cnt=cnt+1, o=old.C1, n=new.C1 where id = 'u1';
363 create trigger t1_cnt_da after delete on t1 for each row
364 update t2 set cnt=cnt+1, o=old.C1, n=' ' where id = 'd1';
365 create trigger t1_cnt_ia after insert on t1 for each row
366 update t2 set cnt=cnt+1, n=new.C1, o=' ' where id = 'i1';
367 SELECT * FROM t2 order by id;
368
369 connection master;
370 --echo # INSERT triggers test
371 insert into t1 values ('a','b');
372
373 sync_slave_with_master;
374
375 connection slave;
376 SELECT * FROM t2 order by id;
377
378 connection master;
379
380 --echo # UPDATE triggers test
381 update t1 set C1= 'd';
382
383 sync_slave_with_master;
384
385 connection slave;
386 SELECT * FROM t2 order by id;
387
388 connection master;
389 --echo # DELETE triggers test
390 delete from t1 where C1='d';
391
392 sync_slave_with_master;
393
394 connection slave;
395 SELECT * FROM t2 order by id;
396
397 --echo # INSERT triggers which cause also UPDATE test (insert duplicate row)
398 insert into t1 values ('0','1');
399
400 SELECT * FROM t2 order by id;
401
402 connection master;
403
404 insert into t1 values ('0','1');
405
406 sync_slave_with_master;
407
408 connection slave;
409 SELECT * FROM t2 order by id;
410
411
412 --echo # INSERT triggers which cause also DELETE test
413 --echo # (insert duplicate row in table referenced by foreign key)
414 insert into t1 values ('1','1');
415
416 connection master;
417
418 CREATE TABLE t3 (C1 CHAR(1) primary key, FOREIGN KEY (C1) REFERENCES t1(C1) ) engine=innodb;
419
420 insert into t1 values ('1','1');
421
422 sync_slave_with_master;
423
424 connection slave;
425 SELECT * FROM t2 order by id;
426
427 connection master;
428
429 drop table t3,t1;
430
431 sync_slave_with_master;
432
433 connection slave;
434 SET @@global.slave_exec_mode= @old_slave_exec_mode;
435 SET @@global.slave_run_triggers_for_rbr= @old_slave_run_triggers_for_rbr;
436 drop table t2;
437
438 --echo #
439 --echo # enterprise 10.4 tests end
440 --echo #
441
442 --source include/rpl_end.inc