Debian Codegraph @

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

raw
1 include/master-slave.inc
2 [connection master]
3 # Test case1: INVOKES A TRIGGER with after insert action
4 connection master;
5 create table t1(a int, b int) engine=innodb;
6 create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
7 create trigger tr1 after insert on t1 for each row insert into t2(a) values(6);
8 create table t3(a int, b int) engine=innodb;
9 create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
10 create table t5(a int) engine=innodb;
11 create trigger tr2 after insert on t3 for each row begin
12 insert into t4(a) values(f1_insert_triggered());
13 insert into t4(a) values(f1_insert_triggered());
14 insert into t5(a) values(8);
15 end |
16 create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
17 CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
18 BEGIN
19 INSERT INTO t6(a) values(2),(3);
20 RETURN 1;
21 END//
22 begin;
23 insert into t1(a,b) values(1,1),(2,1);
24 insert into t3(a,b) values(1,1),(2,1);
25 update t1 set a = a + 5 where b = 1;
26 update t3 set a = a + 5 where b = 1;
27 delete from t1 where b = 1;
28 delete from t3 where b = 1;
29 connection master1;
30 insert into t2(a) values(3);
31 insert into t4(a) values(3);
32 connection master;
33 commit;
34 insert into t1(a,b) values(4,2);
35 insert into t3(a,b) values(4,2);
36 update t1 set a = a + 5 where b = 2;
37 update t3 set a = a + 5 where b = 2;
38 delete from t1 where b = 2;
39 delete from t3 where b = 2;
40 # To verify if insert/update in an autoinc column causes statement to be logged in row format
41 include/show_binlog_events.inc
42 Log_name Pos Event_type Server_id End_log_pos Info
43 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
44 master-bin.000001 # Intvar # # INSERT_ID=3
45 master-bin.000001 # Query # # use `test`; insert into t2(a) values(3)
46 master-bin.000001 # Xid # # COMMIT /* XID */
47 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
48 master-bin.000001 # Intvar # # INSERT_ID=5
49 master-bin.000001 # Query # # use `test`; insert into t4(a) values(3)
50 master-bin.000001 # Xid # # COMMIT /* XID */
51 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
52 master-bin.000001 # Annotate_rows # # insert into t1(a,b) values(1,1),(2,1)
53 master-bin.000001 # Table_map # # table_id: # (test.t1)
54 master-bin.000001 # Table_map # # table_id: # (test.t2)
55 master-bin.000001 # Write_rows_v1 # # table_id: #
56 master-bin.000001 # Write_rows_v1 # # table_id: #
57 master-bin.000001 # Write_rows_v1 # # table_id: #
58 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
59 master-bin.000001 # Annotate_rows # # insert into t3(a,b) values(1,1),(2,1)
60 master-bin.000001 # Table_map # # table_id: # (test.t3)
61 master-bin.000001 # Table_map # # table_id: # (test.t5)
62 master-bin.000001 # Table_map # # table_id: # (test.t4)
63 master-bin.000001 # Table_map # # table_id: # (test.t6)
64 master-bin.000001 # Write_rows_v1 # # table_id: #
65 master-bin.000001 # Write_rows_v1 # # table_id: #
66 master-bin.000001 # Write_rows_v1 # # table_id: #
67 master-bin.000001 # Write_rows_v1 # # table_id: #
68 master-bin.000001 # Write_rows_v1 # # table_id: #
69 master-bin.000001 # Write_rows_v1 # # table_id: #
70 master-bin.000001 # Write_rows_v1 # # table_id: #
71 master-bin.000001 # Write_rows_v1 # # table_id: #
72 master-bin.000001 # Write_rows_v1 # # table_id: #
73 master-bin.000001 # Write_rows_v1 # # table_id: #
74 master-bin.000001 # Write_rows_v1 # # table_id: #
75 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
76 master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 1
77 master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 1
78 master-bin.000001 # Query # # use `test`; delete from t1 where b = 1
79 master-bin.000001 # Query # # use `test`; delete from t3 where b = 1
80 master-bin.000001 # Xid # # COMMIT /* XID */
81 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
82 master-bin.000001 # Annotate_rows # # insert into t1(a,b) values(4,2)
83 master-bin.000001 # Table_map # # table_id: # (test.t1)
84 master-bin.000001 # Table_map # # table_id: # (test.t2)
85 master-bin.000001 # Write_rows_v1 # # table_id: #
86 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
87 master-bin.000001 # Xid # # COMMIT /* XID */
88 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
89 master-bin.000001 # Annotate_rows # # insert into t3(a,b) values(4,2)
90 master-bin.000001 # Table_map # # table_id: # (test.t3)
91 master-bin.000001 # Table_map # # table_id: # (test.t5)
92 master-bin.000001 # Table_map # # table_id: # (test.t4)
93 master-bin.000001 # Table_map # # table_id: # (test.t6)
94 master-bin.000001 # Write_rows_v1 # # table_id: #
95 master-bin.000001 # Write_rows_v1 # # table_id: #
96 master-bin.000001 # Write_rows_v1 # # table_id: #
97 master-bin.000001 # Write_rows_v1 # # table_id: #
98 master-bin.000001 # Write_rows_v1 # # table_id: #
99 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
100 master-bin.000001 # Xid # # COMMIT /* XID */
101 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
102 master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 2
103 master-bin.000001 # Xid # # COMMIT /* XID */
104 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
105 master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 2
106 master-bin.000001 # Xid # # COMMIT /* XID */
107 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
108 master-bin.000001 # Query # # use `test`; delete from t1 where b = 2
109 master-bin.000001 # Xid # # COMMIT /* XID */
110 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
111 master-bin.000001 # Query # # use `test`; delete from t3 where b = 2
112 master-bin.000001 # Xid # # COMMIT /* XID */
113 commit;
114 connection master;
115 connection slave;
116 #Test if the results are consistent on master and slave
117 #for 'INVOKES A TRIGGER with after insert action'
118 include/diff_tables.inc [master:t2, slave:t2]
119 include/diff_tables.inc [master:t4, slave:t4]
120 include/diff_tables.inc [master:t6, slave:t6]
121 connection master;
122 DROP TABLE t1;
123 DROP TABLE t2;
124 DROP TABLE t3;
125 DROP TABLE t4;
126 DROP TABLE t5;
127 DROP TABLE t6;
128 DROP FUNCTION f1_insert_triggered;
129 connection slave;
130 # Test case2: INVOKES A TRIGGER with before insert action
131 connection master;
132 create table t1(a int, b int) engine=innodb;
133 create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
134 create trigger tr1 before insert on t1 for each row insert into t2(a) values(6);
135 create table t3(a int, b int) engine=innodb;
136 create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
137 create table t5(a int) engine=innodb;
138 create trigger tr2 before insert on t3 for each row begin
139 insert into t4(a) values(f1_insert_triggered());
140 insert into t4(a) values(f1_insert_triggered());
141 insert into t5(a) values(8);
142 end |
143 create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
144 CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
145 BEGIN
146 INSERT INTO t6(a) values(2),(3);
147 RETURN 1;
148 END//
149 begin;
150 insert into t1(a,b) values(1,1),(2,1);
151 insert into t3(a,b) values(1,1),(2,1);
152 update t1 set a = a + 5 where b = 1;
153 update t3 set a = a + 5 where b = 1;
154 delete from t1 where b = 1;
155 delete from t3 where b = 1;
156 connection master1;
157 insert into t2(a) values(3);
158 insert into t4(a) values(3);
159 connection master;
160 commit;
161 insert into t1(a,b) values(4,2);
162 insert into t3(a,b) values(4,2);
163 update t1 set a = a + 5 where b = 2;
164 update t3 set a = a + 5 where b = 2;
165 delete from t1 where b = 2;
166 delete from t3 where b = 2;
167 # To verify if insert/update in an autoinc column causes statement to be logged in row format
168 include/show_binlog_events.inc
169 Log_name Pos Event_type Server_id End_log_pos Info
170 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
171 master-bin.000001 # Intvar # # INSERT_ID=3
172 master-bin.000001 # Query # # use `test`; insert into t2(a) values(3)
173 master-bin.000001 # Xid # # COMMIT /* XID */
174 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
175 master-bin.000001 # Intvar # # INSERT_ID=5
176 master-bin.000001 # Query # # use `test`; insert into t4(a) values(3)
177 master-bin.000001 # Xid # # COMMIT /* XID */
178 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
179 master-bin.000001 # Annotate_rows # # insert into t2(a) values(6)
180 master-bin.000001 # Table_map # # table_id: # (test.t1)
181 master-bin.000001 # Table_map # # table_id: # (test.t2)
182 master-bin.000001 # Write_rows_v1 # # table_id: #
183 master-bin.000001 # Write_rows_v1 # # table_id: #
184 master-bin.000001 # Write_rows_v1 # # table_id: #
185 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
186 master-bin.000001 # Annotate_rows # # INSERT INTO t6(a) values(2),(3)
187 master-bin.000001 # Table_map # # table_id: # (test.t3)
188 master-bin.000001 # Table_map # # table_id: # (test.t5)
189 master-bin.000001 # Table_map # # table_id: # (test.t4)
190 master-bin.000001 # Table_map # # table_id: # (test.t6)
191 master-bin.000001 # Write_rows_v1 # # table_id: #
192 master-bin.000001 # Write_rows_v1 # # table_id: #
193 master-bin.000001 # Write_rows_v1 # # table_id: #
194 master-bin.000001 # Write_rows_v1 # # table_id: #
195 master-bin.000001 # Write_rows_v1 # # table_id: #
196 master-bin.000001 # Write_rows_v1 # # table_id: #
197 master-bin.000001 # Write_rows_v1 # # table_id: #
198 master-bin.000001 # Write_rows_v1 # # table_id: #
199 master-bin.000001 # Write_rows_v1 # # table_id: #
200 master-bin.000001 # Write_rows_v1 # # table_id: #
201 master-bin.000001 # Write_rows_v1 # # table_id: #
202 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
203 master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 1
204 master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 1
205 master-bin.000001 # Query # # use `test`; delete from t1 where b = 1
206 master-bin.000001 # Query # # use `test`; delete from t3 where b = 1
207 master-bin.000001 # Xid # # COMMIT /* XID */
208 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
209 master-bin.000001 # Annotate_rows # # insert into t2(a) values(6)
210 master-bin.000001 # Table_map # # table_id: # (test.t1)
211 master-bin.000001 # Table_map # # table_id: # (test.t2)
212 master-bin.000001 # Write_rows_v1 # # table_id: #
213 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
214 master-bin.000001 # Xid # # COMMIT /* XID */
215 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
216 master-bin.000001 # Annotate_rows # # INSERT INTO t6(a) values(2),(3)
217 master-bin.000001 # Table_map # # table_id: # (test.t3)
218 master-bin.000001 # Table_map # # table_id: # (test.t5)
219 master-bin.000001 # Table_map # # table_id: # (test.t4)
220 master-bin.000001 # Table_map # # table_id: # (test.t6)
221 master-bin.000001 # Write_rows_v1 # # table_id: #
222 master-bin.000001 # Write_rows_v1 # # table_id: #
223 master-bin.000001 # Write_rows_v1 # # table_id: #
224 master-bin.000001 # Write_rows_v1 # # table_id: #
225 master-bin.000001 # Write_rows_v1 # # table_id: #
226 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
227 master-bin.000001 # Xid # # COMMIT /* XID */
228 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
229 master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 2
230 master-bin.000001 # Xid # # COMMIT /* XID */
231 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
232 master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 2
233 master-bin.000001 # Xid # # COMMIT /* XID */
234 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
235 master-bin.000001 # Query # # use `test`; delete from t1 where b = 2
236 master-bin.000001 # Xid # # COMMIT /* XID */
237 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
238 master-bin.000001 # Query # # use `test`; delete from t3 where b = 2
239 master-bin.000001 # Xid # # COMMIT /* XID */
240 commit;
241 connection master;
242 connection slave;
243 #Test if the results are consistent on master and slave
244 #for 'INVOKES A TRIGGER with before insert action'
245 include/diff_tables.inc [master:t2, slave:t2]
246 include/diff_tables.inc [master:t4, slave:t4]
247 include/diff_tables.inc [master:t6, slave:t6]
248 connection master;
249 DROP TABLE t1;
250 DROP TABLE t2;
251 DROP TABLE t3;
252 DROP TABLE t4;
253 DROP TABLE t5;
254 DROP TABLE t6;
255 DROP FUNCTION f1_insert_triggered;
256 connection slave;
257 # Test case3: INVOKES A TRIGGER with after update action
258 connection master;
259 create table t1(a int, b int) engine=innodb;
260 create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
261 create trigger tr1 after update on t1 for each row insert into t2(a) values(6);
262 create table t3(a int, b int) engine=innodb;
263 create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
264 create table t5(a int) engine=innodb;
265 create trigger tr2 after update on t3 for each row begin
266 insert into t4(a) values(f1_insert_triggered());
267 insert into t4(a) values(f1_insert_triggered());
268 insert into t5(a) values(8);
269 end |
270 create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
271 CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
272 BEGIN
273 INSERT INTO t6(a) values(2),(3);
274 RETURN 1;
275 END//
276 begin;
277 insert into t1(a,b) values(1,1),(2,1);
278 insert into t3(a,b) values(1,1),(2,1);
279 update t1 set a = a + 5 where b = 1;
280 update t3 set a = a + 5 where b = 1;
281 delete from t1 where b = 1;
282 delete from t3 where b = 1;
283 connection master1;
284 insert into t2(a) values(3);
285 insert into t4(a) values(3);
286 connection master;
287 commit;
288 insert into t1(a,b) values(4,2);
289 insert into t3(a,b) values(4,2);
290 update t1 set a = a + 5 where b = 2;
291 update t3 set a = a + 5 where b = 2;
292 delete from t1 where b = 2;
293 delete from t3 where b = 2;
294 # To verify if insert/update in an autoinc column causes statement to be logged in row format
295 include/show_binlog_events.inc
296 Log_name Pos Event_type Server_id End_log_pos Info
297 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
298 master-bin.000001 # Intvar # # INSERT_ID=3
299 master-bin.000001 # Query # # use `test`; insert into t2(a) values(3)
300 master-bin.000001 # Xid # # COMMIT /* XID */
301 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
302 master-bin.000001 # Intvar # # INSERT_ID=5
303 master-bin.000001 # Query # # use `test`; insert into t4(a) values(3)
304 master-bin.000001 # Xid # # COMMIT /* XID */
305 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
306 master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,1)
307 master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(1,1),(2,1)
308 master-bin.000001 # Annotate_rows # # update t1 set a = a + 5 where b = 1
309 master-bin.000001 # Table_map # # table_id: # (test.t1)
310 master-bin.000001 # Table_map # # table_id: # (test.t2)
311 master-bin.000001 # Update_rows_v1 # # table_id: #
312 master-bin.000001 # Write_rows_v1 # # table_id: #
313 master-bin.000001 # Update_rows_v1 # # table_id: #
314 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
315 master-bin.000001 # Annotate_rows # # update t3 set a = a + 5 where b = 1
316 master-bin.000001 # Table_map # # table_id: # (test.t3)
317 master-bin.000001 # Table_map # # table_id: # (test.t5)
318 master-bin.000001 # Table_map # # table_id: # (test.t4)
319 master-bin.000001 # Table_map # # table_id: # (test.t6)
320 master-bin.000001 # Update_rows_v1 # # table_id: #
321 master-bin.000001 # Write_rows_v1 # # table_id: #
322 master-bin.000001 # Write_rows_v1 # # table_id: #
323 master-bin.000001 # Write_rows_v1 # # table_id: #
324 master-bin.000001 # Write_rows_v1 # # table_id: #
325 master-bin.000001 # Write_rows_v1 # # table_id: #
326 master-bin.000001 # Update_rows_v1 # # table_id: #
327 master-bin.000001 # Write_rows_v1 # # table_id: #
328 master-bin.000001 # Write_rows_v1 # # table_id: #
329 master-bin.000001 # Write_rows_v1 # # table_id: #
330 master-bin.000001 # Write_rows_v1 # # table_id: #
331 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
332 master-bin.000001 # Query # # use `test`; delete from t1 where b = 1
333 master-bin.000001 # Query # # use `test`; delete from t3 where b = 1
334 master-bin.000001 # Xid # # COMMIT /* XID */
335 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
336 master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(4,2)
337 master-bin.000001 # Xid # # COMMIT /* XID */
338 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
339 master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(4,2)
340 master-bin.000001 # Xid # # COMMIT /* XID */
341 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
342 master-bin.000001 # Annotate_rows # # update t1 set a = a + 5 where b = 2
343 master-bin.000001 # Table_map # # table_id: # (test.t1)
344 master-bin.000001 # Table_map # # table_id: # (test.t2)
345 master-bin.000001 # Update_rows_v1 # # table_id: #
346 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
347 master-bin.000001 # Xid # # COMMIT /* XID */
348 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
349 master-bin.000001 # Annotate_rows # # update t3 set a = a + 5 where b = 2
350 master-bin.000001 # Table_map # # table_id: # (test.t3)
351 master-bin.000001 # Table_map # # table_id: # (test.t5)
352 master-bin.000001 # Table_map # # table_id: # (test.t4)
353 master-bin.000001 # Table_map # # table_id: # (test.t6)
354 master-bin.000001 # Update_rows_v1 # # table_id: #
355 master-bin.000001 # Write_rows_v1 # # table_id: #
356 master-bin.000001 # Write_rows_v1 # # table_id: #
357 master-bin.000001 # Write_rows_v1 # # table_id: #
358 master-bin.000001 # Write_rows_v1 # # table_id: #
359 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
360 master-bin.000001 # Xid # # COMMIT /* XID */
361 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
362 master-bin.000001 # Query # # use `test`; delete from t1 where b = 2
363 master-bin.000001 # Xid # # COMMIT /* XID */
364 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
365 master-bin.000001 # Query # # use `test`; delete from t3 where b = 2
366 master-bin.000001 # Xid # # COMMIT /* XID */
367 commit;
368 connection master;
369 connection slave;
370 #Test if the results are consistent on master and slave
371 #for 'INVOKES A TRIGGER with after update action'
372 include/diff_tables.inc [master:t2, slave:t2]
373 include/diff_tables.inc [master:t4, slave:t4]
374 include/diff_tables.inc [master:t6, slave:t6]
375 connection master;
376 DROP TABLE t1;
377 DROP TABLE t2;
378 DROP TABLE t3;
379 DROP TABLE t4;
380 DROP TABLE t5;
381 DROP TABLE t6;
382 DROP FUNCTION f1_insert_triggered;
383 connection slave;
384 # Test case4: INVOKES A TRIGGER with before update action
385 connection master;
386 create table t1(a int, b int) engine=innodb;
387 create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
388 create trigger tr1 before update on t1 for each row insert into t2(a) values(6);
389 create table t3(a int, b int) engine=innodb;
390 create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
391 create table t5(a int) engine=innodb;
392 create trigger tr2 before update on t3 for each row begin
393 insert into t4(a) values(f1_insert_triggered());
394 insert into t4(a) values(f1_insert_triggered());
395 insert into t5(a) values(8);
396 end |
397 create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
398 CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
399 BEGIN
400 INSERT INTO t6(a) values(2),(3);
401 RETURN 1;
402 END//
403 begin;
404 insert into t1(a,b) values(1,1),(2,1);
405 insert into t3(a,b) values(1,1),(2,1);
406 update t1 set a = a + 5 where b = 1;
407 update t3 set a = a + 5 where b = 1;
408 delete from t1 where b = 1;
409 delete from t3 where b = 1;
410 connection master1;
411 insert into t2(a) values(3);
412 insert into t4(a) values(3);
413 connection master;
414 commit;
415 insert into t1(a,b) values(4,2);
416 insert into t3(a,b) values(4,2);
417 update t1 set a = a + 5 where b = 2;
418 update t3 set a = a + 5 where b = 2;
419 delete from t1 where b = 2;
420 delete from t3 where b = 2;
421 # To verify if insert/update in an autoinc column causes statement to be logged in row format
422 include/show_binlog_events.inc
423 Log_name Pos Event_type Server_id End_log_pos Info
424 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
425 master-bin.000001 # Intvar # # INSERT_ID=3
426 master-bin.000001 # Query # # use `test`; insert into t2(a) values(3)
427 master-bin.000001 # Xid # # COMMIT /* XID */
428 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
429 master-bin.000001 # Intvar # # INSERT_ID=5
430 master-bin.000001 # Query # # use `test`; insert into t4(a) values(3)
431 master-bin.000001 # Xid # # COMMIT /* XID */
432 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
433 master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,1)
434 master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(1,1),(2,1)
435 master-bin.000001 # Annotate_rows # # insert into t2(a) values(6)
436 master-bin.000001 # Table_map # # table_id: # (test.t1)
437 master-bin.000001 # Table_map # # table_id: # (test.t2)
438 master-bin.000001 # Write_rows_v1 # # table_id: #
439 master-bin.000001 # Update_rows_v1 # # table_id: #
440 master-bin.000001 # Write_rows_v1 # # table_id: #
441 master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F
442 master-bin.000001 # Annotate_rows # # INSERT INTO t6(a) values(2),(3)
443 master-bin.000001 # Table_map # # table_id: # (test.t3)
444 master-bin.000001 # Table_map # # table_id: # (test.t5)
445 master-bin.000001 # Table_map # # table_id: # (test.t4)
446 master-bin.000001 # Table_map # # table_id: # (test.t6)
447 master-bin.000001 # Write_rows_v1 # # table_id: #
448 master-bin.000001 # Write_rows_v1 # # table_id: #
449 master-bin.000001 # Write_rows_v1 # # table_id: #
450 master-bin.000001 # Write_rows_v1 # # table_id: #
451 master-bin.000001 # Write_rows_v1 # # table_id: #
452 master-bin.000001 # Update_rows_v1 # # table_id: #
453 master-bin.000001 # Write_rows_v1 # # table_id: #
454 master-bin.000001 # Write_rows_v1 # # table_id: #
455 master-bin.000001 # Write_rows_v1 # # table_id: #
456 master-bin.000001 # Write_rows_v1 # # table_id: #
457 master-bin.000001 # Write_rows_v1 # # table_id: #
458 master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F
459 master-bin.000001 # Query # # use `test`; delete from t1 where b = 1
460 master-bin.000001 # Query # # use `test`; delete from t3 where b = 1
461 master-bin.000001 # Xid # # COMMIT /* XID */
462 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
463 master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(4,2)
464 master-bin.000001 # Xid # # COMMIT /* XID */
465 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
466 master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(4,2)
467 master-bin.000001 # Xid # # COMMIT /* XID */
468 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
469 master-bin.000001 # Annotate_rows # # insert into t2(a) values(6)
470 master-bin.000001 # Table_map # # table_id: # (test.t1)
471 master-bin.000001 # Table_map # # table_id: # (test.t2)
472 master-bin.000001 # Write_rows_v1 # # table_id: #
473 master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F
474 master-bin.000001 # Xid # # COMMIT /* XID */
475 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
476 master-bin.000001 # Annotate_rows # # INSERT INTO t6(a) values(2),(3)
477 master-bin.000001 # Table_map # # table_id: # (test.t3)
478 master-bin.000001 # Table_map # # table_id: # (test.t5)
479 master-bin.000001 # Table_map # # table_id: # (test.t4)
480 master-bin.000001 # Table_map # # table_id: # (test.t6)
481 master-bin.000001 # Write_rows_v1 # # table_id: #
482 master-bin.000001 # Write_rows_v1 # # table_id: #
483 master-bin.000001 # Write_rows_v1 # # table_id: #
484 master-bin.000001 # Write_rows_v1 # # table_id: #
485 master-bin.000001 # Write_rows_v1 # # table_id: #
486 master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F
487 master-bin.000001 # Xid # # COMMIT /* XID */
488 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
489 master-bin.000001 # Query # # use `test`; delete from t1 where b = 2
490 master-bin.000001 # Xid # # COMMIT /* XID */
491 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
492 master-bin.000001 # Query # # use `test`; delete from t3 where b = 2
493 master-bin.000001 # Xid # # COMMIT /* XID */
494 commit;
495 connection master;
496 connection slave;
497 #Test if the results are consistent on master and slave
498 #for 'INVOKES A TRIGGER with before update action'
499 include/diff_tables.inc [master:t2, slave:t2]
500 include/diff_tables.inc [master:t4, slave:t4]
501 include/diff_tables.inc [master:t6, slave:t6]
502 connection master;
503 DROP TABLE t1;
504 DROP TABLE t2;
505 DROP TABLE t3;
506 DROP TABLE t4;
507 DROP TABLE t5;
508 DROP TABLE t6;
509 DROP FUNCTION f1_insert_triggered;
510 connection slave;
511 # Test case5: INVOKES A TRIGGER with after delete action
512 connection master;
513 create table t1(a int, b int) engine=innodb;
514 create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
515 create trigger tr1 after delete on t1 for each row insert into t2(a) values(6);
516 create table t3(a int, b int) engine=innodb;
517 create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
518 create table t5(a int) engine=innodb;
519 create trigger tr2 after delete on t3 for each row begin
520 insert into t4(a) values(f1_insert_triggered());
521 insert into t4(a) values(f1_insert_triggered());
522 insert into t5(a) values(8);
523 end |
524 create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
525 CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
526 BEGIN
527 INSERT INTO t6(a) values(2),(3);
528 RETURN 1;
529 END//
530 begin;
531 insert into t1(a,b) values(1,1),(2,1);
532 insert into t3(a,b) values(1,1),(2,1);
533 update t1 set a = a + 5 where b = 1;
534 update t3 set a = a + 5 where b = 1;
535 delete from t1 where b = 1;
536 delete from t3 where b = 1;
537 connection master1;
538 insert into t2(a) values(3);
539 insert into t4(a) values(3);
540 connection master;
541 commit;
542 insert into t1(a,b) values(4,2);
543 insert into t3(a,b) values(4,2);
544 update t1 set a = a + 5 where b = 2;
545 update t3 set a = a + 5 where b = 2;
546 delete from t1 where b = 2;
547 delete from t3 where b = 2;
548 # To verify if insert/update in an autoinc column causes statement to be logged in row format
549 include/show_binlog_events.inc
550 Log_name Pos Event_type Server_id End_log_pos Info
551 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
552 master-bin.000001 # Intvar # # INSERT_ID=3
553 master-bin.000001 # Query # # use `test`; insert into t2(a) values(3)
554 master-bin.000001 # Xid # # COMMIT /* XID */
555 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
556 master-bin.000001 # Intvar # # INSERT_ID=5
557 master-bin.000001 # Query # # use `test`; insert into t4(a) values(3)
558 master-bin.000001 # Xid # # COMMIT /* XID */
559 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
560 master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,1)
561 master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(1,1),(2,1)
562 master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 1
563 master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 1
564 master-bin.000001 # Annotate_rows # # delete from t1 where b = 1
565 master-bin.000001 # Table_map # # table_id: # (test.t1)
566 master-bin.000001 # Table_map # # table_id: # (test.t2)
567 master-bin.000001 # Delete_rows_v1 # # table_id: #
568 master-bin.000001 # Write_rows_v1 # # table_id: #
569 master-bin.000001 # Delete_rows_v1 # # table_id: #
570 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
571 master-bin.000001 # Annotate_rows # # delete from t3 where b = 1
572 master-bin.000001 # Table_map # # table_id: # (test.t3)
573 master-bin.000001 # Table_map # # table_id: # (test.t5)
574 master-bin.000001 # Table_map # # table_id: # (test.t4)
575 master-bin.000001 # Table_map # # table_id: # (test.t6)
576 master-bin.000001 # Delete_rows_v1 # # table_id: #
577 master-bin.000001 # Write_rows_v1 # # table_id: #
578 master-bin.000001 # Write_rows_v1 # # table_id: #
579 master-bin.000001 # Write_rows_v1 # # table_id: #
580 master-bin.000001 # Write_rows_v1 # # table_id: #
581 master-bin.000001 # Write_rows_v1 # # table_id: #
582 master-bin.000001 # Delete_rows_v1 # # table_id: #
583 master-bin.000001 # Write_rows_v1 # # table_id: #
584 master-bin.000001 # Write_rows_v1 # # table_id: #
585 master-bin.000001 # Write_rows_v1 # # table_id: #
586 master-bin.000001 # Write_rows_v1 # # table_id: #
587 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
588 master-bin.000001 # Xid # # COMMIT /* XID */
589 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
590 master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(4,2)
591 master-bin.000001 # Xid # # COMMIT /* XID */
592 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
593 master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(4,2)
594 master-bin.000001 # Xid # # COMMIT /* XID */
595 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
596 master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 2
597 master-bin.000001 # Xid # # COMMIT /* XID */
598 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
599 master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 2
600 master-bin.000001 # Xid # # COMMIT /* XID */
601 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
602 master-bin.000001 # Annotate_rows # # delete from t1 where b = 2
603 master-bin.000001 # Table_map # # table_id: # (test.t1)
604 master-bin.000001 # Table_map # # table_id: # (test.t2)
605 master-bin.000001 # Delete_rows_v1 # # table_id: #
606 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
607 master-bin.000001 # Xid # # COMMIT /* XID */
608 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
609 master-bin.000001 # Annotate_rows # # delete from t3 where b = 2
610 master-bin.000001 # Table_map # # table_id: # (test.t3)
611 master-bin.000001 # Table_map # # table_id: # (test.t5)
612 master-bin.000001 # Table_map # # table_id: # (test.t4)
613 master-bin.000001 # Table_map # # table_id: # (test.t6)
614 master-bin.000001 # Delete_rows_v1 # # table_id: #
615 master-bin.000001 # Write_rows_v1 # # table_id: #
616 master-bin.000001 # Write_rows_v1 # # table_id: #
617 master-bin.000001 # Write_rows_v1 # # table_id: #
618 master-bin.000001 # Write_rows_v1 # # table_id: #
619 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
620 master-bin.000001 # Xid # # COMMIT /* XID */
621 commit;
622 connection master;
623 connection slave;
624 #Test if the results are consistent on master and slave
625 #for 'INVOKES A TRIGGER with after delete action'
626 include/diff_tables.inc [master:t2, slave:t2]
627 include/diff_tables.inc [master:t4, slave:t4]
628 include/diff_tables.inc [master:t6, slave:t6]
629 connection master;
630 DROP TABLE t1;
631 DROP TABLE t2;
632 DROP TABLE t3;
633 DROP TABLE t4;
634 DROP TABLE t5;
635 DROP TABLE t6;
636 DROP FUNCTION f1_insert_triggered;
637 connection slave;
638 # Test case6: INVOKES A TRIGGER with before delete action
639 connection master;
640 create table t1(a int, b int) engine=innodb;
641 create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
642 create trigger tr1 before delete on t1 for each row insert into t2(a) values(6);
643 create table t3(a int, b int) engine=innodb;
644 create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
645 create table t5(a int) engine=innodb;
646 create trigger tr2 before delete on t3 for each row begin
647 insert into t4(a) values(f1_insert_triggered());
648 insert into t4(a) values(f1_insert_triggered());
649 insert into t5(a) values(8);
650 end |
651 create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
652 CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
653 BEGIN
654 INSERT INTO t6(a) values(2),(3);
655 RETURN 1;
656 END//
657 begin;
658 insert into t1(a,b) values(1,1),(2,1);
659 insert into t3(a,b) values(1,1),(2,1);
660 update t1 set a = a + 5 where b = 1;
661 update t3 set a = a + 5 where b = 1;
662 delete from t1 where b = 1;
663 delete from t3 where b = 1;
664 connection master1;
665 insert into t2(a) values(3);
666 insert into t4(a) values(3);
667 connection master;
668 commit;
669 insert into t1(a,b) values(4,2);
670 insert into t3(a,b) values(4,2);
671 update t1 set a = a + 5 where b = 2;
672 update t3 set a = a + 5 where b = 2;
673 delete from t1 where b = 2;
674 delete from t3 where b = 2;
675 # To verify if insert/update in an autoinc column causes statement to be logged in row format
676 include/show_binlog_events.inc
677 Log_name Pos Event_type Server_id End_log_pos Info
678 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
679 master-bin.000001 # Intvar # # INSERT_ID=3
680 master-bin.000001 # Query # # use `test`; insert into t2(a) values(3)
681 master-bin.000001 # Xid # # COMMIT /* XID */
682 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
683 master-bin.000001 # Intvar # # INSERT_ID=5
684 master-bin.000001 # Query # # use `test`; insert into t4(a) values(3)
685 master-bin.000001 # Xid # # COMMIT /* XID */
686 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
687 master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,1)
688 master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(1,1),(2,1)
689 master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 1
690 master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 1
691 master-bin.000001 # Annotate_rows # # insert into t2(a) values(6)
692 master-bin.000001 # Table_map # # table_id: # (test.t1)
693 master-bin.000001 # Table_map # # table_id: # (test.t2)
694 master-bin.000001 # Write_rows_v1 # # table_id: #
695 master-bin.000001 # Delete_rows_v1 # # table_id: #
696 master-bin.000001 # Write_rows_v1 # # table_id: #
697 master-bin.000001 # Delete_rows_v1 # # table_id: # flags: STMT_END_F
698 master-bin.000001 # Annotate_rows # # INSERT INTO t6(a) values(2),(3)
699 master-bin.000001 # Table_map # # table_id: # (test.t3)
700 master-bin.000001 # Table_map # # table_id: # (test.t5)
701 master-bin.000001 # Table_map # # table_id: # (test.t4)
702 master-bin.000001 # Table_map # # table_id: # (test.t6)
703 master-bin.000001 # Write_rows_v1 # # table_id: #
704 master-bin.000001 # Write_rows_v1 # # table_id: #
705 master-bin.000001 # Write_rows_v1 # # table_id: #
706 master-bin.000001 # Write_rows_v1 # # table_id: #
707 master-bin.000001 # Write_rows_v1 # # table_id: #
708 master-bin.000001 # Delete_rows_v1 # # table_id: #
709 master-bin.000001 # Write_rows_v1 # # table_id: #
710 master-bin.000001 # Write_rows_v1 # # table_id: #
711 master-bin.000001 # Write_rows_v1 # # table_id: #
712 master-bin.000001 # Write_rows_v1 # # table_id: #
713 master-bin.000001 # Write_rows_v1 # # table_id: #
714 master-bin.000001 # Delete_rows_v1 # # table_id: # flags: STMT_END_F
715 master-bin.000001 # Xid # # COMMIT /* XID */
716 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
717 master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(4,2)
718 master-bin.000001 # Xid # # COMMIT /* XID */
719 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
720 master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(4,2)
721 master-bin.000001 # Xid # # COMMIT /* XID */
722 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
723 master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 2
724 master-bin.000001 # Xid # # COMMIT /* XID */
725 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
726 master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 2
727 master-bin.000001 # Xid # # COMMIT /* XID */
728 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
729 master-bin.000001 # Annotate_rows # # insert into t2(a) values(6)
730 master-bin.000001 # Table_map # # table_id: # (test.t1)
731 master-bin.000001 # Table_map # # table_id: # (test.t2)
732 master-bin.000001 # Write_rows_v1 # # table_id: #
733 master-bin.000001 # Delete_rows_v1 # # table_id: # flags: STMT_END_F
734 master-bin.000001 # Xid # # COMMIT /* XID */
735 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
736 master-bin.000001 # Annotate_rows # # INSERT INTO t6(a) values(2),(3)
737 master-bin.000001 # Table_map # # table_id: # (test.t3)
738 master-bin.000001 # Table_map # # table_id: # (test.t5)
739 master-bin.000001 # Table_map # # table_id: # (test.t4)
740 master-bin.000001 # Table_map # # table_id: # (test.t6)
741 master-bin.000001 # Write_rows_v1 # # table_id: #
742 master-bin.000001 # Write_rows_v1 # # table_id: #
743 master-bin.000001 # Write_rows_v1 # # table_id: #
744 master-bin.000001 # Write_rows_v1 # # table_id: #
745 master-bin.000001 # Write_rows_v1 # # table_id: #
746 master-bin.000001 # Delete_rows_v1 # # table_id: # flags: STMT_END_F
747 master-bin.000001 # Xid # # COMMIT /* XID */
748 commit;
749 connection master;
750 connection slave;
751 #Test if the results are consistent on master and slave
752 #for 'INVOKES A TRIGGER with before delete action'
753 include/diff_tables.inc [master:t2, slave:t2]
754 include/diff_tables.inc [master:t4, slave:t4]
755 include/diff_tables.inc [master:t6, slave:t6]
756 connection master;
757 DROP TABLE t1;
758 DROP TABLE t2;
759 DROP TABLE t3;
760 DROP TABLE t4;
761 DROP TABLE t5;
762 DROP TABLE t6;
763 DROP FUNCTION f1_insert_triggered;
764 connection slave;
765 # Test case7: CALLS A FUNCTION which INVOKES A TRIGGER with after insert action
766 connection master;
767 create table t1(a int) engine=innodb;
768 create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
769 create table t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
770 CREATE FUNCTION f1_two_inserts_trigger() RETURNS INTEGER
771 BEGIN
772 INSERT INTO t2(a) values(2),(3);
773 INSERT INTO t2(a) values(2),(3);
774 RETURN 1;
775 END |
776 create trigger tr11 after insert on t2 for each row begin
777 insert into t3(a) values(new.a);
778 insert into t3(a) values(new.a);
779 end |
780 begin;
781 insert into t1(a) values(f1_two_inserts_trigger());
782 connection master1;
783 insert into t2(a) values(4),(5);
784 connection master;
785 commit;
786 insert into t1(a) values(f1_two_inserts_trigger());
787 # To verify if insert/update in an autoinc column causes statement to be logged in row format
788 include/show_binlog_events.inc
789 Log_name Pos Event_type Server_id End_log_pos Info
790 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
791 master-bin.000001 # Annotate_rows # # insert into t2(a) values(4),(5)
792 master-bin.000001 # Table_map # # table_id: # (test.t2)
793 master-bin.000001 # Table_map # # table_id: # (test.t3)
794 master-bin.000001 # Write_rows_v1 # # table_id: #
795 master-bin.000001 # Write_rows_v1 # # table_id: #
796 master-bin.000001 # Write_rows_v1 # # table_id: #
797 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
798 master-bin.000001 # Xid # # COMMIT /* XID */
799 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
800 master-bin.000001 # Annotate_rows # # INSERT INTO t2(a) values(2),(3)
801 master-bin.000001 # Table_map # # table_id: # (test.t1)
802 master-bin.000001 # Table_map # # table_id: # (test.t2)
803 master-bin.000001 # Table_map # # table_id: # (test.t3)
804 master-bin.000001 # Write_rows_v1 # # table_id: #
805 master-bin.000001 # Write_rows_v1 # # table_id: #
806 master-bin.000001 # Write_rows_v1 # # table_id: #
807 master-bin.000001 # Write_rows_v1 # # table_id: #
808 master-bin.000001 # Write_rows_v1 # # table_id: #
809 master-bin.000001 # Write_rows_v1 # # table_id: #
810 master-bin.000001 # Write_rows_v1 # # table_id: #
811 master-bin.000001 # Write_rows_v1 # # table_id: #
812 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
813 master-bin.000001 # Xid # # COMMIT /* XID */
814 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
815 master-bin.000001 # Annotate_rows # # INSERT INTO t2(a) values(2),(3)
816 master-bin.000001 # Table_map # # table_id: # (test.t1)
817 master-bin.000001 # Table_map # # table_id: # (test.t2)
818 master-bin.000001 # Table_map # # table_id: # (test.t3)
819 master-bin.000001 # Write_rows_v1 # # table_id: #
820 master-bin.000001 # Write_rows_v1 # # table_id: #
821 master-bin.000001 # Write_rows_v1 # # table_id: #
822 master-bin.000001 # Write_rows_v1 # # table_id: #
823 master-bin.000001 # Write_rows_v1 # # table_id: #
824 master-bin.000001 # Write_rows_v1 # # table_id: #
825 master-bin.000001 # Write_rows_v1 # # table_id: #
826 master-bin.000001 # Write_rows_v1 # # table_id: #
827 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
828 master-bin.000001 # Xid # # COMMIT /* XID */
829 commit;
830 connection master;
831 connection slave;
832 #Test if the results are consistent on master and slave
833 #for 'CALLS A FUNCTION which INVOKES A TRIGGER with after insert action'
834 include/diff_tables.inc [master:t2, slave:t2]
835 include/diff_tables.inc [master:t3, slave:t3]
836 connection master;
837 drop table t1;
838 drop table t2;
839 drop table t3;
840 drop function f1_two_inserts_trigger;
841 connection slave;
842 # Test case8: CALLS A FUNCTION which INVOKES A TRIGGER with before insert action
843 connection master;
844 create table t1(a int) engine=innodb;
845 create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
846 create table t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
847 CREATE FUNCTION f1_two_inserts_trigger() RETURNS INTEGER
848 BEGIN
849 INSERT INTO t2(a) values(2),(3);
850 INSERT INTO t2(a) values(2),(3);
851 RETURN 1;
852 END |
853 create trigger tr11 before insert on t2 for each row begin
854 insert into t3(a) values(new.a);
855 insert into t3(a) values(new.a);
856 end |
857 begin;
858 insert into t1(a) values(f1_two_inserts_trigger());
859 connection master1;
860 insert into t2(a) values(4),(5);
861 connection master;
862 commit;
863 insert into t1(a) values(f1_two_inserts_trigger());
864 # To verify if insert/update in an autoinc column causes statement to be logged in row format
865 include/show_binlog_events.inc
866 Log_name Pos Event_type Server_id End_log_pos Info
867 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
868 master-bin.000001 # Annotate_rows # # insert into t3(a) values(new.a)
869 master-bin.000001 # Table_map # # table_id: # (test.t2)
870 master-bin.000001 # Table_map # # table_id: # (test.t3)
871 master-bin.000001 # Write_rows_v1 # # table_id: #
872 master-bin.000001 # Write_rows_v1 # # table_id: #
873 master-bin.000001 # Write_rows_v1 # # table_id: #
874 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
875 master-bin.000001 # Xid # # COMMIT /* XID */
876 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
877 master-bin.000001 # Annotate_rows # # insert into t3(a) values(new.a)
878 master-bin.000001 # Table_map # # table_id: # (test.t1)
879 master-bin.000001 # Table_map # # table_id: # (test.t2)
880 master-bin.000001 # Table_map # # table_id: # (test.t3)
881 master-bin.000001 # Write_rows_v1 # # table_id: #
882 master-bin.000001 # Write_rows_v1 # # table_id: #
883 master-bin.000001 # Write_rows_v1 # # table_id: #
884 master-bin.000001 # Write_rows_v1 # # table_id: #
885 master-bin.000001 # Write_rows_v1 # # table_id: #
886 master-bin.000001 # Write_rows_v1 # # table_id: #
887 master-bin.000001 # Write_rows_v1 # # table_id: #
888 master-bin.000001 # Write_rows_v1 # # table_id: #
889 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
890 master-bin.000001 # Xid # # COMMIT /* XID */
891 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
892 master-bin.000001 # Annotate_rows # # insert into t3(a) values(new.a)
893 master-bin.000001 # Table_map # # table_id: # (test.t1)
894 master-bin.000001 # Table_map # # table_id: # (test.t2)
895 master-bin.000001 # Table_map # # table_id: # (test.t3)
896 master-bin.000001 # Write_rows_v1 # # table_id: #
897 master-bin.000001 # Write_rows_v1 # # table_id: #
898 master-bin.000001 # Write_rows_v1 # # table_id: #
899 master-bin.000001 # Write_rows_v1 # # table_id: #
900 master-bin.000001 # Write_rows_v1 # # table_id: #
901 master-bin.000001 # Write_rows_v1 # # table_id: #
902 master-bin.000001 # Write_rows_v1 # # table_id: #
903 master-bin.000001 # Write_rows_v1 # # table_id: #
904 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
905 master-bin.000001 # Xid # # COMMIT /* XID */
906 commit;
907 connection master;
908 connection slave;
909 #Test if the results are consistent on master and slave
910 #for 'CALLS A FUNCTION which INVOKES A TRIGGER with before insert action'
911 include/diff_tables.inc [master:t2, slave:t2]
912 include/diff_tables.inc [master:t3, slave:t3]
913 connection master;
914 drop table t1;
915 drop table t2;
916 drop table t3;
917 drop function f1_two_inserts_trigger;
918 connection slave;
919 # Test case9: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with after insert action
920 connection master;
921 CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb;
922 CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb;
923 CREATE TABLE t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
924 create trigger tr16 after insert on t1 for each row insert into t3(a) values(new.c1);
925 create trigger tr17 after insert on t2 for each row insert into t3(a) values(new.c2);
926 begin;
927 INSERT INTO t1(c1) VALUES (11), (12);
928 INSERT INTO t2(c2) VALUES (13), (14);
929 CREATE VIEW v16 AS SELECT c1, c2 FROM t1, t2;
930 INSERT INTO v16(c1) VALUES (15),(16);
931 INSERT INTO v16(c2) VALUES (17),(18);
932 connection master1;
933 INSERT INTO v16(c1) VALUES (19),(20);
934 INSERT INTO v16(c2) VALUES (21),(22);
935 connection master;
936 INSERT INTO v16(c1) VALUES (23), (24);
937 INSERT INTO v16(c1) VALUES (25), (26);
938 commit;
939 connection slave;
940 #Test if the results are consistent on master and slave
941 #for 'INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS'
942 include/diff_tables.inc [master:t3, slave:t3]
943 connection master;
944 DROP TABLE t1;
945 DROP TABLE t2;
946 DROP TABLE t3;
947 DROP VIEW v16;
948 connection slave;
949 # Test case10: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with before insert action
950 connection master;
951 CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb;
952 CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb;
953 CREATE TABLE t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
954 create trigger tr16 before insert on t1 for each row insert into t3(a) values(new.c1);
955 create trigger tr17 before insert on t2 for each row insert into t3(a) values(new.c2);
956 begin;
957 INSERT INTO t1(c1) VALUES (11), (12);
958 INSERT INTO t2(c2) VALUES (13), (14);
959 CREATE VIEW v16 AS SELECT c1, c2 FROM t1, t2;
960 INSERT INTO v16(c1) VALUES (15),(16);
961 INSERT INTO v16(c2) VALUES (17),(18);
962 connection master1;
963 INSERT INTO v16(c1) VALUES (19),(20);
964 INSERT INTO v16(c2) VALUES (21),(22);
965 connection master;
966 INSERT INTO v16(c1) VALUES (23), (24);
967 INSERT INTO v16(c1) VALUES (25), (26);
968 commit;
969 connection slave;
970 #Test if the results are consistent on master and slave
971 #for 'INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS'
972 include/diff_tables.inc [master:t3, slave:t3]
973 connection master;
974 DROP TABLE t1;
975 DROP TABLE t2;
976 DROP TABLE t3;
977 DROP VIEW v16;
978 connection slave;
979 # Test case11: INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES INTO A TABLE WITH AUTOINC COLUMN
980 connection master;
981 create table t1(a int) engine=innodb;
982 create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
983 CREATE FUNCTION f1_two_inserts() RETURNS INTEGER
984 BEGIN
985 INSERT INTO t2(a) values(2);
986 INSERT INTO t2(a) values(2);
987 RETURN 1;
988 END//
989 begin;
990 insert into t1(a) values(f1_two_inserts());
991 connection master1;
992 insert into t2(a) values(4),(5);
993 connection master;
994 commit;
995 insert into t1(a) values(f1_two_inserts());
996 commit;
997 connection master;
998 #Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on master
999 select * from t2 ORDER BY i1;
1000 i1 a
1001 1 2
1002 2 2
1003 3 4
1004 4 5
1005 5 2
1006 6 2
1007 connection slave;
1008 connection slave;
1009 #Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on slave
1010 select * from t2 ORDER BY i1;
1011 i1 a
1012 1 2
1013 2 2
1014 3 4
1015 4 5
1016 5 2
1017 6 2
1018 connection master;
1019 drop table t1;
1020 drop table t2;
1021 drop function f1_two_inserts;
1022 connection slave;
1023 # Test case12: INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES OF A TABLE WITH AUTOINC COLUMN
1024 connection master;
1025 create table t1(a int) engine=innodb;
1026 create table t2(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb;
1027 CREATE FUNCTION f1_two_updates() RETURNS INTEGER
1028 BEGIN
1029 update t2 set a = a + 5 where b = 1;
1030 update t2 set a = a + 5 where b = 2;
1031 update t2 set a = a + 5 where b = 3;
1032 update t2 set a = a + 5 where b = 4;
1033 RETURN 1;
1034 END//
1035 connection master1;
1036 insert into t2(a,b) values(1,1);
1037 insert into t2(a,b) values(2,2);
1038 insert into t2(a,b) values(3,3);
1039 insert into t2(a,b) values(4,4);
1040 insert into t1(a) values(f1_two_updates());
1041 connection master;
1042 begin;
1043 insert into t1(a) values(f1_two_updates());
1044 commit;
1045 connection master;
1046 #Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on master
1047 select * from t2 ORDER BY i1;
1048 i1 a b
1049 1 11 1
1050 2 12 2
1051 3 13 3
1052 4 14 4
1053 connection slave;
1054 connection slave;
1055 #Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on slave
1056 select * from t2 ORDER BY i1;
1057 i1 a b
1058 1 11 1
1059 2 12 2
1060 3 13 3
1061 4 14 4
1062 connection master;
1063 drop table t1;
1064 drop table t2;
1065 drop function f1_two_updates;
1066 connection slave;
1067 # Test case13: UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT
1068 connection master;
1069 create table t1(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb;
1070 create table t2(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb;
1071 begin;
1072 insert into t1(a,b) values(1,1),(2,2);
1073 insert into t2(a,b) values(1,1),(2,2);
1074 update t1,t2 set t1.a=t1.a+5, t2.a=t2.a+5 where t1.b=t2.b;
1075 insert into t1(a,b) values(3,3);
1076 insert into t2(a,b) values(3,3);
1077 commit;
1078 # To verify if it works fine when these statements are not be marked as unsafe
1079 include/show_binlog_events.inc
1080 Log_name Pos Event_type Server_id End_log_pos Info
1081 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
1082 master-bin.000001 # Intvar # # INSERT_ID=1
1083 master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,2)
1084 master-bin.000001 # Intvar # # INSERT_ID=1
1085 master-bin.000001 # Query # # use `test`; insert into t2(a,b) values(1,1),(2,2)
1086 master-bin.000001 # Query # # use `test`; update t1,t2 set t1.a=t1.a+5, t2.a=t2.a+5 where t1.b=t2.b
1087 master-bin.000001 # Intvar # # INSERT_ID=3
1088 master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(3,3)
1089 master-bin.000001 # Intvar # # INSERT_ID=3
1090 master-bin.000001 # Query # # use `test`; insert into t2(a,b) values(3,3)
1091 master-bin.000001 # Xid # # COMMIT /* XID */
1092 connection slave;
1093 #Test if the results are consistent on master and slave
1094 #for 'UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT'
1095 include/diff_tables.inc [master:t1, slave:t1]
1096 include/diff_tables.inc [master:t2, slave:t2]
1097 connection master;
1098 drop table t1;
1099 drop table t2;
1100 connection slave;
1101 # Test case14: INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES
1102 connection master;
1103 CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb;
1104 CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb;
1105 begin;
1106 INSERT INTO t1(c1) VALUES (11), (12);
1107 INSERT INTO t2(c2) VALUES (13), (14);
1108 CREATE VIEW v15 AS SELECT c1, c2 FROM t1, t2;
1109 INSERT INTO v15(c1) VALUES (15),(16);
1110 INSERT INTO v15(c2) VALUES (17),(18);
1111 connection master1;
1112 INSERT INTO v15(c1) VALUES (19),(20);
1113 INSERT INTO v15(c2) VALUES (21),(22);
1114 connection master;
1115 INSERT INTO v15(c1) VALUES (23), (24);
1116 INSERT INTO v15(c2) VALUES (25), (26);
1117 commit;
1118 # To verify if it works fine when these statements are not be marked as unsafe
1119 include/show_binlog_events.inc
1120 Log_name Pos Event_type Server_id End_log_pos Info
1121 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
1122 master-bin.000001 # Intvar # # INSERT_ID=1
1123 master-bin.000001 # Query # # use `test`; INSERT INTO t1(c1) VALUES (11), (12)
1124 master-bin.000001 # Intvar # # INSERT_ID=1
1125 master-bin.000001 # Query # # use `test`; INSERT INTO t2(c2) VALUES (13), (14)
1126 master-bin.000001 # Xid # # COMMIT /* XID */
1127 master-bin.000001 # Gtid # # GTID #-#-#
1128 master-bin.000001 # Query # # use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v15` AS SELECT c1, c2 FROM t1, t2
1129 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
1130 master-bin.000001 # Intvar # # INSERT_ID=3
1131 master-bin.000001 # Query # # use `test`; INSERT INTO v15(c1) VALUES (15),(16)
1132 master-bin.000001 # Xid # # COMMIT /* XID */
1133 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
1134 master-bin.000001 # Intvar # # INSERT_ID=3
1135 master-bin.000001 # Query # # use `test`; INSERT INTO v15(c2) VALUES (17),(18)
1136 master-bin.000001 # Xid # # COMMIT /* XID */
1137 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
1138 master-bin.000001 # Intvar # # INSERT_ID=5
1139 master-bin.000001 # Query # # use `test`; INSERT INTO v15(c1) VALUES (19),(20)
1140 master-bin.000001 # Xid # # COMMIT /* XID */
1141 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
1142 master-bin.000001 # Intvar # # INSERT_ID=5
1143 master-bin.000001 # Query # # use `test`; INSERT INTO v15(c2) VALUES (21),(22)
1144 master-bin.000001 # Xid # # COMMIT /* XID */
1145 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
1146 master-bin.000001 # Intvar # # INSERT_ID=7
1147 master-bin.000001 # Query # # use `test`; INSERT INTO v15(c1) VALUES (23), (24)
1148 master-bin.000001 # Xid # # COMMIT /* XID */
1149 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
1150 master-bin.000001 # Intvar # # INSERT_ID=7
1151 master-bin.000001 # Query # # use `test`; INSERT INTO v15(c2) VALUES (25), (26)
1152 master-bin.000001 # Xid # # COMMIT /* XID */
1153 connection slave;
1154 #Test if the results are consistent on master and slave
1155 #for 'INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES'
1156 include/diff_tables.inc [master:t1, slave:t1]
1157 include/diff_tables.inc [master:t2, slave:t2]
1158 connection master;
1159 drop table t1;
1160 drop table t2;
1161 drop view v15;
1162 connection slave;
1163 include/rpl_end.inc