Debian Codegraph @

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

raw
1 include/master-slave.inc
2 [connection master]
3 CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
4 connection slave;
5 CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
6 connection master;
7 CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY);
8 CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY);
9 CREATE PROCEDURE p1()
10 BEGIN
11 DECLARE ins_count INT DEFAULT 10;
12 WHILE ins_count > 0 DO
13 INSERT INTO t1 VALUES (NULL);
14 SET ins_count = ins_count - 1;
15 END WHILE;
16 DELETE FROM t1 WHERE id = 1;
17 DELETE FROM t1 WHERE id = 2;
18 DELETE FROM t2 WHERE id = 1;
19 DELETE FROM t2 WHERE id = 2;
20 END//
21 CREATE PROCEDURE p2()
22 BEGIN
23 INSERT INTO t1 VALUES (NULL);
24 DELETE FROM t1 WHERE id = f1(3);
25 DELETE FROM t1 WHERE id = f1(4);
26 DELETE FROM t2 WHERE id = 3;
27 DELETE FROM t2 WHERE id = 4;
28 END//
29 CREATE TRIGGER tr1 BEFORE DELETE
30 ON t1 FOR EACH ROW
31 BEGIN
32 INSERT INTO t2 VALUES (NULL);
33 END//
34 CREATE FUNCTION f1 (i int) RETURNS int
35 BEGIN
36 INSERT INTO t2 VALUES (NULL);
37 RETURN i;
38 END//
39 CALL p1();
40 Warnings:
41 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly
42 include/show_binlog_events.inc
43 Log_name Pos Event_type Server_id End_log_pos Info
44 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
45 master-bin.000001 # Intvar # # INSERT_ID=1
46 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL)
47 master-bin.000001 # Query # # COMMIT
48 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
49 master-bin.000001 # Intvar # # INSERT_ID=2
50 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL)
51 master-bin.000001 # Query # # COMMIT
52 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
53 master-bin.000001 # Intvar # # INSERT_ID=3
54 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL)
55 master-bin.000001 # Query # # COMMIT
56 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
57 master-bin.000001 # Intvar # # INSERT_ID=4
58 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL)
59 master-bin.000001 # Query # # COMMIT
60 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
61 master-bin.000001 # Intvar # # INSERT_ID=5
62 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL)
63 master-bin.000001 # Query # # COMMIT
64 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
65 master-bin.000001 # Intvar # # INSERT_ID=6
66 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL)
67 master-bin.000001 # Query # # COMMIT
68 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
69 master-bin.000001 # Intvar # # INSERT_ID=7
70 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL)
71 master-bin.000001 # Query # # COMMIT
72 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
73 master-bin.000001 # Intvar # # INSERT_ID=8
74 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL)
75 master-bin.000001 # Query # # COMMIT
76 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
77 master-bin.000001 # Intvar # # INSERT_ID=9
78 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL)
79 master-bin.000001 # Query # # COMMIT
80 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
81 master-bin.000001 # Intvar # # INSERT_ID=10
82 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL)
83 master-bin.000001 # Query # # COMMIT
84 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
85 master-bin.000001 # Intvar # # INSERT_ID=1
86 master-bin.000001 # Query # # use `test`; DELETE FROM t1 WHERE id = 1
87 master-bin.000001 # Query # # COMMIT
88 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
89 master-bin.000001 # Intvar # # INSERT_ID=2
90 master-bin.000001 # Query # # use `test`; DELETE FROM t1 WHERE id = 2
91 master-bin.000001 # Query # # COMMIT
92 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
93 master-bin.000001 # Query # # use `test`; DELETE FROM t2 WHERE id = 1
94 master-bin.000001 # Query # # COMMIT
95 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
96 master-bin.000001 # Query # # use `test`; DELETE FROM t2 WHERE id = 2
97 master-bin.000001 # Query # # COMMIT
98 # Result on master
99 SELECT * FROM t1;
100 id
101 3
102 4
103 5
104 6
105 7
106 8
107 9
108 10
109 SELECT * FROM t2;
110 id
111 connection slave;
112 SELECT * FROM t1;
113 id
114 3
115 4
116 5
117 6
118 7
119 8
120 9
121 10
122 SELECT * FROM t2;
123 id
124 connection master;
125 DROP TRIGGER tr1;
126 CALL p2();
127 Warnings:
128 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly
129 include/show_binlog_events.inc
130 Log_name Pos Event_type Server_id End_log_pos Info
131 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
132 master-bin.000001 # Intvar # # INSERT_ID=11
133 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (NULL)
134 master-bin.000001 # Query # # COMMIT
135 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
136 master-bin.000001 # Intvar # # INSERT_ID=3
137 master-bin.000001 # Query # # use `test`; DELETE FROM t1 WHERE id = f1(3)
138 master-bin.000001 # Query # # COMMIT
139 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
140 master-bin.000001 # Intvar # # INSERT_ID=12
141 master-bin.000001 # Query # # use `test`; DELETE FROM t1 WHERE id = f1(4)
142 master-bin.000001 # Query # # COMMIT
143 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
144 master-bin.000001 # Query # # use `test`; DELETE FROM t2 WHERE id = 3
145 master-bin.000001 # Query # # COMMIT
146 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
147 master-bin.000001 # Query # # use `test`; DELETE FROM t2 WHERE id = 4
148 master-bin.000001 # Query # # COMMIT
149 # Result on master
150 SELECT * FROM t1;
151 id
152 5
153 6
154 7
155 8
156 9
157 10
158 11
159 SELECT * FROM t2;
160 id
161 5
162 6
163 7
164 8
165 9
166 10
167 11
168 12
169 13
170 14
171 15
172 16
173 17
174 18
175 19
176 connection slave;
177 SELECT * FROM t1;
178 id
179 5
180 6
181 7
182 8
183 9
184 10
185 11
186 SELECT * FROM t2;
187 id
188 5
189 6
190 7
191 8
192 9
193 10
194 11
195 12
196 13
197 14
198 15
199 16
200 17
201 18
202 19
203 connection master;
204 DROP TABLE IF EXISTS t1, t2;
205 DROP PROCEDURE IF EXISTS p1;
206 DROP PROCEDURE IF EXISTS p2;
207 DROP FUNCTION IF EXISTS f1;
208 DROP TRIGGER IF EXISTS tr1;
209 connection slave;
210 include/rpl_end.inc