Debian Codegraph @

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

raw
1 # BUG#33029 5.0 to 5.1 replication fails on dup key when inserting
2 # using a trig in SP
3
4 # For all 5.0 up to 5.0.58 exclusive, and 5.1 up to 5.1.12 exclusive,
5 # if one statement in a SP generated AUTO_INCREMENT value by the top
6 # statement, all statements after it would be considered generated
7 # AUTO_INCREMENT value by the top statement, and a erroneous INSERT_ID
8 # value might be associated with these statement, which could cause
9 # duplicate entry error and stop the slave.
10
11 source include/have_binlog_format_statement.inc;
12 source include/master-slave.inc;
13
14 CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
15 --connection slave
16 CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
17 --connection master
18
19 CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY);
20 CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY);
21
22 delimiter //;
23
24 CREATE PROCEDURE p1()
25 BEGIN
26 DECLARE ins_count INT DEFAULT 10;
27
28 WHILE ins_count > 0 DO
29 INSERT INTO t1 VALUES (NULL);
30 SET ins_count = ins_count - 1;
31 END WHILE;
32
33 DELETE FROM t1 WHERE id = 1;
34 DELETE FROM t1 WHERE id = 2;
35 DELETE FROM t2 WHERE id = 1;
36 DELETE FROM t2 WHERE id = 2;
37 END//
38
39 CREATE PROCEDURE p2()
40 BEGIN
41 INSERT INTO t1 VALUES (NULL);
42 DELETE FROM t1 WHERE id = f1(3);
43 DELETE FROM t1 WHERE id = f1(4);
44 DELETE FROM t2 WHERE id = 3;
45 DELETE FROM t2 WHERE id = 4;
46 END//
47
48 CREATE TRIGGER tr1 BEFORE DELETE
49 ON t1 FOR EACH ROW
50 BEGIN
51 INSERT INTO t2 VALUES (NULL);
52 END//
53
54 CREATE FUNCTION f1 (i int) RETURNS int
55 BEGIN
56 INSERT INTO t2 VALUES (NULL);
57 RETURN i;
58 END//
59
60 delimiter ;//
61
62 # the $binlog_start will be used by the show_binlog_events.inc, so
63 # that we can skip binlog events we don't care
64 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
65 CALL p1();
66 source include/show_binlog_events.inc;
67
68 echo # Result on master;
69 SELECT * FROM t1;
70 SELECT * FROM t2;
71
72 sync_slave_with_master;
73
74 SELECT * FROM t1;
75 SELECT * FROM t2;
76
77 connection master;
78
79 DROP TRIGGER tr1;
80
81 # the $binlog_start will be used by the show_binlog_events.inc, so
82 # that we can skip binlog events we don't care
83 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
84 CALL p2();
85 source include/show_binlog_events.inc;
86
87 echo # Result on master;
88 SELECT * FROM t1;
89 SELECT * FROM t2;
90
91 sync_slave_with_master;
92
93 SELECT * FROM t1;
94 SELECT * FROM t2;
95
96 # clean up
97 connection master;
98 disable_warnings;
99 DROP TABLE IF EXISTS t1, t2;
100 DROP PROCEDURE IF EXISTS p1;
101 DROP PROCEDURE IF EXISTS p2;
102 DROP FUNCTION IF EXISTS f1;
103 DROP TRIGGER IF EXISTS tr1;
104 enable_warnings;
105 sync_slave_with_master;
106 --source include/rpl_end.inc