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/not_group_replication_plugin.inc
12 source include/have_binlog_format_statement.inc;
13 --source include/set_privilege_checks_user_as_system_user.inc
14 source include/master-slave.inc;
15
16 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.");
17
18 --disable_warnings
19 DROP TABLE IF EXISTS t1, t2;
20 DROP PROCEDURE IF EXISTS p1;
21 DROP PROCEDURE IF EXISTS p2;
22 DROP TRIGGER IF EXISTS tr1;
23 DROP FUNCTION IF EXISTS f1;
24 --enable_warnings
25
26 --source include/sync_slave_sql_with_master.inc
27 --connection master
28
29
30 CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY);
31 CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY);
32
33 delimiter //;
34
35 CREATE PROCEDURE p1()
36 BEGIN
37 DECLARE ins_count INT DEFAULT 10;
38
39 WHILE ins_count > 0 DO
40 INSERT INTO t1 VALUES (NULL);
41 SET ins_count = ins_count - 1;
42 END WHILE;
43
44 DELETE FROM t1 WHERE id = 1;
45 DELETE FROM t1 WHERE id = 2;
46 DELETE FROM t2 WHERE id = 1;
47 DELETE FROM t2 WHERE id = 2;
48 END//
49
50 CREATE PROCEDURE p2()
51 BEGIN
52 INSERT INTO t1 VALUES (NULL);
53 DELETE FROM t1 WHERE id = f1(3);
54 DELETE FROM t1 WHERE id = f1(4);
55 DELETE FROM t2 WHERE id = 3;
56 DELETE FROM t2 WHERE id = 4;
57 END//
58
59 CREATE TRIGGER tr1 BEFORE DELETE
60 ON t1 FOR EACH ROW
61 BEGIN
62 INSERT INTO t2 VALUES (NULL);
63 END//
64
65 CREATE FUNCTION f1 (i int) RETURNS int
66 BEGIN
67 INSERT INTO t2 VALUES (NULL);
68 RETURN i;
69 END//
70
71 delimiter ;//
72
73 # the $binlog_start will be used by the show_binlog_events.inc, so
74 # that we can skip binlog events we don't care
75 --let $mask_binlog_commit_events= 1
76 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
77 CALL p1();
78 source include/show_binlog_events.inc;
79 --let $mask_binlog_commit_events= 0
80
81 echo # Result on master;
82 SELECT * FROM t1;
83 SELECT * FROM t2;
84
85 --source include/sync_slave_sql_with_master.inc
86
87 echo # Result on slave;
88 SELECT * FROM t1;
89 SELECT * FROM t2;
90
91 connection master;
92
93 DROP TRIGGER tr1;
94
95 # the $binlog_start will be used by the show_binlog_events.inc, so
96 # that we can skip binlog events we don't care
97 --let $mask_binlog_commit_events= 1
98 let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
99 CALL p2();
100 source include/show_binlog_events.inc;
101 --let $mask_binlog_commit_events= 0
102
103 echo # Result on master;
104 SELECT * FROM t1;
105 SELECT * FROM t2;
106
107 --source include/sync_slave_sql_with_master.inc
108
109 echo # Result on slave;
110 SELECT * FROM t1;
111 SELECT * FROM t2;
112
113 # clean up
114 connection master;
115 disable_warnings;
116 DROP TABLE IF EXISTS t1, t2;
117 DROP PROCEDURE IF EXISTS p1;
118 DROP PROCEDURE IF EXISTS p2;
119 DROP FUNCTION IF EXISTS f1;
120 DROP TRIGGER IF EXISTS tr1;
121 enable_warnings;
122 --source include/sync_slave_sql_with_master.inc
123 --source include/rpl_end.inc