Debian Codegraph @

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

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