2
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
5
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
7
# Test case1: INVOKES A TRIGGER with after insert action
8
create table t1(a int, b int) engine=innodb;
9
create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
10
create trigger tr1 after insert on t1 for each row insert into t2(a) values(6);
11
create table t3(a int, b int) engine=innodb;
12
create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
13
create table t5(a int) engine=innodb;
14
create trigger tr2 after insert on t3 for each row begin
15
insert into t4(a) values(f1_insert_triggered());
16
insert into t4(a) values(f1_insert_triggered());
17
insert into t5(a) values(8);
19
create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
20
CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
22
INSERT INTO t6(a) values(2),(3);
26
insert into t1(a,b) values(1,1),(2,1);
27
insert into t3(a,b) values(1,1),(2,1);
28
update t1 set a = a + 5 where b = 1;
29
update t3 set a = a + 5 where b = 1;
30
delete from t1 where b = 1;
31
delete from t3 where b = 1;
32
insert into t2(a) values(3);
33
insert into t4(a) values(3);
35
insert into t1(a,b) values(4,2);
36
insert into t3(a,b) values(4,2);
37
update t1 set a = a + 5 where b = 2;
38
update t3 set a = a + 5 where b = 2;
39
delete from t1 where b = 2;
40
delete from t3 where b = 2;
41
# To verify if insert/update in an autoinc column causes statement to be logged in row format
42
show binlog events from <binlog_start>;
43
Log_name Pos Event_type Server_id End_log_pos Info
44
master-bin.000001 # Query # # BEGIN
45
master-bin.000001 # Intvar # # INSERT_ID=3
46
master-bin.000001 # Query # # use `test`; insert into t2(a) values(3)
47
master-bin.000001 # Xid # # COMMIT /* XID */
48
master-bin.000001 # Query # # BEGIN
49
master-bin.000001 # Intvar # # INSERT_ID=5
50
master-bin.000001 # Query # # use `test`; insert into t4(a) values(3)
51
master-bin.000001 # Xid # # COMMIT /* XID */
52
master-bin.000001 # Query # # BEGIN
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 # # table_id: #
56
master-bin.000001 # Write_rows # # table_id: #
57
master-bin.000001 # Write_rows # # table_id: #
58
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
59
master-bin.000001 # Table_map # # table_id: # (test.t3)
60
master-bin.000001 # Table_map # # table_id: # (test.t5)
61
master-bin.000001 # Table_map # # table_id: # (test.t4)
62
master-bin.000001 # Table_map # # table_id: # (test.t6)
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: #
74
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
75
master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 1
76
master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 1
77
master-bin.000001 # Query # # use `test`; delete from t1 where b = 1
78
master-bin.000001 # Query # # use `test`; delete from t3 where b = 1
79
master-bin.000001 # Xid # # COMMIT /* XID */
80
master-bin.000001 # Query # # BEGIN
81
master-bin.000001 # Table_map # # table_id: # (test.t1)
82
master-bin.000001 # Table_map # # table_id: # (test.t2)
83
master-bin.000001 # Write_rows # # table_id: #
84
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
85
master-bin.000001 # Xid # # COMMIT /* XID */
86
master-bin.000001 # Query # # BEGIN
87
master-bin.000001 # Table_map # # table_id: # (test.t3)
88
master-bin.000001 # Table_map # # table_id: # (test.t5)
89
master-bin.000001 # Table_map # # table_id: # (test.t4)
90
master-bin.000001 # Table_map # # table_id: # (test.t6)
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: #
96
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
97
master-bin.000001 # Xid # # COMMIT /* XID */
98
master-bin.000001 # Query # # BEGIN
99
master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 2
100
master-bin.000001 # Xid # # COMMIT /* XID */
101
master-bin.000001 # Query # # BEGIN
102
master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 2
103
master-bin.000001 # Xid # # COMMIT /* XID */
104
master-bin.000001 # Query # # BEGIN
105
master-bin.000001 # Query # # use `test`; delete from t1 where b = 2
106
master-bin.000001 # Xid # # COMMIT /* XID */
107
master-bin.000001 # Query # # BEGIN
108
master-bin.000001 # Query # # use `test`; delete from t3 where b = 2
109
master-bin.000001 # Xid # # COMMIT /* XID */
111
#Test if the results are consistent on master and slave
112
#for 'INVOKES A TRIGGER with after insert action'
113
Comparing tables master:test.t2 and slave:test.t2
114
Comparing tables master:test.t4 and slave:test.t4
115
Comparing tables master:test.t6 and slave:test.t6
122
DROP FUNCTION f1_insert_triggered;
123
# Test case2: INVOKES A TRIGGER with before insert action
124
create table t1(a int, b int) engine=innodb;
125
create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
126
create trigger tr1 before insert on t1 for each row insert into t2(a) values(6);
127
create table t3(a int, b int) engine=innodb;
128
create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
129
create table t5(a int) engine=innodb;
130
create trigger tr2 before insert on t3 for each row begin
131
insert into t4(a) values(f1_insert_triggered());
132
insert into t4(a) values(f1_insert_triggered());
133
insert into t5(a) values(8);
135
create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
136
CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
138
INSERT INTO t6(a) values(2),(3);
142
insert into t1(a,b) values(1,1),(2,1);
143
insert into t3(a,b) values(1,1),(2,1);
144
update t1 set a = a + 5 where b = 1;
145
update t3 set a = a + 5 where b = 1;
146
delete from t1 where b = 1;
147
delete from t3 where b = 1;
148
insert into t2(a) values(3);
149
insert into t4(a) values(3);
151
insert into t1(a,b) values(4,2);
152
insert into t3(a,b) values(4,2);
153
update t1 set a = a + 5 where b = 2;
154
update t3 set a = a + 5 where b = 2;
155
delete from t1 where b = 2;
156
delete from t3 where b = 2;
157
# To verify if insert/update in an autoinc column causes statement to be logged in row format
158
show binlog events from <binlog_start>;
159
Log_name Pos Event_type Server_id End_log_pos Info
160
master-bin.000001 # Query # # BEGIN
161
master-bin.000001 # Intvar # # INSERT_ID=3
162
master-bin.000001 # Query # # use `test`; insert into t2(a) values(3)
163
master-bin.000001 # Xid # # COMMIT /* XID */
164
master-bin.000001 # Query # # BEGIN
165
master-bin.000001 # Intvar # # INSERT_ID=5
166
master-bin.000001 # Query # # use `test`; insert into t4(a) values(3)
167
master-bin.000001 # Xid # # COMMIT /* XID */
168
master-bin.000001 # Query # # BEGIN
169
master-bin.000001 # Table_map # # table_id: # (test.t1)
170
master-bin.000001 # Table_map # # table_id: # (test.t2)
171
master-bin.000001 # Write_rows # # table_id: #
172
master-bin.000001 # Write_rows # # table_id: #
173
master-bin.000001 # Write_rows # # table_id: #
174
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
175
master-bin.000001 # Table_map # # table_id: # (test.t3)
176
master-bin.000001 # Table_map # # table_id: # (test.t5)
177
master-bin.000001 # Table_map # # table_id: # (test.t4)
178
master-bin.000001 # Table_map # # table_id: # (test.t6)
179
master-bin.000001 # Write_rows # # table_id: #
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: # flags: STMT_END_F
191
master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 1
192
master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 1
193
master-bin.000001 # Query # # use `test`; delete from t1 where b = 1
194
master-bin.000001 # Query # # use `test`; delete from t3 where b = 1
195
master-bin.000001 # Xid # # COMMIT /* XID */
196
master-bin.000001 # Query # # BEGIN
197
master-bin.000001 # Table_map # # table_id: # (test.t1)
198
master-bin.000001 # Table_map # # table_id: # (test.t2)
199
master-bin.000001 # Write_rows # # table_id: #
200
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
201
master-bin.000001 # Xid # # COMMIT /* XID */
202
master-bin.000001 # Query # # BEGIN
203
master-bin.000001 # Table_map # # table_id: # (test.t3)
204
master-bin.000001 # Table_map # # table_id: # (test.t5)
205
master-bin.000001 # Table_map # # table_id: # (test.t4)
206
master-bin.000001 # Table_map # # table_id: # (test.t6)
207
master-bin.000001 # Write_rows # # table_id: #
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: # flags: STMT_END_F
213
master-bin.000001 # Xid # # COMMIT /* XID */
214
master-bin.000001 # Query # # BEGIN
215
master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 2
216
master-bin.000001 # Xid # # COMMIT /* XID */
217
master-bin.000001 # Query # # BEGIN
218
master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 2
219
master-bin.000001 # Xid # # COMMIT /* XID */
220
master-bin.000001 # Query # # BEGIN
221
master-bin.000001 # Query # # use `test`; delete from t1 where b = 2
222
master-bin.000001 # Xid # # COMMIT /* XID */
223
master-bin.000001 # Query # # BEGIN
224
master-bin.000001 # Query # # use `test`; delete from t3 where b = 2
225
master-bin.000001 # Xid # # COMMIT /* XID */
227
#Test if the results are consistent on master and slave
228
#for 'INVOKES A TRIGGER with before insert action'
229
Comparing tables master:test.t2 and slave:test.t2
230
Comparing tables master:test.t4 and slave:test.t4
231
Comparing tables master:test.t6 and slave:test.t6
238
DROP FUNCTION f1_insert_triggered;
239
# Test case3: INVOKES A TRIGGER with after update action
240
create table t1(a int, b int) engine=innodb;
241
create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
242
create trigger tr1 after update on t1 for each row insert into t2(a) values(6);
243
create table t3(a int, b int) engine=innodb;
244
create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
245
create table t5(a int) engine=innodb;
246
create trigger tr2 after update on t3 for each row begin
247
insert into t4(a) values(f1_insert_triggered());
248
insert into t4(a) values(f1_insert_triggered());
249
insert into t5(a) values(8);
251
create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
252
CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
254
INSERT INTO t6(a) values(2),(3);
258
insert into t1(a,b) values(1,1),(2,1);
259
insert into t3(a,b) values(1,1),(2,1);
260
update t1 set a = a + 5 where b = 1;
261
update t3 set a = a + 5 where b = 1;
262
delete from t1 where b = 1;
263
delete from t3 where b = 1;
264
insert into t2(a) values(3);
265
insert into t4(a) values(3);
267
insert into t1(a,b) values(4,2);
268
insert into t3(a,b) values(4,2);
269
update t1 set a = a + 5 where b = 2;
270
update t3 set a = a + 5 where b = 2;
271
delete from t1 where b = 2;
272
delete from t3 where b = 2;
273
# To verify if insert/update in an autoinc column causes statement to be logged in row format
274
show binlog events from <binlog_start>;
275
Log_name Pos Event_type Server_id End_log_pos Info
276
master-bin.000001 # Query # # BEGIN
277
master-bin.000001 # Intvar # # INSERT_ID=3
278
master-bin.000001 # Query # # use `test`; insert into t2(a) values(3)
279
master-bin.000001 # Xid # # COMMIT /* XID */
280
master-bin.000001 # Query # # BEGIN
281
master-bin.000001 # Intvar # # INSERT_ID=5
282
master-bin.000001 # Query # # use `test`; insert into t4(a) values(3)
283
master-bin.000001 # Xid # # COMMIT /* XID */
284
master-bin.000001 # Query # # BEGIN
285
master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,1)
286
master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(1,1),(2,1)
287
master-bin.000001 # Table_map # # table_id: # (test.t1)
288
master-bin.000001 # Table_map # # table_id: # (test.t2)
289
master-bin.000001 # Update_rows # # table_id: #
290
master-bin.000001 # Write_rows # # table_id: #
291
master-bin.000001 # Update_rows # # table_id: #
292
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
293
master-bin.000001 # Table_map # # table_id: # (test.t3)
294
master-bin.000001 # Table_map # # table_id: # (test.t5)
295
master-bin.000001 # Table_map # # table_id: # (test.t4)
296
master-bin.000001 # Table_map # # table_id: # (test.t6)
297
master-bin.000001 # Update_rows # # table_id: #
298
master-bin.000001 # Write_rows # # table_id: #
299
master-bin.000001 # Write_rows # # table_id: #
300
master-bin.000001 # Write_rows # # table_id: #
301
master-bin.000001 # Write_rows # # table_id: #
302
master-bin.000001 # Write_rows # # table_id: #
303
master-bin.000001 # Update_rows # # table_id: #
304
master-bin.000001 # Write_rows # # table_id: #
305
master-bin.000001 # Write_rows # # table_id: #
306
master-bin.000001 # Write_rows # # table_id: #
307
master-bin.000001 # Write_rows # # table_id: #
308
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
309
master-bin.000001 # Query # # use `test`; delete from t1 where b = 1
310
master-bin.000001 # Query # # use `test`; delete from t3 where b = 1
311
master-bin.000001 # Xid # # COMMIT /* XID */
312
master-bin.000001 # Query # # BEGIN
313
master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(4,2)
314
master-bin.000001 # Xid # # COMMIT /* XID */
315
master-bin.000001 # Query # # BEGIN
316
master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(4,2)
317
master-bin.000001 # Xid # # COMMIT /* XID */
318
master-bin.000001 # Query # # BEGIN
319
master-bin.000001 # Table_map # # table_id: # (test.t1)
320
master-bin.000001 # Table_map # # table_id: # (test.t2)
321
master-bin.000001 # Update_rows # # table_id: #
322
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
323
master-bin.000001 # Xid # # COMMIT /* XID */
324
master-bin.000001 # Query # # BEGIN
325
master-bin.000001 # Table_map # # table_id: # (test.t3)
326
master-bin.000001 # Table_map # # table_id: # (test.t5)
327
master-bin.000001 # Table_map # # table_id: # (test.t4)
328
master-bin.000001 # Table_map # # table_id: # (test.t6)
329
master-bin.000001 # Update_rows # # table_id: #
330
master-bin.000001 # Write_rows # # table_id: #
331
master-bin.000001 # Write_rows # # table_id: #
332
master-bin.000001 # Write_rows # # table_id: #
333
master-bin.000001 # Write_rows # # table_id: #
334
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
335
master-bin.000001 # Xid # # COMMIT /* XID */
336
master-bin.000001 # Query # # BEGIN
337
master-bin.000001 # Query # # use `test`; delete from t1 where b = 2
338
master-bin.000001 # Xid # # COMMIT /* XID */
339
master-bin.000001 # Query # # BEGIN
340
master-bin.000001 # Query # # use `test`; delete from t3 where b = 2
341
master-bin.000001 # Xid # # COMMIT /* XID */
343
#Test if the results are consistent on master and slave
344
#for 'INVOKES A TRIGGER with after update action'
345
Comparing tables master:test.t2 and slave:test.t2
346
Comparing tables master:test.t4 and slave:test.t4
347
Comparing tables master:test.t6 and slave:test.t6
354
DROP FUNCTION f1_insert_triggered;
355
# Test case4: INVOKES A TRIGGER with before update action
356
create table t1(a int, b int) engine=innodb;
357
create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
358
create trigger tr1 before update on t1 for each row insert into t2(a) values(6);
359
create table t3(a int, b int) engine=innodb;
360
create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
361
create table t5(a int) engine=innodb;
362
create trigger tr2 before update on t3 for each row begin
363
insert into t4(a) values(f1_insert_triggered());
364
insert into t4(a) values(f1_insert_triggered());
365
insert into t5(a) values(8);
367
create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
368
CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
370
INSERT INTO t6(a) values(2),(3);
374
insert into t1(a,b) values(1,1),(2,1);
375
insert into t3(a,b) values(1,1),(2,1);
376
update t1 set a = a + 5 where b = 1;
377
update t3 set a = a + 5 where b = 1;
378
delete from t1 where b = 1;
379
delete from t3 where b = 1;
380
insert into t2(a) values(3);
381
insert into t4(a) values(3);
383
insert into t1(a,b) values(4,2);
384
insert into t3(a,b) values(4,2);
385
update t1 set a = a + 5 where b = 2;
386
update t3 set a = a + 5 where b = 2;
387
delete from t1 where b = 2;
388
delete from t3 where b = 2;
389
# To verify if insert/update in an autoinc column causes statement to be logged in row format
390
show binlog events from <binlog_start>;
391
Log_name Pos Event_type Server_id End_log_pos Info
392
master-bin.000001 # Query # # BEGIN
393
master-bin.000001 # Intvar # # INSERT_ID=3
394
master-bin.000001 # Query # # use `test`; insert into t2(a) values(3)
395
master-bin.000001 # Xid # # COMMIT /* XID */
396
master-bin.000001 # Query # # BEGIN
397
master-bin.000001 # Intvar # # INSERT_ID=5
398
master-bin.000001 # Query # # use `test`; insert into t4(a) values(3)
399
master-bin.000001 # Xid # # COMMIT /* XID */
400
master-bin.000001 # Query # # BEGIN
401
master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,1)
402
master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(1,1),(2,1)
403
master-bin.000001 # Table_map # # table_id: # (test.t1)
404
master-bin.000001 # Table_map # # table_id: # (test.t2)
405
master-bin.000001 # Write_rows # # table_id: #
406
master-bin.000001 # Update_rows # # table_id: #
407
master-bin.000001 # Write_rows # # table_id: #
408
master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F
409
master-bin.000001 # Table_map # # table_id: # (test.t3)
410
master-bin.000001 # Table_map # # table_id: # (test.t5)
411
master-bin.000001 # Table_map # # table_id: # (test.t4)
412
master-bin.000001 # Table_map # # table_id: # (test.t6)
413
master-bin.000001 # Write_rows # # table_id: #
414
master-bin.000001 # Write_rows # # table_id: #
415
master-bin.000001 # Write_rows # # table_id: #
416
master-bin.000001 # Write_rows # # table_id: #
417
master-bin.000001 # Write_rows # # table_id: #
418
master-bin.000001 # Update_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 # Write_rows # # table_id: #
424
master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F
425
master-bin.000001 # Query # # use `test`; delete from t1 where b = 1
426
master-bin.000001 # Query # # use `test`; delete from t3 where b = 1
427
master-bin.000001 # Xid # # COMMIT /* XID */
428
master-bin.000001 # Query # # BEGIN
429
master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(4,2)
430
master-bin.000001 # Xid # # COMMIT /* XID */
431
master-bin.000001 # Query # # BEGIN
432
master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(4,2)
433
master-bin.000001 # Xid # # COMMIT /* XID */
434
master-bin.000001 # Query # # BEGIN
435
master-bin.000001 # Table_map # # table_id: # (test.t1)
436
master-bin.000001 # Table_map # # table_id: # (test.t2)
437
master-bin.000001 # Write_rows # # table_id: #
438
master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F
439
master-bin.000001 # Xid # # COMMIT /* XID */
440
master-bin.000001 # Query # # BEGIN
441
master-bin.000001 # Table_map # # table_id: # (test.t3)
442
master-bin.000001 # Table_map # # table_id: # (test.t5)
443
master-bin.000001 # Table_map # # table_id: # (test.t4)
444
master-bin.000001 # Table_map # # table_id: # (test.t6)
445
master-bin.000001 # Write_rows # # table_id: #
446
master-bin.000001 # Write_rows # # table_id: #
447
master-bin.000001 # Write_rows # # table_id: #
448
master-bin.000001 # Write_rows # # table_id: #
449
master-bin.000001 # Write_rows # # table_id: #
450
master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F
451
master-bin.000001 # Xid # # COMMIT /* XID */
452
master-bin.000001 # Query # # BEGIN
453
master-bin.000001 # Query # # use `test`; delete from t1 where b = 2
454
master-bin.000001 # Xid # # COMMIT /* XID */
455
master-bin.000001 # Query # # BEGIN
456
master-bin.000001 # Query # # use `test`; delete from t3 where b = 2
457
master-bin.000001 # Xid # # COMMIT /* XID */
459
#Test if the results are consistent on master and slave
460
#for 'INVOKES A TRIGGER with before update action'
461
Comparing tables master:test.t2 and slave:test.t2
462
Comparing tables master:test.t4 and slave:test.t4
463
Comparing tables master:test.t6 and slave:test.t6
470
DROP FUNCTION f1_insert_triggered;
471
# Test case5: INVOKES A TRIGGER with after delete action
472
create table t1(a int, b int) engine=innodb;
473
create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
474
create trigger tr1 after delete on t1 for each row insert into t2(a) values(6);
475
create table t3(a int, b int) engine=innodb;
476
create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
477
create table t5(a int) engine=innodb;
478
create trigger tr2 after delete on t3 for each row begin
479
insert into t4(a) values(f1_insert_triggered());
480
insert into t4(a) values(f1_insert_triggered());
481
insert into t5(a) values(8);
483
create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
484
CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
486
INSERT INTO t6(a) values(2),(3);
490
insert into t1(a,b) values(1,1),(2,1);
491
insert into t3(a,b) values(1,1),(2,1);
492
update t1 set a = a + 5 where b = 1;
493
update t3 set a = a + 5 where b = 1;
494
delete from t1 where b = 1;
495
delete from t3 where b = 1;
496
insert into t2(a) values(3);
497
insert into t4(a) values(3);
499
insert into t1(a,b) values(4,2);
500
insert into t3(a,b) values(4,2);
501
update t1 set a = a + 5 where b = 2;
502
update t3 set a = a + 5 where b = 2;
503
delete from t1 where b = 2;
504
delete from t3 where b = 2;
505
# To verify if insert/update in an autoinc column causes statement to be logged in row format
506
show binlog events from <binlog_start>;
507
Log_name Pos Event_type Server_id End_log_pos Info
508
master-bin.000001 # Query # # BEGIN
509
master-bin.000001 # Intvar # # INSERT_ID=3
510
master-bin.000001 # Query # # use `test`; insert into t2(a) values(3)
511
master-bin.000001 # Xid # # COMMIT /* XID */
512
master-bin.000001 # Query # # BEGIN
513
master-bin.000001 # Intvar # # INSERT_ID=5
514
master-bin.000001 # Query # # use `test`; insert into t4(a) values(3)
515
master-bin.000001 # Xid # # COMMIT /* XID */
516
master-bin.000001 # Query # # BEGIN
517
master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,1)
518
master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(1,1),(2,1)
519
master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 1
520
master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 1
521
master-bin.000001 # Table_map # # table_id: # (test.t1)
522
master-bin.000001 # Table_map # # table_id: # (test.t2)
523
master-bin.000001 # Delete_rows # # table_id: #
524
master-bin.000001 # Write_rows # # table_id: #
525
master-bin.000001 # Delete_rows # # table_id: #
526
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
527
master-bin.000001 # Table_map # # table_id: # (test.t3)
528
master-bin.000001 # Table_map # # table_id: # (test.t5)
529
master-bin.000001 # Table_map # # table_id: # (test.t4)
530
master-bin.000001 # Table_map # # table_id: # (test.t6)
531
master-bin.000001 # Delete_rows # # table_id: #
532
master-bin.000001 # Write_rows # # table_id: #
533
master-bin.000001 # Write_rows # # table_id: #
534
master-bin.000001 # Write_rows # # table_id: #
535
master-bin.000001 # Write_rows # # table_id: #
536
master-bin.000001 # Write_rows # # table_id: #
537
master-bin.000001 # Delete_rows # # table_id: #
538
master-bin.000001 # Write_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: # flags: STMT_END_F
543
master-bin.000001 # Xid # # COMMIT /* XID */
544
master-bin.000001 # Query # # BEGIN
545
master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(4,2)
546
master-bin.000001 # Xid # # COMMIT /* XID */
547
master-bin.000001 # Query # # BEGIN
548
master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(4,2)
549
master-bin.000001 # Xid # # COMMIT /* XID */
550
master-bin.000001 # Query # # BEGIN
551
master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 2
552
master-bin.000001 # Xid # # COMMIT /* XID */
553
master-bin.000001 # Query # # BEGIN
554
master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 2
555
master-bin.000001 # Xid # # COMMIT /* XID */
556
master-bin.000001 # Query # # BEGIN
557
master-bin.000001 # Table_map # # table_id: # (test.t1)
558
master-bin.000001 # Table_map # # table_id: # (test.t2)
559
master-bin.000001 # Delete_rows # # table_id: #
560
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
561
master-bin.000001 # Xid # # COMMIT /* XID */
562
master-bin.000001 # Query # # BEGIN
563
master-bin.000001 # Table_map # # table_id: # (test.t3)
564
master-bin.000001 # Table_map # # table_id: # (test.t5)
565
master-bin.000001 # Table_map # # table_id: # (test.t4)
566
master-bin.000001 # Table_map # # table_id: # (test.t6)
567
master-bin.000001 # Delete_rows # # table_id: #
568
master-bin.000001 # Write_rows # # table_id: #
569
master-bin.000001 # Write_rows # # table_id: #
570
master-bin.000001 # Write_rows # # table_id: #
571
master-bin.000001 # Write_rows # # table_id: #
572
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
573
master-bin.000001 # Xid # # COMMIT /* XID */
575
#Test if the results are consistent on master and slave
576
#for 'INVOKES A TRIGGER with after delete action'
577
Comparing tables master:test.t2 and slave:test.t2
578
Comparing tables master:test.t4 and slave:test.t4
579
Comparing tables master:test.t6 and slave:test.t6
586
DROP FUNCTION f1_insert_triggered;
587
# Test case6: INVOKES A TRIGGER with before delete action
588
create table t1(a int, b int) engine=innodb;
589
create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
590
create trigger tr1 before delete on t1 for each row insert into t2(a) values(6);
591
create table t3(a int, b int) engine=innodb;
592
create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
593
create table t5(a int) engine=innodb;
594
create trigger tr2 before delete on t3 for each row begin
595
insert into t4(a) values(f1_insert_triggered());
596
insert into t4(a) values(f1_insert_triggered());
597
insert into t5(a) values(8);
599
create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
600
CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER
602
INSERT INTO t6(a) values(2),(3);
606
insert into t1(a,b) values(1,1),(2,1);
607
insert into t3(a,b) values(1,1),(2,1);
608
update t1 set a = a + 5 where b = 1;
609
update t3 set a = a + 5 where b = 1;
610
delete from t1 where b = 1;
611
delete from t3 where b = 1;
612
insert into t2(a) values(3);
613
insert into t4(a) values(3);
615
insert into t1(a,b) values(4,2);
616
insert into t3(a,b) values(4,2);
617
update t1 set a = a + 5 where b = 2;
618
update t3 set a = a + 5 where b = 2;
619
delete from t1 where b = 2;
620
delete from t3 where b = 2;
621
# To verify if insert/update in an autoinc column causes statement to be logged in row format
622
show binlog events from <binlog_start>;
623
Log_name Pos Event_type Server_id End_log_pos Info
624
master-bin.000001 # Query # # BEGIN
625
master-bin.000001 # Intvar # # INSERT_ID=3
626
master-bin.000001 # Query # # use `test`; insert into t2(a) values(3)
627
master-bin.000001 # Xid # # COMMIT /* XID */
628
master-bin.000001 # Query # # BEGIN
629
master-bin.000001 # Intvar # # INSERT_ID=5
630
master-bin.000001 # Query # # use `test`; insert into t4(a) values(3)
631
master-bin.000001 # Xid # # COMMIT /* XID */
632
master-bin.000001 # Query # # BEGIN
633
master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,1)
634
master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(1,1),(2,1)
635
master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 1
636
master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 1
637
master-bin.000001 # Table_map # # table_id: # (test.t1)
638
master-bin.000001 # Table_map # # table_id: # (test.t2)
639
master-bin.000001 # Write_rows # # table_id: #
640
master-bin.000001 # Delete_rows # # table_id: #
641
master-bin.000001 # Write_rows # # table_id: #
642
master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F
643
master-bin.000001 # Table_map # # table_id: # (test.t3)
644
master-bin.000001 # Table_map # # table_id: # (test.t5)
645
master-bin.000001 # Table_map # # table_id: # (test.t4)
646
master-bin.000001 # Table_map # # table_id: # (test.t6)
647
master-bin.000001 # Write_rows # # table_id: #
648
master-bin.000001 # Write_rows # # table_id: #
649
master-bin.000001 # Write_rows # # table_id: #
650
master-bin.000001 # Write_rows # # table_id: #
651
master-bin.000001 # Write_rows # # table_id: #
652
master-bin.000001 # Delete_rows # # table_id: #
653
master-bin.000001 # Write_rows # # table_id: #
654
master-bin.000001 # Write_rows # # table_id: #
655
master-bin.000001 # Write_rows # # table_id: #
656
master-bin.000001 # Write_rows # # table_id: #
657
master-bin.000001 # Write_rows # # table_id: #
658
master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F
659
master-bin.000001 # Xid # # COMMIT /* XID */
660
master-bin.000001 # Query # # BEGIN
661
master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(4,2)
662
master-bin.000001 # Xid # # COMMIT /* XID */
663
master-bin.000001 # Query # # BEGIN
664
master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(4,2)
665
master-bin.000001 # Xid # # COMMIT /* XID */
666
master-bin.000001 # Query # # BEGIN
667
master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 2
668
master-bin.000001 # Xid # # COMMIT /* XID */
669
master-bin.000001 # Query # # BEGIN
670
master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 2
671
master-bin.000001 # Xid # # COMMIT /* XID */
672
master-bin.000001 # Query # # BEGIN
673
master-bin.000001 # Table_map # # table_id: # (test.t1)
674
master-bin.000001 # Table_map # # table_id: # (test.t2)
675
master-bin.000001 # Write_rows # # table_id: #
676
master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F
677
master-bin.000001 # Xid # # COMMIT /* XID */
678
master-bin.000001 # Query # # BEGIN
679
master-bin.000001 # Table_map # # table_id: # (test.t3)
680
master-bin.000001 # Table_map # # table_id: # (test.t5)
681
master-bin.000001 # Table_map # # table_id: # (test.t4)
682
master-bin.000001 # Table_map # # table_id: # (test.t6)
683
master-bin.000001 # Write_rows # # table_id: #
684
master-bin.000001 # Write_rows # # table_id: #
685
master-bin.000001 # Write_rows # # table_id: #
686
master-bin.000001 # Write_rows # # table_id: #
687
master-bin.000001 # Write_rows # # table_id: #
688
master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F
689
master-bin.000001 # Xid # # COMMIT /* XID */
691
#Test if the results are consistent on master and slave
692
#for 'INVOKES A TRIGGER with before delete action'
693
Comparing tables master:test.t2 and slave:test.t2
694
Comparing tables master:test.t4 and slave:test.t4
695
Comparing tables master:test.t6 and slave:test.t6
702
DROP FUNCTION f1_insert_triggered;
703
# Test case7: CALLS A FUNCTION which INVOKES A TRIGGER with after insert action
704
create table t1(a int) engine=innodb;
705
create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
706
create table t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
707
CREATE FUNCTION f1_two_inserts_trigger() RETURNS INTEGER
709
INSERT INTO t2(a) values(2),(3);
710
INSERT INTO t2(a) values(2),(3);
713
create trigger tr11 after insert on t2 for each row begin
714
insert into t3(a) values(new.a);
715
insert into t3(a) values(new.a);
718
insert into t1(a) values(f1_two_inserts_trigger());
719
insert into t2(a) values(4),(5);
721
insert into t1(a) values(f1_two_inserts_trigger());
722
# To verify if insert/update in an autoinc column causes statement to be logged in row format
723
show binlog events from <binlog_start>;
724
Log_name Pos Event_type Server_id End_log_pos Info
725
master-bin.000001 # Query # # BEGIN
726
master-bin.000001 # Table_map # # table_id: # (test.t2)
727
master-bin.000001 # Table_map # # table_id: # (test.t3)
728
master-bin.000001 # Write_rows # # table_id: #
729
master-bin.000001 # Write_rows # # table_id: #
730
master-bin.000001 # Write_rows # # table_id: #
731
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
732
master-bin.000001 # Xid # # COMMIT /* XID */
733
master-bin.000001 # Query # # BEGIN
734
master-bin.000001 # Table_map # # table_id: # (test.t1)
735
master-bin.000001 # Table_map # # table_id: # (test.t2)
736
master-bin.000001 # Table_map # # table_id: # (test.t3)
737
master-bin.000001 # Write_rows # # table_id: #
738
master-bin.000001 # Write_rows # # table_id: #
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: #
743
master-bin.000001 # Write_rows # # table_id: #
744
master-bin.000001 # Write_rows # # table_id: #
745
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
746
master-bin.000001 # Xid # # COMMIT /* XID */
747
master-bin.000001 # Query # # BEGIN
748
master-bin.000001 # Table_map # # table_id: # (test.t1)
749
master-bin.000001 # Table_map # # table_id: # (test.t2)
750
master-bin.000001 # Table_map # # table_id: # (test.t3)
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: #
757
master-bin.000001 # Write_rows # # table_id: #
758
master-bin.000001 # Write_rows # # table_id: #
759
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
760
master-bin.000001 # Xid # # COMMIT /* XID */
762
#Test if the results are consistent on master and slave
763
#for 'CALLS A FUNCTION which INVOKES A TRIGGER with after insert action'
764
Comparing tables master:test.t2 and slave:test.t2
765
Comparing tables master:test.t3 and slave:test.t3
769
drop function f1_two_inserts_trigger;
770
# Test case8: CALLS A FUNCTION which INVOKES A TRIGGER with before insert action
771
create table t1(a int) engine=innodb;
772
create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
773
create table t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
774
CREATE FUNCTION f1_two_inserts_trigger() RETURNS INTEGER
776
INSERT INTO t2(a) values(2),(3);
777
INSERT INTO t2(a) values(2),(3);
780
create trigger tr11 before insert on t2 for each row begin
781
insert into t3(a) values(new.a);
782
insert into t3(a) values(new.a);
785
insert into t1(a) values(f1_two_inserts_trigger());
786
insert into t2(a) values(4),(5);
788
insert into t1(a) values(f1_two_inserts_trigger());
789
# To verify if insert/update in an autoinc column causes statement to be logged in row format
790
show binlog events from <binlog_start>;
791
Log_name Pos Event_type Server_id End_log_pos Info
792
master-bin.000001 # Query # # BEGIN
793
master-bin.000001 # Table_map # # table_id: # (test.t2)
794
master-bin.000001 # Table_map # # table_id: # (test.t3)
795
master-bin.000001 # Write_rows # # table_id: #
796
master-bin.000001 # Write_rows # # table_id: #
797
master-bin.000001 # Write_rows # # table_id: #
798
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
799
master-bin.000001 # Xid # # COMMIT /* XID */
800
master-bin.000001 # Query # # BEGIN
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 # # table_id: #
805
master-bin.000001 # Write_rows # # table_id: #
806
master-bin.000001 # Write_rows # # table_id: #
807
master-bin.000001 # Write_rows # # table_id: #
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: #
812
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
813
master-bin.000001 # Xid # # COMMIT /* XID */
814
master-bin.000001 # Query # # BEGIN
815
master-bin.000001 # Table_map # # table_id: # (test.t1)
816
master-bin.000001 # Table_map # # table_id: # (test.t2)
817
master-bin.000001 # Table_map # # table_id: # (test.t3)
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: #
826
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
827
master-bin.000001 # Xid # # COMMIT /* XID */
829
#Test if the results are consistent on master and slave
830
#for 'CALLS A FUNCTION which INVOKES A TRIGGER with before insert action'
831
Comparing tables master:test.t2 and slave:test.t2
832
Comparing tables master:test.t3 and slave:test.t3
836
drop function f1_two_inserts_trigger;
837
# Test case9: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with after insert action
838
CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb;
839
CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb;
840
CREATE TABLE t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
841
create trigger tr16 after insert on t1 for each row insert into t3(a) values(new.c1);
842
create trigger tr17 after insert on t2 for each row insert into t3(a) values(new.c2);
844
INSERT INTO t1(c1) VALUES (11), (12);
845
INSERT INTO t2(c2) VALUES (13), (14);
846
CREATE VIEW v16 AS SELECT c1, c2 FROM t1, t2;
847
INSERT INTO v16(c1) VALUES (15),(16);
848
INSERT INTO v16(c2) VALUES (17),(18);
849
INSERT INTO v16(c1) VALUES (19),(20);
850
INSERT INTO v16(c2) VALUES (21),(22);
851
INSERT INTO v16(c1) VALUES (23), (24);
852
INSERT INTO v16(c1) VALUES (25), (26);
854
#Test if the results are consistent on master and slave
855
#for 'INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS'
856
Comparing tables master:test.t3 and slave:test.t3
861
# Test case10: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with before insert action
862
CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb;
863
CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb;
864
CREATE TABLE t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
865
create trigger tr16 before insert on t1 for each row insert into t3(a) values(new.c1);
866
create trigger tr17 before insert on t2 for each row insert into t3(a) values(new.c2);
868
INSERT INTO t1(c1) VALUES (11), (12);
869
INSERT INTO t2(c2) VALUES (13), (14);
870
CREATE VIEW v16 AS SELECT c1, c2 FROM t1, t2;
871
INSERT INTO v16(c1) VALUES (15),(16);
872
INSERT INTO v16(c2) VALUES (17),(18);
873
INSERT INTO v16(c1) VALUES (19),(20);
874
INSERT INTO v16(c2) VALUES (21),(22);
875
INSERT INTO v16(c1) VALUES (23), (24);
876
INSERT INTO v16(c1) VALUES (25), (26);
878
#Test if the results are consistent on master and slave
879
#for 'INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS'
880
Comparing tables master:test.t3 and slave:test.t3
885
# Test case11: INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES INTO A TABLE WITH AUTOINC COLUMN
886
create table t1(a int) engine=innodb;
887
create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb;
888
CREATE FUNCTION f1_two_inserts() RETURNS INTEGER
890
INSERT INTO t2(a) values(2);
891
INSERT INTO t2(a) values(2);
895
insert into t1(a) values(f1_two_inserts());
896
insert into t2(a) values(4),(5);
898
insert into t1(a) values(f1_two_inserts());
900
#Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on master
901
select * from t2 ORDER BY i1;
909
#Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on slave
910
select * from t2 ORDER BY i1;
920
drop function f1_two_inserts;
921
# Test case12: INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES OF A TABLE WITH AUTOINC COLUMN
922
create table t1(a int) engine=innodb;
923
create table t2(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb;
924
CREATE FUNCTION f1_two_updates() RETURNS INTEGER
926
update t2 set a = a + 5 where b = 1;
927
update t2 set a = a + 5 where b = 2;
928
update t2 set a = a + 5 where b = 3;
929
update t2 set a = a + 5 where b = 4;
932
insert into t2(a,b) values(1,1);
933
insert into t2(a,b) values(2,2);
934
insert into t2(a,b) values(3,3);
935
insert into t2(a,b) values(4,4);
936
insert into t1(a) values(f1_two_updates());
938
insert into t1(a) values(f1_two_updates());
940
#Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on master
941
select * from t2 ORDER BY i1;
947
#Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on slave
948
select * from t2 ORDER BY i1;
956
drop function f1_two_updates;
957
# Test case13: UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT
958
create table t1(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb;
959
create table t2(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb;
961
insert into t1(a,b) values(1,1),(2,2);
962
insert into t2(a,b) values(1,1),(2,2);
963
update t1,t2 set t1.a=t1.a+5, t2.a=t2.a+5 where t1.b=t2.b;
964
insert into t1(a,b) values(3,3);
965
insert into t2(a,b) values(3,3);
967
# To verify if it works fine when these statements are not be marked as unsafe
968
show binlog events from <binlog_start>;
969
Log_name Pos Event_type Server_id End_log_pos Info
970
master-bin.000001 # Query # # BEGIN
971
master-bin.000001 # Intvar # # INSERT_ID=1
972
master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,2)
973
master-bin.000001 # Intvar # # INSERT_ID=1
974
master-bin.000001 # Query # # use `test`; insert into t2(a,b) values(1,1),(2,2)
975
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
976
master-bin.000001 # Intvar # # INSERT_ID=3
977
master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(3,3)
978
master-bin.000001 # Intvar # # INSERT_ID=3
979
master-bin.000001 # Query # # use `test`; insert into t2(a,b) values(3,3)
980
master-bin.000001 # Xid # # COMMIT /* XID */
981
#Test if the results are consistent on master and slave
982
#for 'UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT'
983
Comparing tables master:test.t1 and slave:test.t1
984
Comparing tables master:test.t2 and slave:test.t2
987
# Test case14: INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES
988
CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb;
989
CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb;
991
INSERT INTO t1(c1) VALUES (11), (12);
992
INSERT INTO t2(c2) VALUES (13), (14);
993
CREATE VIEW v15 AS SELECT c1, c2 FROM t1, t2;
994
INSERT INTO v15(c1) VALUES (15),(16);
995
INSERT INTO v15(c2) VALUES (17),(18);
996
INSERT INTO v15(c1) VALUES (19),(20);
997
INSERT INTO v15(c2) VALUES (21),(22);
998
INSERT INTO v15(c1) VALUES (23), (24);
999
INSERT INTO v15(c2) VALUES (25), (26);
1001
# To verify if it works fine when these statements are not be marked as unsafe
1002
show binlog events from <binlog_start>;
1003
Log_name Pos Event_type Server_id End_log_pos Info
1004
master-bin.000001 # Query # # BEGIN
1005
master-bin.000001 # Intvar # # INSERT_ID=1
1006
master-bin.000001 # Query # # use `test`; INSERT INTO t1(c1) VALUES (11), (12)
1007
master-bin.000001 # Intvar # # INSERT_ID=1
1008
master-bin.000001 # Query # # use `test`; INSERT INTO t2(c2) VALUES (13), (14)
1009
master-bin.000001 # Xid # # COMMIT /* XID */
1010
master-bin.000001 # Query # # use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v15` AS SELECT c1, c2 FROM t1, t2
1011
master-bin.000001 # Query # # BEGIN
1012
master-bin.000001 # Intvar # # INSERT_ID=3
1013
master-bin.000001 # Query # # use `test`; INSERT INTO v15(c1) VALUES (15),(16)
1014
master-bin.000001 # Xid # # COMMIT /* XID */
1015
master-bin.000001 # Query # # BEGIN
1016
master-bin.000001 # Intvar # # INSERT_ID=3
1017
master-bin.000001 # Query # # use `test`; INSERT INTO v15(c2) VALUES (17),(18)
1018
master-bin.000001 # Xid # # COMMIT /* XID */
1019
master-bin.000001 # Query # # BEGIN
1020
master-bin.000001 # Intvar # # INSERT_ID=5
1021
master-bin.000001 # Query # # use `test`; INSERT INTO v15(c1) VALUES (19),(20)
1022
master-bin.000001 # Xid # # COMMIT /* XID */
1023
master-bin.000001 # Query # # BEGIN
1024
master-bin.000001 # Intvar # # INSERT_ID=5
1025
master-bin.000001 # Query # # use `test`; INSERT INTO v15(c2) VALUES (21),(22)
1026
master-bin.000001 # Xid # # COMMIT /* XID */
1027
master-bin.000001 # Query # # BEGIN
1028
master-bin.000001 # Intvar # # INSERT_ID=7
1029
master-bin.000001 # Query # # use `test`; INSERT INTO v15(c1) VALUES (23), (24)
1030
master-bin.000001 # Xid # # COMMIT /* XID */
1031
master-bin.000001 # Query # # BEGIN
1032
master-bin.000001 # Intvar # # INSERT_ID=7
1033
master-bin.000001 # Query # # use `test`; INSERT INTO v15(c2) VALUES (25), (26)
1034
master-bin.000001 # Xid # # COMMIT /* XID */
1035
#Test if the results are consistent on master and slave
1036
#for 'INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES'
1037
Comparing tables master:test.t1 and slave:test.t1
1038
Comparing tables master:test.t2 and slave:test.t2