Debian Codegraph @

mysql-test/suite/rpl/r/rpl_row_triggers.result

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