Debian Codegraph @

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