Debian Codegraph @

mysql-test/suite/binlog/r/binlog_unsafe.result

raw
1 #### Setup tables ####
2 CREATE TABLE t0 (a CHAR(200));
3 CREATE TABLE t1 (a CHAR(200));
4 CREATE TABLE t2 (a CHAR(200));
5 CREATE TABLE t3 (a CHAR(200));
6 CREATE TABLE ta0 (a CHAR(200));
7 CREATE TABLE ta1 (a CHAR(200));
8 CREATE TABLE ta2 (a CHAR(200));
9 CREATE TABLE ta3 (a CHAR(200));
10 CREATE TABLE autoinc_table (a INT PRIMARY KEY AUTO_INCREMENT);
11 CREATE TABLE data_table (a CHAR(200));
12 INSERT INTO data_table VALUES ('foo');
13 CREATE TABLE trigger_table_1 (a INT);
14 CREATE TABLE trigger_table_2 (a INT);
15 CREATE TABLE trigger_table_3 (a INT);
16 CREATE TABLE double_autoinc_table (a INT PRIMARY KEY AUTO_INCREMENT);
17 CREATE TRIGGER double_autoinc_trig
18 BEFORE INSERT ON double_autoinc_table FOR EACH ROW
19 BEGIN
20 INSERT INTO autoinc_table VALUES (NULL);
21 END|
22 CREATE FUNCTION multi_unsafe_func() RETURNS INT
23 BEGIN
24 INSERT INTO t0 VALUES(CONCAT(@@hostname, @@hostname));
25 INSERT INTO t0 VALUES(0);
26 INSERT INTO t0 VALUES(CONCAT(UUID(), @@hostname));
27 RETURN 1;
28 END|
29 CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
30
31 ==== Testing UUID() unsafeness ====
32
33 Invoking function func_retval_1 returning value from unsafe UUID() function.
34 CREATE FUNCTION func_retval_1() RETURNS VARCHAR(100) BEGIN INSERT INTO ta1 VALUES (47); RETURN UUID(); END;
35 * binlog_format = STATEMENT: expect 1 warnings.
36 INSERT INTO t1 VALUES (func_retval_1());
37 Warnings:
38 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
39 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
40 * binlog_format = MIXED: expect row events in binlog and no warning.
41 * Invoke statement so that return value is dicarded: expect no warning.
42 SELECT func_retval_1();
43
44 Invoking function func_retval_2 returning value from function func_retval_1 returning value from unsafe UUID() function.
45 CREATE FUNCTION func_retval_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); RETURN func_retval_1(); END;
46 * binlog_format = STATEMENT: expect 1 warnings.
47 INSERT INTO t2 VALUES (func_retval_2());
48 Warnings:
49 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
50 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
51 * binlog_format = MIXED: expect row events in binlog and no warning.
52 * Invoke statement so that return value is dicarded: expect no warning.
53 SELECT func_retval_2();
54 DROP FUNCTION func_retval_2;
55
56 Invoking function func_sidef_2 invoking function func_retval_1 returning value from unsafe UUID() function.
57 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 VALUES (func_retval_1()); RETURN 0; END;
58 * binlog_format = STATEMENT: expect 1 warnings.
59 INSERT INTO t2 SELECT func_sidef_2();
60 Warnings:
61 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
62 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
63 * binlog_format = MIXED: expect row events in binlog and no warning.
64 DROP FUNCTION func_sidef_2;
65
66 Invoking procedure proc_2 invoking function func_retval_1 returning value from unsafe UUID() function.
67 CREATE PROCEDURE proc_2() BEGIN INSERT INTO t1 VALUES (func_retval_1()); INSERT INTO ta2 VALUES (47); END;
68 * binlog_format = STATEMENT: expect 1 warnings.
69 CALL proc_2();
70 Warnings:
71 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
72 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
73 * binlog_format = MIXED: expect row events in binlog and no warning.
74 DROP PROCEDURE proc_2;
75
76 Invoking trigger trig_2 invoking function func_retval_1 returning value from unsafe UUID() function.
77 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 VALUES (func_retval_1()); END;
78 * binlog_format = STATEMENT: expect 1 warnings.
79 INSERT INTO trigger_table_2 VALUES (1);
80 Warnings:
81 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
82 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
83 * binlog_format = MIXED: expect row events in binlog and no warning.
84 DROP TRIGGER trig_2;
85
86 Invoking view view_retval_2 returning value from function func_retval_1 returning value from unsafe UUID() function.
87 CREATE VIEW view_retval_2 AS SELECT func_retval_1();
88 * binlog_format = STATEMENT: expect 1 warnings.
89 INSERT INTO t2 SELECT * FROM view_retval_2;
90 Warnings:
91 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
92 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
93 * binlog_format = MIXED: expect row events in binlog and no warning.
94 * Invoke statement so that return value is dicarded: expect no warning.
95 SELECT * FROM view_retval_2;
96 DROP VIEW view_retval_2;
97
98 Invoking prepared statement prep_2 invoking function func_retval_1 returning value from unsafe UUID() function.
99 PREPARE prep_2 FROM "INSERT INTO t1 VALUES (func_retval_1())";
100 * binlog_format = STATEMENT: expect 1 warnings.
101 EXECUTE prep_2;
102 Warnings:
103 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
104 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
105 * binlog_format = MIXED: expect row events in binlog and no warning.
106 DROP PREPARE prep_2;
107 DROP FUNCTION func_retval_1;
108
109 Invoking function func_sidef_1 invoking unsafe UUID() function.
110 CREATE FUNCTION func_sidef_1() RETURNS VARCHAR(100) BEGIN INSERT INTO ta1 VALUES (47); INSERT INTO t0 VALUES (UUID()); RETURN 0; END;
111 * binlog_format = STATEMENT: expect 1 warnings.
112 INSERT INTO t1 SELECT func_sidef_1();
113 Warnings:
114 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
115 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
116 * binlog_format = MIXED: expect row events in binlog and no warning.
117
118 Invoking function func_sidef_2 invoking function func_sidef_1 invoking unsafe UUID() function.
119 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); RETURN 0; END;
120 * binlog_format = STATEMENT: expect 1 warnings.
121 INSERT INTO t2 SELECT func_sidef_2();
122 Warnings:
123 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
124 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
125 * binlog_format = MIXED: expect row events in binlog and no warning.
126 DROP FUNCTION func_sidef_2;
127
128 Invoking procedure proc_2 invoking function func_sidef_1 invoking unsafe UUID() function.
129 CREATE PROCEDURE proc_2() BEGIN INSERT INTO t1 SELECT func_sidef_1(); INSERT INTO ta2 VALUES (47); END;
130 * binlog_format = STATEMENT: expect 1 warnings.
131 CALL proc_2();
132 Warnings:
133 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
134 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
135 * binlog_format = MIXED: expect row events in binlog and no warning.
136 DROP PROCEDURE proc_2;
137
138 Invoking trigger trig_2 invoking function func_sidef_1 invoking unsafe UUID() function.
139 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); END;
140 * binlog_format = STATEMENT: expect 1 warnings.
141 INSERT INTO trigger_table_2 VALUES (1);
142 Warnings:
143 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
144 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
145 * binlog_format = MIXED: expect row events in binlog and no warning.
146 DROP TRIGGER trig_2;
147
148 Invoking view view_sidef_2 invoking function func_sidef_1 invoking unsafe UUID() function.
149 CREATE VIEW view_sidef_2 AS SELECT func_sidef_1();
150 * binlog_format = STATEMENT: expect 1 warnings.
151 INSERT INTO t2 SELECT * FROM view_sidef_2;
152 Warnings:
153 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
154 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
155 * binlog_format = MIXED: expect row events in binlog and no warning.
156 DROP VIEW view_sidef_2;
157
158 Invoking prepared statement prep_2 invoking function func_sidef_1 invoking unsafe UUID() function.
159 PREPARE prep_2 FROM "INSERT INTO t1 SELECT func_sidef_1()";
160 * binlog_format = STATEMENT: expect 1 warnings.
161 EXECUTE prep_2;
162 Warnings:
163 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
164 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
165 * binlog_format = MIXED: expect row events in binlog and no warning.
166 DROP PREPARE prep_2;
167 DROP FUNCTION func_sidef_1;
168
169 Invoking procedure proc_1 invoking unsafe UUID() function.
170 CREATE PROCEDURE proc_1() BEGIN INSERT INTO t0 VALUES (UUID()); INSERT INTO ta1 VALUES (47); END;
171 * binlog_format = STATEMENT: expect 1 warnings.
172 CALL proc_1();
173 Warnings:
174 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
175 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
176 * binlog_format = MIXED: expect row events in binlog and no warning.
177
178 Invoking function func_sidef_2 invoking procedure proc_1 invoking unsafe UUID() function.
179 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); RETURN 0; END;
180 * binlog_format = STATEMENT: expect 1 warnings.
181 INSERT INTO t2 SELECT func_sidef_2();
182 Warnings:
183 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
184 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
185 * binlog_format = MIXED: expect row events in binlog and no warning.
186 DROP FUNCTION func_sidef_2;
187
188 Invoking procedure proc_2 invoking procedure proc_1 invoking unsafe UUID() function.
189 CREATE PROCEDURE proc_2() BEGIN CALL proc_1(); INSERT INTO ta2 VALUES (47); END;
190 * binlog_format = STATEMENT: expect 1 warnings.
191 CALL proc_2();
192 Warnings:
193 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
194 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
195 * binlog_format = MIXED: expect row events in binlog and no warning.
196 DROP PROCEDURE proc_2;
197
198 Invoking trigger trig_2 invoking procedure proc_1 invoking unsafe UUID() function.
199 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); END;
200 * binlog_format = STATEMENT: expect 1 warnings.
201 INSERT INTO trigger_table_2 VALUES (1);
202 Warnings:
203 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
204 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
205 * binlog_format = MIXED: expect row events in binlog and no warning.
206 DROP TRIGGER trig_2;
207
208 Invoking prepared statement prep_2 invoking procedure proc_1 invoking unsafe UUID() function.
209 PREPARE prep_2 FROM "CALL proc_1()";
210 * binlog_format = STATEMENT: expect 1 warnings.
211 EXECUTE prep_2;
212 Warnings:
213 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
214 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
215 * binlog_format = MIXED: expect row events in binlog and no warning.
216 DROP PREPARE prep_2;
217 DROP PROCEDURE proc_1;
218
219 Invoking trigger trig_1 invoking unsafe UUID() function.
220 CREATE TRIGGER trig_1 BEFORE INSERT ON trigger_table_1 FOR EACH ROW BEGIN INSERT INTO ta1 VALUES (47); INSERT INTO t0 VALUES (UUID()); END;
221 * binlog_format = STATEMENT: expect 1 warnings.
222 INSERT INTO trigger_table_1 VALUES (1);
223 Warnings:
224 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
225 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
226 * binlog_format = MIXED: expect row events in binlog and no warning.
227
228 Invoking function func_sidef_2 invoking trigger trig_1 invoking unsafe UUID() function.
229 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); RETURN 0; END;
230 * binlog_format = STATEMENT: expect 1 warnings.
231 INSERT INTO t2 SELECT func_sidef_2();
232 Warnings:
233 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
234 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
235 * binlog_format = MIXED: expect row events in binlog and no warning.
236 DROP FUNCTION func_sidef_2;
237
238 Invoking procedure proc_2 invoking trigger trig_1 invoking unsafe UUID() function.
239 CREATE PROCEDURE proc_2() BEGIN INSERT INTO trigger_table_1 VALUES (1); INSERT INTO ta2 VALUES (47); END;
240 * binlog_format = STATEMENT: expect 1 warnings.
241 CALL proc_2();
242 Warnings:
243 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
244 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
245 * binlog_format = MIXED: expect row events in binlog and no warning.
246 DROP PROCEDURE proc_2;
247
248 Invoking trigger trig_2 invoking trigger trig_1 invoking unsafe UUID() function.
249 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); END;
250 * binlog_format = STATEMENT: expect 1 warnings.
251 INSERT INTO trigger_table_2 VALUES (1);
252 Warnings:
253 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
254 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
255 * binlog_format = MIXED: expect row events in binlog and no warning.
256 DROP TRIGGER trig_2;
257
258 Invoking prepared statement prep_2 invoking trigger trig_1 invoking unsafe UUID() function.
259 PREPARE prep_2 FROM "INSERT INTO trigger_table_1 VALUES (1)";
260 * binlog_format = STATEMENT: expect 1 warnings.
261 EXECUTE prep_2;
262 Warnings:
263 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
264 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
265 * binlog_format = MIXED: expect row events in binlog and no warning.
266 DROP PREPARE prep_2;
267 DROP TRIGGER trig_1;
268
269 Invoking view view_retval_1 returning value from unsafe UUID() function.
270 CREATE VIEW view_retval_1 AS SELECT UUID();
271 * binlog_format = STATEMENT: expect 1 warnings.
272 INSERT INTO t1 SELECT * FROM view_retval_1;
273 Warnings:
274 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
275 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
276 * binlog_format = MIXED: expect row events in binlog and no warning.
277 * Invoke statement so that return value is dicarded: expect no warning.
278 SELECT * FROM view_retval_1;
279
280 Invoking function func_sidef_2 invoking view view_retval_1 returning value from unsafe UUID() function.
281 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT * FROM view_retval_1; RETURN 0; END;
282 * binlog_format = STATEMENT: expect 1 warnings.
283 INSERT INTO t2 SELECT func_sidef_2();
284 Warnings:
285 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
286 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
287 * binlog_format = MIXED: expect row events in binlog and no warning.
288 DROP FUNCTION func_sidef_2;
289
290 Invoking procedure proc_2 invoking view view_retval_1 returning value from unsafe UUID() function.
291 CREATE PROCEDURE proc_2() BEGIN INSERT INTO t1 SELECT * FROM view_retval_1; INSERT INTO ta2 VALUES (47); END;
292 * binlog_format = STATEMENT: expect 1 warnings.
293 CALL proc_2();
294 Warnings:
295 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
296 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
297 * binlog_format = MIXED: expect row events in binlog and no warning.
298 DROP PROCEDURE proc_2;
299
300 Invoking trigger trig_2 invoking view view_retval_1 returning value from unsafe UUID() function.
301 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT * FROM view_retval_1; END;
302 * binlog_format = STATEMENT: expect 1 warnings.
303 INSERT INTO trigger_table_2 VALUES (1);
304 Warnings:
305 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
306 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
307 * binlog_format = MIXED: expect row events in binlog and no warning.
308 DROP TRIGGER trig_2;
309
310 Invoking view view_retval_2 returning value from view view_retval_1 returning value from unsafe UUID() function.
311 CREATE VIEW view_retval_2 AS SELECT * FROM view_retval_1;
312 * binlog_format = STATEMENT: expect 1 warnings.
313 INSERT INTO t2 SELECT * FROM view_retval_2;
314 Warnings:
315 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
316 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
317 * binlog_format = MIXED: expect row events in binlog and no warning.
318 * Invoke statement so that return value is dicarded: expect no warning.
319 SELECT * FROM view_retval_2;
320 DROP VIEW view_retval_2;
321
322 Invoking prepared statement prep_2 invoking view view_retval_1 returning value from unsafe UUID() function.
323 PREPARE prep_2 FROM "INSERT INTO t1 SELECT * FROM view_retval_1";
324 * binlog_format = STATEMENT: expect 1 warnings.
325 EXECUTE prep_2;
326 Warnings:
327 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
328 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
329 * binlog_format = MIXED: expect row events in binlog and no warning.
330 DROP PREPARE prep_2;
331 DROP VIEW view_retval_1;
332
333 Invoking prepared statement prep_1 invoking unsafe UUID() function.
334 PREPARE prep_1 FROM "INSERT INTO t0 VALUES (UUID())";
335 * binlog_format = STATEMENT: expect 1 warnings.
336 EXECUTE prep_1;
337 Warnings:
338 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
339 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
340 * binlog_format = MIXED: expect row events in binlog and no warning.
341 DROP PREPARE prep_1;
342
343 Invoking unsafe UUID() function.
344 * binlog_format = STATEMENT: expect 1 warnings.
345 INSERT INTO t0 VALUES (UUID());
346 Warnings:
347 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
348 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
349 * binlog_format = MIXED: expect row events in binlog and no warning.
350 * Invoke statement so that return value is dicarded: expect no warning.
351 SELECT UUID();
352
353 ==== Testing @@hostname unsafeness ====
354
355 Invoking function func_retval_1 returning value from unsafe @@hostname variable.
356 CREATE FUNCTION func_retval_1() RETURNS VARCHAR(100) BEGIN INSERT INTO ta1 VALUES (47); RETURN @@hostname; END;
357 * binlog_format = STATEMENT: expect 1 warnings.
358 INSERT INTO t1 VALUES (func_retval_1());
359 Warnings:
360 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
361 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
362 * binlog_format = MIXED: expect row events in binlog and no warning.
363 * Invoke statement so that return value is dicarded: expect no warning.
364 SELECT func_retval_1();
365
366 Invoking function func_retval_2 returning value from function func_retval_1 returning value from unsafe @@hostname variable.
367 CREATE FUNCTION func_retval_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); RETURN func_retval_1(); END;
368 * binlog_format = STATEMENT: expect 1 warnings.
369 INSERT INTO t2 VALUES (func_retval_2());
370 Warnings:
371 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
372 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
373 * binlog_format = MIXED: expect row events in binlog and no warning.
374 * Invoke statement so that return value is dicarded: expect no warning.
375 SELECT func_retval_2();
376 DROP FUNCTION func_retval_2;
377
378 Invoking function func_sidef_2 invoking function func_retval_1 returning value from unsafe @@hostname variable.
379 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 VALUES (func_retval_1()); RETURN 0; END;
380 * binlog_format = STATEMENT: expect 1 warnings.
381 INSERT INTO t2 SELECT func_sidef_2();
382 Warnings:
383 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
384 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
385 * binlog_format = MIXED: expect row events in binlog and no warning.
386 DROP FUNCTION func_sidef_2;
387
388 Invoking procedure proc_2 invoking function func_retval_1 returning value from unsafe @@hostname variable.
389 CREATE PROCEDURE proc_2() BEGIN INSERT INTO t1 VALUES (func_retval_1()); INSERT INTO ta2 VALUES (47); END;
390 * binlog_format = STATEMENT: expect 1 warnings.
391 CALL proc_2();
392 Warnings:
393 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
394 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
395 * binlog_format = MIXED: expect row events in binlog and no warning.
396 DROP PROCEDURE proc_2;
397
398 Invoking trigger trig_2 invoking function func_retval_1 returning value from unsafe @@hostname variable.
399 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 VALUES (func_retval_1()); END;
400 * binlog_format = STATEMENT: expect 1 warnings.
401 INSERT INTO trigger_table_2 VALUES (1);
402 Warnings:
403 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
404 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
405 * binlog_format = MIXED: expect row events in binlog and no warning.
406 DROP TRIGGER trig_2;
407
408 Invoking view view_retval_2 returning value from function func_retval_1 returning value from unsafe @@hostname variable.
409 CREATE VIEW view_retval_2 AS SELECT func_retval_1();
410 * binlog_format = STATEMENT: expect 1 warnings.
411 INSERT INTO t2 SELECT * FROM view_retval_2;
412 Warnings:
413 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
414 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
415 * binlog_format = MIXED: expect row events in binlog and no warning.
416 * Invoke statement so that return value is dicarded: expect no warning.
417 SELECT * FROM view_retval_2;
418 DROP VIEW view_retval_2;
419
420 Invoking prepared statement prep_2 invoking function func_retval_1 returning value from unsafe @@hostname variable.
421 PREPARE prep_2 FROM "INSERT INTO t1 VALUES (func_retval_1())";
422 * binlog_format = STATEMENT: expect 1 warnings.
423 EXECUTE prep_2;
424 Warnings:
425 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
426 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
427 * binlog_format = MIXED: expect row events in binlog and no warning.
428 DROP PREPARE prep_2;
429 DROP FUNCTION func_retval_1;
430
431 Invoking function func_sidef_1 invoking unsafe @@hostname variable.
432 CREATE FUNCTION func_sidef_1() RETURNS VARCHAR(100) BEGIN INSERT INTO ta1 VALUES (47); INSERT INTO t0 VALUES (@@hostname); RETURN 0; END;
433 * binlog_format = STATEMENT: expect 1 warnings.
434 INSERT INTO t1 SELECT func_sidef_1();
435 Warnings:
436 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
437 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
438 * binlog_format = MIXED: expect row events in binlog and no warning.
439
440 Invoking function func_sidef_2 invoking function func_sidef_1 invoking unsafe @@hostname variable.
441 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); RETURN 0; END;
442 * binlog_format = STATEMENT: expect 1 warnings.
443 INSERT INTO t2 SELECT func_sidef_2();
444 Warnings:
445 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
446 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
447 * binlog_format = MIXED: expect row events in binlog and no warning.
448 DROP FUNCTION func_sidef_2;
449
450 Invoking procedure proc_2 invoking function func_sidef_1 invoking unsafe @@hostname variable.
451 CREATE PROCEDURE proc_2() BEGIN INSERT INTO t1 SELECT func_sidef_1(); INSERT INTO ta2 VALUES (47); END;
452 * binlog_format = STATEMENT: expect 1 warnings.
453 CALL proc_2();
454 Warnings:
455 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
456 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
457 * binlog_format = MIXED: expect row events in binlog and no warning.
458 DROP PROCEDURE proc_2;
459
460 Invoking trigger trig_2 invoking function func_sidef_1 invoking unsafe @@hostname variable.
461 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); END;
462 * binlog_format = STATEMENT: expect 1 warnings.
463 INSERT INTO trigger_table_2 VALUES (1);
464 Warnings:
465 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
466 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
467 * binlog_format = MIXED: expect row events in binlog and no warning.
468 DROP TRIGGER trig_2;
469
470 Invoking view view_sidef_2 invoking function func_sidef_1 invoking unsafe @@hostname variable.
471 CREATE VIEW view_sidef_2 AS SELECT func_sidef_1();
472 * binlog_format = STATEMENT: expect 1 warnings.
473 INSERT INTO t2 SELECT * FROM view_sidef_2;
474 Warnings:
475 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
476 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
477 * binlog_format = MIXED: expect row events in binlog and no warning.
478 DROP VIEW view_sidef_2;
479
480 Invoking prepared statement prep_2 invoking function func_sidef_1 invoking unsafe @@hostname variable.
481 PREPARE prep_2 FROM "INSERT INTO t1 SELECT func_sidef_1()";
482 * binlog_format = STATEMENT: expect 1 warnings.
483 EXECUTE prep_2;
484 Warnings:
485 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
486 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
487 * binlog_format = MIXED: expect row events in binlog and no warning.
488 DROP PREPARE prep_2;
489 DROP FUNCTION func_sidef_1;
490
491 Invoking procedure proc_1 invoking unsafe @@hostname variable.
492 CREATE PROCEDURE proc_1() BEGIN INSERT INTO t0 VALUES (@@hostname); INSERT INTO ta1 VALUES (47); END;
493 * binlog_format = STATEMENT: expect 1 warnings.
494 CALL proc_1();
495 Warnings:
496 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
497 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
498 * binlog_format = MIXED: expect row events in binlog and no warning.
499
500 Invoking function func_sidef_2 invoking procedure proc_1 invoking unsafe @@hostname variable.
501 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); RETURN 0; END;
502 * binlog_format = STATEMENT: expect 1 warnings.
503 INSERT INTO t2 SELECT func_sidef_2();
504 Warnings:
505 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
506 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
507 * binlog_format = MIXED: expect row events in binlog and no warning.
508 DROP FUNCTION func_sidef_2;
509
510 Invoking procedure proc_2 invoking procedure proc_1 invoking unsafe @@hostname variable.
511 CREATE PROCEDURE proc_2() BEGIN CALL proc_1(); INSERT INTO ta2 VALUES (47); END;
512 * binlog_format = STATEMENT: expect 1 warnings.
513 CALL proc_2();
514 Warnings:
515 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
516 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
517 * binlog_format = MIXED: expect row events in binlog and no warning.
518 DROP PROCEDURE proc_2;
519
520 Invoking trigger trig_2 invoking procedure proc_1 invoking unsafe @@hostname variable.
521 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); END;
522 * binlog_format = STATEMENT: expect 1 warnings.
523 INSERT INTO trigger_table_2 VALUES (1);
524 Warnings:
525 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
526 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
527 * binlog_format = MIXED: expect row events in binlog and no warning.
528 DROP TRIGGER trig_2;
529
530 Invoking prepared statement prep_2 invoking procedure proc_1 invoking unsafe @@hostname variable.
531 PREPARE prep_2 FROM "CALL proc_1()";
532 * binlog_format = STATEMENT: expect 1 warnings.
533 EXECUTE prep_2;
534 Warnings:
535 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
536 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
537 * binlog_format = MIXED: expect row events in binlog and no warning.
538 DROP PREPARE prep_2;
539 DROP PROCEDURE proc_1;
540
541 Invoking trigger trig_1 invoking unsafe @@hostname variable.
542 CREATE TRIGGER trig_1 BEFORE INSERT ON trigger_table_1 FOR EACH ROW BEGIN INSERT INTO ta1 VALUES (47); INSERT INTO t0 VALUES (@@hostname); END;
543 * binlog_format = STATEMENT: expect 1 warnings.
544 INSERT INTO trigger_table_1 VALUES (1);
545 Warnings:
546 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
547 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
548 * binlog_format = MIXED: expect row events in binlog and no warning.
549
550 Invoking function func_sidef_2 invoking trigger trig_1 invoking unsafe @@hostname variable.
551 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); RETURN 0; END;
552 * binlog_format = STATEMENT: expect 1 warnings.
553 INSERT INTO t2 SELECT func_sidef_2();
554 Warnings:
555 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
556 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
557 * binlog_format = MIXED: expect row events in binlog and no warning.
558 DROP FUNCTION func_sidef_2;
559
560 Invoking procedure proc_2 invoking trigger trig_1 invoking unsafe @@hostname variable.
561 CREATE PROCEDURE proc_2() BEGIN INSERT INTO trigger_table_1 VALUES (1); INSERT INTO ta2 VALUES (47); END;
562 * binlog_format = STATEMENT: expect 1 warnings.
563 CALL proc_2();
564 Warnings:
565 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
566 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
567 * binlog_format = MIXED: expect row events in binlog and no warning.
568 DROP PROCEDURE proc_2;
569
570 Invoking trigger trig_2 invoking trigger trig_1 invoking unsafe @@hostname variable.
571 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); END;
572 * binlog_format = STATEMENT: expect 1 warnings.
573 INSERT INTO trigger_table_2 VALUES (1);
574 Warnings:
575 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
576 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
577 * binlog_format = MIXED: expect row events in binlog and no warning.
578 DROP TRIGGER trig_2;
579
580 Invoking prepared statement prep_2 invoking trigger trig_1 invoking unsafe @@hostname variable.
581 PREPARE prep_2 FROM "INSERT INTO trigger_table_1 VALUES (1)";
582 * binlog_format = STATEMENT: expect 1 warnings.
583 EXECUTE prep_2;
584 Warnings:
585 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
586 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
587 * binlog_format = MIXED: expect row events in binlog and no warning.
588 DROP PREPARE prep_2;
589 DROP TRIGGER trig_1;
590
591 Invoking prepared statement prep_1 invoking unsafe @@hostname variable.
592 PREPARE prep_1 FROM "INSERT INTO t0 VALUES (@@hostname)";
593 * binlog_format = STATEMENT: expect 1 warnings.
594 EXECUTE prep_1;
595 Warnings:
596 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
597 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
598 * binlog_format = MIXED: expect row events in binlog and no warning.
599 DROP PREPARE prep_1;
600
601 Invoking unsafe @@hostname variable.
602 * binlog_format = STATEMENT: expect 1 warnings.
603 INSERT INTO t0 VALUES (@@hostname);
604 Warnings:
605 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
606 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
607 * binlog_format = MIXED: expect row events in binlog and no warning.
608
609 ==== Testing SELECT...LIMIT unsafeness ====
610
611 Invoking function func_sidef_1 invoking unsafe SELECT...LIMIT statement.
612 CREATE FUNCTION func_sidef_1() RETURNS VARCHAR(100) BEGIN INSERT INTO ta1 VALUES (47); INSERT INTO t0 SELECT * FROM data_table LIMIT 1; RETURN 0; END;
613 * binlog_format = STATEMENT: expect 1 warnings.
614 INSERT INTO t1 SELECT func_sidef_1();
615 Warnings:
616 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
617 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
618 * binlog_format = MIXED: expect row events in binlog and no warning.
619
620 Invoking function func_sidef_2 invoking function func_sidef_1 invoking unsafe SELECT...LIMIT statement.
621 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); RETURN 0; END;
622 * binlog_format = STATEMENT: expect 1 warnings.
623 INSERT INTO t2 SELECT func_sidef_2();
624 Warnings:
625 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
626 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
627 * binlog_format = MIXED: expect row events in binlog and no warning.
628 DROP FUNCTION func_sidef_2;
629
630 Invoking procedure proc_2 invoking function func_sidef_1 invoking unsafe SELECT...LIMIT statement.
631 CREATE PROCEDURE proc_2() BEGIN INSERT INTO t1 SELECT func_sidef_1(); INSERT INTO ta2 VALUES (47); END;
632 * binlog_format = STATEMENT: expect 1 warnings.
633 CALL proc_2();
634 Warnings:
635 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
636 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
637 * binlog_format = MIXED: expect row events in binlog and no warning.
638 DROP PROCEDURE proc_2;
639
640 Invoking trigger trig_2 invoking function func_sidef_1 invoking unsafe SELECT...LIMIT statement.
641 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); END;
642 * binlog_format = STATEMENT: expect 1 warnings.
643 INSERT INTO trigger_table_2 VALUES (1);
644 Warnings:
645 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
646 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
647 * binlog_format = MIXED: expect row events in binlog and no warning.
648 DROP TRIGGER trig_2;
649
650 Invoking view view_sidef_2 invoking function func_sidef_1 invoking unsafe SELECT...LIMIT statement.
651 CREATE VIEW view_sidef_2 AS SELECT func_sidef_1();
652 * binlog_format = STATEMENT: expect 1 warnings.
653 INSERT INTO t2 SELECT * FROM view_sidef_2;
654 Warnings:
655 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
656 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
657 * binlog_format = MIXED: expect row events in binlog and no warning.
658 DROP VIEW view_sidef_2;
659
660 Invoking prepared statement prep_2 invoking function func_sidef_1 invoking unsafe SELECT...LIMIT statement.
661 PREPARE prep_2 FROM "INSERT INTO t1 SELECT func_sidef_1()";
662 * binlog_format = STATEMENT: expect 1 warnings.
663 EXECUTE prep_2;
664 Warnings:
665 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
666 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
667 * binlog_format = MIXED: expect row events in binlog and no warning.
668 DROP PREPARE prep_2;
669 DROP FUNCTION func_sidef_1;
670
671 Invoking procedure proc_1 invoking unsafe SELECT...LIMIT statement.
672 CREATE PROCEDURE proc_1() BEGIN INSERT INTO t0 SELECT * FROM data_table LIMIT 1; INSERT INTO ta1 VALUES (47); END;
673 * binlog_format = STATEMENT: expect 1 warnings.
674 CALL proc_1();
675 Warnings:
676 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
677 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
678 * binlog_format = MIXED: expect row events in binlog and no warning.
679
680 Invoking function func_sidef_2 invoking procedure proc_1 invoking unsafe SELECT...LIMIT statement.
681 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); RETURN 0; END;
682 * binlog_format = STATEMENT: expect 1 warnings.
683 INSERT INTO t2 SELECT func_sidef_2();
684 Warnings:
685 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
686 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
687 * binlog_format = MIXED: expect row events in binlog and no warning.
688 DROP FUNCTION func_sidef_2;
689
690 Invoking procedure proc_2 invoking procedure proc_1 invoking unsafe SELECT...LIMIT statement.
691 CREATE PROCEDURE proc_2() BEGIN CALL proc_1(); INSERT INTO ta2 VALUES (47); END;
692 * binlog_format = STATEMENT: expect 1 warnings.
693 CALL proc_2();
694 Warnings:
695 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
696 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
697 * binlog_format = MIXED: expect row events in binlog and no warning.
698 DROP PROCEDURE proc_2;
699
700 Invoking trigger trig_2 invoking procedure proc_1 invoking unsafe SELECT...LIMIT statement.
701 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); END;
702 * binlog_format = STATEMENT: expect 1 warnings.
703 INSERT INTO trigger_table_2 VALUES (1);
704 Warnings:
705 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
706 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
707 * binlog_format = MIXED: expect row events in binlog and no warning.
708 DROP TRIGGER trig_2;
709
710 Invoking prepared statement prep_2 invoking procedure proc_1 invoking unsafe SELECT...LIMIT statement.
711 PREPARE prep_2 FROM "CALL proc_1()";
712 * binlog_format = STATEMENT: expect 1 warnings.
713 EXECUTE prep_2;
714 Warnings:
715 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
716 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
717 * binlog_format = MIXED: expect row events in binlog and no warning.
718 DROP PREPARE prep_2;
719 DROP PROCEDURE proc_1;
720
721 Invoking trigger trig_1 invoking unsafe SELECT...LIMIT statement.
722 CREATE TRIGGER trig_1 BEFORE INSERT ON trigger_table_1 FOR EACH ROW BEGIN INSERT INTO ta1 VALUES (47); INSERT INTO t0 SELECT * FROM data_table LIMIT 1; END;
723 * binlog_format = STATEMENT: expect 1 warnings.
724 INSERT INTO trigger_table_1 VALUES (1);
725 Warnings:
726 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
727 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
728 * binlog_format = MIXED: expect row events in binlog and no warning.
729
730 Invoking function func_sidef_2 invoking trigger trig_1 invoking unsafe SELECT...LIMIT statement.
731 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); RETURN 0; END;
732 * binlog_format = STATEMENT: expect 1 warnings.
733 INSERT INTO t2 SELECT func_sidef_2();
734 Warnings:
735 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
736 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
737 * binlog_format = MIXED: expect row events in binlog and no warning.
738 DROP FUNCTION func_sidef_2;
739
740 Invoking procedure proc_2 invoking trigger trig_1 invoking unsafe SELECT...LIMIT statement.
741 CREATE PROCEDURE proc_2() BEGIN INSERT INTO trigger_table_1 VALUES (1); INSERT INTO ta2 VALUES (47); END;
742 * binlog_format = STATEMENT: expect 1 warnings.
743 CALL proc_2();
744 Warnings:
745 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
746 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
747 * binlog_format = MIXED: expect row events in binlog and no warning.
748 DROP PROCEDURE proc_2;
749
750 Invoking trigger trig_2 invoking trigger trig_1 invoking unsafe SELECT...LIMIT statement.
751 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); END;
752 * binlog_format = STATEMENT: expect 1 warnings.
753 INSERT INTO trigger_table_2 VALUES (1);
754 Warnings:
755 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
756 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
757 * binlog_format = MIXED: expect row events in binlog and no warning.
758 DROP TRIGGER trig_2;
759
760 Invoking prepared statement prep_2 invoking trigger trig_1 invoking unsafe SELECT...LIMIT statement.
761 PREPARE prep_2 FROM "INSERT INTO trigger_table_1 VALUES (1)";
762 * binlog_format = STATEMENT: expect 1 warnings.
763 EXECUTE prep_2;
764 Warnings:
765 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
766 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
767 * binlog_format = MIXED: expect row events in binlog and no warning.
768 DROP PREPARE prep_2;
769 DROP TRIGGER trig_1;
770
771 Invoking view view_retval_1 returning value from unsafe SELECT...LIMIT statement.
772 CREATE VIEW view_retval_1 AS SELECT * FROM data_table LIMIT 1;
773 * binlog_format = STATEMENT: expect 1 warnings.
774 INSERT INTO t1 SELECT * FROM view_retval_1;
775 Warnings:
776 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
777 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
778 * binlog_format = MIXED: expect row events in binlog and no warning.
779 * Invoke statement so that return value is dicarded: expect no warning.
780 SELECT * FROM view_retval_1;
781
782 Invoking function func_sidef_2 invoking view view_retval_1 returning value from unsafe SELECT...LIMIT statement.
783 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT * FROM view_retval_1; RETURN 0; END;
784 * binlog_format = STATEMENT: expect 1 warnings.
785 INSERT INTO t2 SELECT func_sidef_2();
786 Warnings:
787 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
788 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
789 * binlog_format = MIXED: expect row events in binlog and no warning.
790 DROP FUNCTION func_sidef_2;
791
792 Invoking procedure proc_2 invoking view view_retval_1 returning value from unsafe SELECT...LIMIT statement.
793 CREATE PROCEDURE proc_2() BEGIN INSERT INTO t1 SELECT * FROM view_retval_1; INSERT INTO ta2 VALUES (47); END;
794 * binlog_format = STATEMENT: expect 1 warnings.
795 CALL proc_2();
796 Warnings:
797 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
798 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
799 * binlog_format = MIXED: expect row events in binlog and no warning.
800 DROP PROCEDURE proc_2;
801
802 Invoking trigger trig_2 invoking view view_retval_1 returning value from unsafe SELECT...LIMIT statement.
803 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT * FROM view_retval_1; END;
804 * binlog_format = STATEMENT: expect 1 warnings.
805 INSERT INTO trigger_table_2 VALUES (1);
806 Warnings:
807 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
808 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
809 * binlog_format = MIXED: expect row events in binlog and no warning.
810 DROP TRIGGER trig_2;
811
812 Invoking view view_retval_2 returning value from view view_retval_1 returning value from unsafe SELECT...LIMIT statement.
813 CREATE VIEW view_retval_2 AS SELECT * FROM view_retval_1;
814 * binlog_format = STATEMENT: expect 1 warnings.
815 INSERT INTO t2 SELECT * FROM view_retval_2;
816 Warnings:
817 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
818 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
819 * binlog_format = MIXED: expect row events in binlog and no warning.
820 * Invoke statement so that return value is dicarded: expect no warning.
821 SELECT * FROM view_retval_2;
822 DROP VIEW view_retval_2;
823
824 Invoking prepared statement prep_2 invoking view view_retval_1 returning value from unsafe SELECT...LIMIT statement.
825 PREPARE prep_2 FROM "INSERT INTO t1 SELECT * FROM view_retval_1";
826 * binlog_format = STATEMENT: expect 1 warnings.
827 EXECUTE prep_2;
828 Warnings:
829 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
830 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
831 * binlog_format = MIXED: expect row events in binlog and no warning.
832 DROP PREPARE prep_2;
833 DROP VIEW view_retval_1;
834
835 Invoking prepared statement prep_1 invoking unsafe SELECT...LIMIT statement.
836 PREPARE prep_1 FROM "INSERT INTO t0 SELECT * FROM data_table LIMIT 1";
837 * binlog_format = STATEMENT: expect 1 warnings.
838 EXECUTE prep_1;
839 Warnings:
840 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
841 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
842 * binlog_format = MIXED: expect row events in binlog and no warning.
843 DROP PREPARE prep_1;
844
845 Invoking unsafe SELECT...LIMIT statement.
846 * binlog_format = STATEMENT: expect 1 warnings.
847 INSERT INTO t0 SELECT * FROM data_table LIMIT 1;
848 Warnings:
849 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
850 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
851 * binlog_format = MIXED: expect row events in binlog and no warning.
852 * Invoke statement so that return value is dicarded: expect no warning.
853 SELECT * FROM data_table LIMIT 1;
854
855 ==== Testing INSERT DELAYED safeness after BUG#54579 is fixed ====
856
857 Invoking function func_sidef_1 invoking unsafe INSERT DELAYED statement.
858 CREATE FUNCTION func_sidef_1() RETURNS VARCHAR(100) BEGIN INSERT INTO ta1 VALUES (47); INSERT DELAYED INTO t0 VALUES (1), (2); RETURN 0; END;
859 * binlog_format = STATEMENT: expect 0 warnings.
860 INSERT INTO t1 SELECT func_sidef_1();
861 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
862 * binlog_format = MIXED: expect row events in binlog and no warning.
863
864 Invoking function func_sidef_2 invoking function func_sidef_1 invoking unsafe INSERT DELAYED statement.
865 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); RETURN 0; END;
866 * binlog_format = STATEMENT: expect 0 warnings.
867 INSERT INTO t2 SELECT func_sidef_2();
868 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
869 * binlog_format = MIXED: expect row events in binlog and no warning.
870 DROP FUNCTION func_sidef_2;
871
872 Invoking procedure proc_2 invoking function func_sidef_1 invoking unsafe INSERT DELAYED statement.
873 CREATE PROCEDURE proc_2() BEGIN INSERT INTO t1 SELECT func_sidef_1(); INSERT INTO ta2 VALUES (47); END;
874 * binlog_format = STATEMENT: expect 0 warnings.
875 CALL proc_2();
876 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
877 * binlog_format = MIXED: expect row events in binlog and no warning.
878 DROP PROCEDURE proc_2;
879
880 Invoking trigger trig_2 invoking function func_sidef_1 invoking unsafe INSERT DELAYED statement.
881 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); END;
882 * binlog_format = STATEMENT: expect 0 warnings.
883 INSERT INTO trigger_table_2 VALUES (1);
884 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
885 * binlog_format = MIXED: expect row events in binlog and no warning.
886 DROP TRIGGER trig_2;
887
888 Invoking view view_sidef_2 invoking function func_sidef_1 invoking unsafe INSERT DELAYED statement.
889 CREATE VIEW view_sidef_2 AS SELECT func_sidef_1();
890 * binlog_format = STATEMENT: expect 0 warnings.
891 INSERT INTO t2 SELECT * FROM view_sidef_2;
892 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
893 * binlog_format = MIXED: expect row events in binlog and no warning.
894 DROP VIEW view_sidef_2;
895
896 Invoking prepared statement prep_2 invoking function func_sidef_1 invoking unsafe INSERT DELAYED statement.
897 PREPARE prep_2 FROM "INSERT INTO t1 SELECT func_sidef_1()";
898 * binlog_format = STATEMENT: expect 0 warnings.
899 EXECUTE prep_2;
900 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
901 * binlog_format = MIXED: expect row events in binlog and no warning.
902 DROP PREPARE prep_2;
903 DROP FUNCTION func_sidef_1;
904
905 Invoking procedure proc_1 invoking unsafe INSERT DELAYED statement.
906 CREATE PROCEDURE proc_1() BEGIN INSERT DELAYED INTO t0 VALUES (1), (2); INSERT INTO ta1 VALUES (47); END;
907 * binlog_format = STATEMENT: expect 0 warnings.
908 CALL proc_1();
909 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
910 * binlog_format = MIXED: expect row events in binlog and no warning.
911
912 Invoking function func_sidef_2 invoking procedure proc_1 invoking unsafe INSERT DELAYED statement.
913 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); RETURN 0; END;
914 * binlog_format = STATEMENT: expect 0 warnings.
915 INSERT INTO t2 SELECT func_sidef_2();
916 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
917 * binlog_format = MIXED: expect row events in binlog and no warning.
918 DROP FUNCTION func_sidef_2;
919
920 Invoking procedure proc_2 invoking procedure proc_1 invoking unsafe INSERT DELAYED statement.
921 CREATE PROCEDURE proc_2() BEGIN CALL proc_1(); INSERT INTO ta2 VALUES (47); END;
922 * binlog_format = STATEMENT: expect 0 warnings.
923 CALL proc_2();
924 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
925 * binlog_format = MIXED: expect row events in binlog and no warning.
926 DROP PROCEDURE proc_2;
927
928 Invoking trigger trig_2 invoking procedure proc_1 invoking unsafe INSERT DELAYED statement.
929 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); END;
930 * binlog_format = STATEMENT: expect 0 warnings.
931 INSERT INTO trigger_table_2 VALUES (1);
932 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
933 * binlog_format = MIXED: expect row events in binlog and no warning.
934 DROP TRIGGER trig_2;
935
936 Invoking prepared statement prep_2 invoking procedure proc_1 invoking unsafe INSERT DELAYED statement.
937 PREPARE prep_2 FROM "CALL proc_1()";
938 * binlog_format = STATEMENT: expect 0 warnings.
939 EXECUTE prep_2;
940 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
941 * binlog_format = MIXED: expect row events in binlog and no warning.
942 DROP PREPARE prep_2;
943 DROP PROCEDURE proc_1;
944
945 Invoking trigger trig_1 invoking unsafe INSERT DELAYED statement.
946 CREATE TRIGGER trig_1 BEFORE INSERT ON trigger_table_1 FOR EACH ROW BEGIN INSERT INTO ta1 VALUES (47); INSERT DELAYED INTO t0 VALUES (1), (2); END;
947 * binlog_format = STATEMENT: expect 0 warnings.
948 INSERT INTO trigger_table_1 VALUES (1);
949 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
950 * binlog_format = MIXED: expect row events in binlog and no warning.
951
952 Invoking function func_sidef_2 invoking trigger trig_1 invoking unsafe INSERT DELAYED statement.
953 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); RETURN 0; END;
954 * binlog_format = STATEMENT: expect 0 warnings.
955 INSERT INTO t2 SELECT func_sidef_2();
956 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
957 * binlog_format = MIXED: expect row events in binlog and no warning.
958 DROP FUNCTION func_sidef_2;
959
960 Invoking procedure proc_2 invoking trigger trig_1 invoking unsafe INSERT DELAYED statement.
961 CREATE PROCEDURE proc_2() BEGIN INSERT INTO trigger_table_1 VALUES (1); INSERT INTO ta2 VALUES (47); END;
962 * binlog_format = STATEMENT: expect 0 warnings.
963 CALL proc_2();
964 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
965 * binlog_format = MIXED: expect row events in binlog and no warning.
966 DROP PROCEDURE proc_2;
967
968 Invoking trigger trig_2 invoking trigger trig_1 invoking unsafe INSERT DELAYED statement.
969 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); END;
970 * binlog_format = STATEMENT: expect 0 warnings.
971 INSERT INTO trigger_table_2 VALUES (1);
972 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
973 * binlog_format = MIXED: expect row events in binlog and no warning.
974 DROP TRIGGER trig_2;
975
976 Invoking prepared statement prep_2 invoking trigger trig_1 invoking unsafe INSERT DELAYED statement.
977 PREPARE prep_2 FROM "INSERT INTO trigger_table_1 VALUES (1)";
978 * binlog_format = STATEMENT: expect 0 warnings.
979 EXECUTE prep_2;
980 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
981 * binlog_format = MIXED: expect row events in binlog and no warning.
982 DROP PREPARE prep_2;
983 DROP TRIGGER trig_1;
984
985 Invoking prepared statement prep_1 invoking unsafe INSERT DELAYED statement.
986 PREPARE prep_1 FROM "INSERT DELAYED INTO t0 VALUES (1), (2)";
987 * binlog_format = STATEMENT: expect 0 warnings.
988 EXECUTE prep_1;
989 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
990 * binlog_format = MIXED: expect row events in binlog and no warning.
991 DROP PREPARE prep_1;
992
993 Invoking unsafe INSERT DELAYED statement.
994 * binlog_format = STATEMENT: expect 0 warnings.
995 INSERT DELAYED INTO t0 VALUES (1), (2);
996 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
997 * binlog_format = MIXED: expect row events in binlog and no warning.
998
999 ==== Testing unsafeness of insert of two autoinc values ====
1000
1001 Invoking function func_sidef_1 invoking unsafe update of two autoinc columns.
1002 CREATE FUNCTION func_sidef_1() RETURNS VARCHAR(100) BEGIN INSERT INTO ta1 VALUES (47); INSERT INTO double_autoinc_table VALUES (NULL); RETURN 0; END;
1003 * binlog_format = STATEMENT: expect 1 warnings.
1004 INSERT INTO t1 SELECT func_sidef_1();
1005 Warnings:
1006 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
1007 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1008 * binlog_format = MIXED: expect row events in binlog and no warning.
1009
1010 Invoking function func_sidef_2 invoking function func_sidef_1 invoking unsafe update of two autoinc columns.
1011 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); RETURN 0; END;
1012 * binlog_format = STATEMENT: expect 1 warnings.
1013 INSERT INTO t2 SELECT func_sidef_2();
1014 Warnings:
1015 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
1016 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1017 * binlog_format = MIXED: expect row events in binlog and no warning.
1018 DROP FUNCTION func_sidef_2;
1019
1020 Invoking procedure proc_2 invoking function func_sidef_1 invoking unsafe update of two autoinc columns.
1021 CREATE PROCEDURE proc_2() BEGIN INSERT INTO t1 SELECT func_sidef_1(); INSERT INTO ta2 VALUES (47); END;
1022 * binlog_format = STATEMENT: expect 1 warnings.
1023 CALL proc_2();
1024 Warnings:
1025 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
1026 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1027 * binlog_format = MIXED: expect row events in binlog and no warning.
1028 DROP PROCEDURE proc_2;
1029
1030 Invoking trigger trig_2 invoking function func_sidef_1 invoking unsafe update of two autoinc columns.
1031 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); END;
1032 * binlog_format = STATEMENT: expect 1 warnings.
1033 INSERT INTO trigger_table_2 VALUES (1);
1034 Warnings:
1035 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
1036 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1037 * binlog_format = MIXED: expect row events in binlog and no warning.
1038 DROP TRIGGER trig_2;
1039
1040 Invoking view view_sidef_2 invoking function func_sidef_1 invoking unsafe update of two autoinc columns.
1041 CREATE VIEW view_sidef_2 AS SELECT func_sidef_1();
1042 * binlog_format = STATEMENT: expect 1 warnings.
1043 INSERT INTO t2 SELECT * FROM view_sidef_2;
1044 Warnings:
1045 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
1046 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1047 * binlog_format = MIXED: expect row events in binlog and no warning.
1048 DROP VIEW view_sidef_2;
1049
1050 Invoking prepared statement prep_2 invoking function func_sidef_1 invoking unsafe update of two autoinc columns.
1051 PREPARE prep_2 FROM "INSERT INTO t1 SELECT func_sidef_1()";
1052 * binlog_format = STATEMENT: expect 1 warnings.
1053 EXECUTE prep_2;
1054 Warnings:
1055 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
1056 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1057 * binlog_format = MIXED: expect row events in binlog and no warning.
1058 DROP PREPARE prep_2;
1059 DROP FUNCTION func_sidef_1;
1060
1061 Invoking procedure proc_1 invoking unsafe update of two autoinc columns.
1062 CREATE PROCEDURE proc_1() BEGIN INSERT INTO double_autoinc_table VALUES (NULL); INSERT INTO ta1 VALUES (47); END;
1063 * binlog_format = STATEMENT: expect 1 warnings.
1064 CALL proc_1();
1065 Warnings:
1066 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
1067 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1068 * binlog_format = MIXED: expect row events in binlog and no warning.
1069
1070 Invoking function func_sidef_2 invoking procedure proc_1 invoking unsafe update of two autoinc columns.
1071 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); RETURN 0; END;
1072 * binlog_format = STATEMENT: expect 1 warnings.
1073 INSERT INTO t2 SELECT func_sidef_2();
1074 Warnings:
1075 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
1076 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1077 * binlog_format = MIXED: expect row events in binlog and no warning.
1078 DROP FUNCTION func_sidef_2;
1079
1080 Invoking procedure proc_2 invoking procedure proc_1 invoking unsafe update of two autoinc columns.
1081 CREATE PROCEDURE proc_2() BEGIN CALL proc_1(); INSERT INTO ta2 VALUES (47); END;
1082 * binlog_format = STATEMENT: expect 1 warnings.
1083 CALL proc_2();
1084 Warnings:
1085 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
1086 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1087 * binlog_format = MIXED: expect row events in binlog and no warning.
1088 DROP PROCEDURE proc_2;
1089
1090 Invoking trigger trig_2 invoking procedure proc_1 invoking unsafe update of two autoinc columns.
1091 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); END;
1092 * binlog_format = STATEMENT: expect 1 warnings.
1093 INSERT INTO trigger_table_2 VALUES (1);
1094 Warnings:
1095 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
1096 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1097 * binlog_format = MIXED: expect row events in binlog and no warning.
1098 DROP TRIGGER trig_2;
1099
1100 Invoking prepared statement prep_2 invoking procedure proc_1 invoking unsafe update of two autoinc columns.
1101 PREPARE prep_2 FROM "CALL proc_1()";
1102 * binlog_format = STATEMENT: expect 1 warnings.
1103 EXECUTE prep_2;
1104 Warnings:
1105 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
1106 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1107 * binlog_format = MIXED: expect row events in binlog and no warning.
1108 DROP PREPARE prep_2;
1109 DROP PROCEDURE proc_1;
1110
1111 Invoking trigger trig_1 invoking unsafe update of two autoinc columns.
1112 CREATE TRIGGER trig_1 BEFORE INSERT ON trigger_table_1 FOR EACH ROW BEGIN INSERT INTO ta1 VALUES (47); INSERT INTO double_autoinc_table VALUES (NULL); END;
1113 * binlog_format = STATEMENT: expect 1 warnings.
1114 INSERT INTO trigger_table_1 VALUES (1);
1115 Warnings:
1116 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
1117 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1118 * binlog_format = MIXED: expect row events in binlog and no warning.
1119
1120 Invoking function func_sidef_2 invoking trigger trig_1 invoking unsafe update of two autoinc columns.
1121 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); RETURN 0; END;
1122 * binlog_format = STATEMENT: expect 1 warnings.
1123 INSERT INTO t2 SELECT func_sidef_2();
1124 Warnings:
1125 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
1126 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1127 * binlog_format = MIXED: expect row events in binlog and no warning.
1128 DROP FUNCTION func_sidef_2;
1129
1130 Invoking procedure proc_2 invoking trigger trig_1 invoking unsafe update of two autoinc columns.
1131 CREATE PROCEDURE proc_2() BEGIN INSERT INTO trigger_table_1 VALUES (1); INSERT INTO ta2 VALUES (47); END;
1132 * binlog_format = STATEMENT: expect 1 warnings.
1133 CALL proc_2();
1134 Warnings:
1135 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
1136 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1137 * binlog_format = MIXED: expect row events in binlog and no warning.
1138 DROP PROCEDURE proc_2;
1139
1140 Invoking trigger trig_2 invoking trigger trig_1 invoking unsafe update of two autoinc columns.
1141 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); END;
1142 * binlog_format = STATEMENT: expect 1 warnings.
1143 INSERT INTO trigger_table_2 VALUES (1);
1144 Warnings:
1145 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
1146 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1147 * binlog_format = MIXED: expect row events in binlog and no warning.
1148 DROP TRIGGER trig_2;
1149
1150 Invoking prepared statement prep_2 invoking trigger trig_1 invoking unsafe update of two autoinc columns.
1151 PREPARE prep_2 FROM "INSERT INTO trigger_table_1 VALUES (1)";
1152 * binlog_format = STATEMENT: expect 1 warnings.
1153 EXECUTE prep_2;
1154 Warnings:
1155 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
1156 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1157 * binlog_format = MIXED: expect row events in binlog and no warning.
1158 DROP PREPARE prep_2;
1159 DROP TRIGGER trig_1;
1160
1161 Invoking prepared statement prep_1 invoking unsafe update of two autoinc columns.
1162 PREPARE prep_1 FROM "INSERT INTO double_autoinc_table VALUES (NULL)";
1163 * binlog_format = STATEMENT: expect 1 warnings.
1164 EXECUTE prep_1;
1165 Warnings:
1166 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
1167 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1168 * binlog_format = MIXED: expect row events in binlog and no warning.
1169 DROP PREPARE prep_1;
1170
1171 Invoking unsafe update of two autoinc columns.
1172 * binlog_format = STATEMENT: expect 1 warnings.
1173 INSERT INTO double_autoinc_table VALUES (NULL);
1174 Warnings:
1175 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
1176 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1177 * binlog_format = MIXED: expect row events in binlog and no warning.
1178
1179 ==== Testing unsafeness of UDF's ====
1180
1181 Invoking function func_retval_1 returning value from unsafe UDF.
1182 CREATE FUNCTION func_retval_1() RETURNS VARCHAR(100) BEGIN INSERT INTO ta1 VALUES (47); RETURN myfunc_int(10); END;
1183 * binlog_format = STATEMENT: expect 1 warnings.
1184 INSERT INTO t1 VALUES (func_retval_1());
1185 Warnings:
1186 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1187 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1188 * binlog_format = MIXED: expect row events in binlog and no warning.
1189 * Invoke statement so that return value is dicarded: expect no warning.
1190 SELECT func_retval_1();
1191
1192 Invoking function func_retval_2 returning value from function func_retval_1 returning value from unsafe UDF.
1193 CREATE FUNCTION func_retval_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); RETURN func_retval_1(); END;
1194 * binlog_format = STATEMENT: expect 1 warnings.
1195 INSERT INTO t2 VALUES (func_retval_2());
1196 Warnings:
1197 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1198 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1199 * binlog_format = MIXED: expect row events in binlog and no warning.
1200 * Invoke statement so that return value is dicarded: expect no warning.
1201 SELECT func_retval_2();
1202 DROP FUNCTION func_retval_2;
1203
1204 Invoking function func_sidef_2 invoking function func_retval_1 returning value from unsafe UDF.
1205 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 VALUES (func_retval_1()); RETURN 0; END;
1206 * binlog_format = STATEMENT: expect 1 warnings.
1207 INSERT INTO t2 SELECT func_sidef_2();
1208 Warnings:
1209 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1210 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1211 * binlog_format = MIXED: expect row events in binlog and no warning.
1212 DROP FUNCTION func_sidef_2;
1213
1214 Invoking procedure proc_2 invoking function func_retval_1 returning value from unsafe UDF.
1215 CREATE PROCEDURE proc_2() BEGIN INSERT INTO t1 VALUES (func_retval_1()); INSERT INTO ta2 VALUES (47); END;
1216 * binlog_format = STATEMENT: expect 1 warnings.
1217 CALL proc_2();
1218 Warnings:
1219 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1220 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1221 * binlog_format = MIXED: expect row events in binlog and no warning.
1222 DROP PROCEDURE proc_2;
1223
1224 Invoking trigger trig_2 invoking function func_retval_1 returning value from unsafe UDF.
1225 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 VALUES (func_retval_1()); END;
1226 * binlog_format = STATEMENT: expect 1 warnings.
1227 INSERT INTO trigger_table_2 VALUES (1);
1228 Warnings:
1229 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1230 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1231 * binlog_format = MIXED: expect row events in binlog and no warning.
1232 DROP TRIGGER trig_2;
1233
1234 Invoking view view_retval_2 returning value from function func_retval_1 returning value from unsafe UDF.
1235 CREATE VIEW view_retval_2 AS SELECT func_retval_1();
1236 * binlog_format = STATEMENT: expect 1 warnings.
1237 INSERT INTO t2 SELECT * FROM view_retval_2;
1238 Warnings:
1239 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1240 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1241 * binlog_format = MIXED: expect row events in binlog and no warning.
1242 * Invoke statement so that return value is dicarded: expect no warning.
1243 SELECT * FROM view_retval_2;
1244 DROP VIEW view_retval_2;
1245
1246 Invoking prepared statement prep_2 invoking function func_retval_1 returning value from unsafe UDF.
1247 PREPARE prep_2 FROM "INSERT INTO t1 VALUES (func_retval_1())";
1248 * binlog_format = STATEMENT: expect 1 warnings.
1249 EXECUTE prep_2;
1250 Warnings:
1251 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1252 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1253 * binlog_format = MIXED: expect row events in binlog and no warning.
1254 DROP PREPARE prep_2;
1255 DROP FUNCTION func_retval_1;
1256
1257 Invoking function func_sidef_1 invoking unsafe UDF.
1258 CREATE FUNCTION func_sidef_1() RETURNS VARCHAR(100) BEGIN INSERT INTO ta1 VALUES (47); INSERT INTO t0 VALUES (myfunc_int(10)); RETURN 0; END;
1259 * binlog_format = STATEMENT: expect 1 warnings.
1260 INSERT INTO t1 SELECT func_sidef_1();
1261 Warnings:
1262 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1263 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1264 * binlog_format = MIXED: expect row events in binlog and no warning.
1265
1266 Invoking function func_sidef_2 invoking function func_sidef_1 invoking unsafe UDF.
1267 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); RETURN 0; END;
1268 * binlog_format = STATEMENT: expect 1 warnings.
1269 INSERT INTO t2 SELECT func_sidef_2();
1270 Warnings:
1271 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1272 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1273 * binlog_format = MIXED: expect row events in binlog and no warning.
1274 DROP FUNCTION func_sidef_2;
1275
1276 Invoking procedure proc_2 invoking function func_sidef_1 invoking unsafe UDF.
1277 CREATE PROCEDURE proc_2() BEGIN INSERT INTO t1 SELECT func_sidef_1(); INSERT INTO ta2 VALUES (47); END;
1278 * binlog_format = STATEMENT: expect 1 warnings.
1279 CALL proc_2();
1280 Warnings:
1281 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1282 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1283 * binlog_format = MIXED: expect row events in binlog and no warning.
1284 DROP PROCEDURE proc_2;
1285
1286 Invoking trigger trig_2 invoking function func_sidef_1 invoking unsafe UDF.
1287 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); END;
1288 * binlog_format = STATEMENT: expect 1 warnings.
1289 INSERT INTO trigger_table_2 VALUES (1);
1290 Warnings:
1291 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1292 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1293 * binlog_format = MIXED: expect row events in binlog and no warning.
1294 DROP TRIGGER trig_2;
1295
1296 Invoking view view_sidef_2 invoking function func_sidef_1 invoking unsafe UDF.
1297 CREATE VIEW view_sidef_2 AS SELECT func_sidef_1();
1298 * binlog_format = STATEMENT: expect 1 warnings.
1299 INSERT INTO t2 SELECT * FROM view_sidef_2;
1300 Warnings:
1301 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1302 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1303 * binlog_format = MIXED: expect row events in binlog and no warning.
1304 DROP VIEW view_sidef_2;
1305
1306 Invoking prepared statement prep_2 invoking function func_sidef_1 invoking unsafe UDF.
1307 PREPARE prep_2 FROM "INSERT INTO t1 SELECT func_sidef_1()";
1308 * binlog_format = STATEMENT: expect 1 warnings.
1309 EXECUTE prep_2;
1310 Warnings:
1311 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1312 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1313 * binlog_format = MIXED: expect row events in binlog and no warning.
1314 DROP PREPARE prep_2;
1315 DROP FUNCTION func_sidef_1;
1316
1317 Invoking procedure proc_1 invoking unsafe UDF.
1318 CREATE PROCEDURE proc_1() BEGIN INSERT INTO t0 VALUES (myfunc_int(10)); INSERT INTO ta1 VALUES (47); END;
1319 * binlog_format = STATEMENT: expect 1 warnings.
1320 CALL proc_1();
1321 Warnings:
1322 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1323 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1324 * binlog_format = MIXED: expect row events in binlog and no warning.
1325
1326 Invoking function func_sidef_2 invoking procedure proc_1 invoking unsafe UDF.
1327 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); RETURN 0; END;
1328 * binlog_format = STATEMENT: expect 1 warnings.
1329 INSERT INTO t2 SELECT func_sidef_2();
1330 Warnings:
1331 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1332 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1333 * binlog_format = MIXED: expect row events in binlog and no warning.
1334 DROP FUNCTION func_sidef_2;
1335
1336 Invoking procedure proc_2 invoking procedure proc_1 invoking unsafe UDF.
1337 CREATE PROCEDURE proc_2() BEGIN CALL proc_1(); INSERT INTO ta2 VALUES (47); END;
1338 * binlog_format = STATEMENT: expect 1 warnings.
1339 CALL proc_2();
1340 Warnings:
1341 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1342 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1343 * binlog_format = MIXED: expect row events in binlog and no warning.
1344 DROP PROCEDURE proc_2;
1345
1346 Invoking trigger trig_2 invoking procedure proc_1 invoking unsafe UDF.
1347 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); END;
1348 * binlog_format = STATEMENT: expect 1 warnings.
1349 INSERT INTO trigger_table_2 VALUES (1);
1350 Warnings:
1351 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1352 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1353 * binlog_format = MIXED: expect row events in binlog and no warning.
1354 DROP TRIGGER trig_2;
1355
1356 Invoking prepared statement prep_2 invoking procedure proc_1 invoking unsafe UDF.
1357 PREPARE prep_2 FROM "CALL proc_1()";
1358 * binlog_format = STATEMENT: expect 1 warnings.
1359 EXECUTE prep_2;
1360 Warnings:
1361 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1362 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1363 * binlog_format = MIXED: expect row events in binlog and no warning.
1364 DROP PREPARE prep_2;
1365 DROP PROCEDURE proc_1;
1366
1367 Invoking trigger trig_1 invoking unsafe UDF.
1368 CREATE TRIGGER trig_1 BEFORE INSERT ON trigger_table_1 FOR EACH ROW BEGIN INSERT INTO ta1 VALUES (47); INSERT INTO t0 VALUES (myfunc_int(10)); END;
1369 * binlog_format = STATEMENT: expect 1 warnings.
1370 INSERT INTO trigger_table_1 VALUES (1);
1371 Warnings:
1372 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1373 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1374 * binlog_format = MIXED: expect row events in binlog and no warning.
1375
1376 Invoking function func_sidef_2 invoking trigger trig_1 invoking unsafe UDF.
1377 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); RETURN 0; END;
1378 * binlog_format = STATEMENT: expect 1 warnings.
1379 INSERT INTO t2 SELECT func_sidef_2();
1380 Warnings:
1381 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1382 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1383 * binlog_format = MIXED: expect row events in binlog and no warning.
1384 DROP FUNCTION func_sidef_2;
1385
1386 Invoking procedure proc_2 invoking trigger trig_1 invoking unsafe UDF.
1387 CREATE PROCEDURE proc_2() BEGIN INSERT INTO trigger_table_1 VALUES (1); INSERT INTO ta2 VALUES (47); END;
1388 * binlog_format = STATEMENT: expect 1 warnings.
1389 CALL proc_2();
1390 Warnings:
1391 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1392 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1393 * binlog_format = MIXED: expect row events in binlog and no warning.
1394 DROP PROCEDURE proc_2;
1395
1396 Invoking trigger trig_2 invoking trigger trig_1 invoking unsafe UDF.
1397 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); END;
1398 * binlog_format = STATEMENT: expect 1 warnings.
1399 INSERT INTO trigger_table_2 VALUES (1);
1400 Warnings:
1401 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1402 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1403 * binlog_format = MIXED: expect row events in binlog and no warning.
1404 DROP TRIGGER trig_2;
1405
1406 Invoking prepared statement prep_2 invoking trigger trig_1 invoking unsafe UDF.
1407 PREPARE prep_2 FROM "INSERT INTO trigger_table_1 VALUES (1)";
1408 * binlog_format = STATEMENT: expect 1 warnings.
1409 EXECUTE prep_2;
1410 Warnings:
1411 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1412 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1413 * binlog_format = MIXED: expect row events in binlog and no warning.
1414 DROP PREPARE prep_2;
1415 DROP TRIGGER trig_1;
1416
1417 Invoking view view_sidef_1 invoking unsafe UDF.
1418 CREATE VIEW view_sidef_1 AS SELECT myfunc_int(10);
1419 * binlog_format = STATEMENT: expect 1 warnings.
1420 INSERT INTO t1 SELECT * FROM view_sidef_1;
1421 Warnings:
1422 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1423 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1424 * binlog_format = MIXED: expect row events in binlog and no warning.
1425
1426 Invoking function func_sidef_2 invoking view view_sidef_1 invoking unsafe UDF.
1427 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT * FROM view_sidef_1; RETURN 0; END;
1428 * binlog_format = STATEMENT: expect 1 warnings.
1429 INSERT INTO t2 SELECT func_sidef_2();
1430 Warnings:
1431 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1432 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1433 * binlog_format = MIXED: expect row events in binlog and no warning.
1434 DROP FUNCTION func_sidef_2;
1435
1436 Invoking procedure proc_2 invoking view view_sidef_1 invoking unsafe UDF.
1437 CREATE PROCEDURE proc_2() BEGIN INSERT INTO t1 SELECT * FROM view_sidef_1; INSERT INTO ta2 VALUES (47); END;
1438 * binlog_format = STATEMENT: expect 1 warnings.
1439 CALL proc_2();
1440 Warnings:
1441 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1442 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1443 * binlog_format = MIXED: expect row events in binlog and no warning.
1444 DROP PROCEDURE proc_2;
1445
1446 Invoking trigger trig_2 invoking view view_sidef_1 invoking unsafe UDF.
1447 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT * FROM view_sidef_1; END;
1448 * binlog_format = STATEMENT: expect 1 warnings.
1449 INSERT INTO trigger_table_2 VALUES (1);
1450 Warnings:
1451 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1452 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1453 * binlog_format = MIXED: expect row events in binlog and no warning.
1454 DROP TRIGGER trig_2;
1455
1456 Invoking view view_sidef_2 invoking view view_sidef_1 invoking unsafe UDF.
1457 CREATE VIEW view_sidef_2 AS SELECT * FROM view_sidef_1;
1458 * binlog_format = STATEMENT: expect 1 warnings.
1459 INSERT INTO t2 SELECT * FROM view_sidef_2;
1460 Warnings:
1461 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1462 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1463 * binlog_format = MIXED: expect row events in binlog and no warning.
1464 DROP VIEW view_sidef_2;
1465
1466 Invoking prepared statement prep_2 invoking view view_sidef_1 invoking unsafe UDF.
1467 PREPARE prep_2 FROM "INSERT INTO t1 SELECT * FROM view_sidef_1";
1468 * binlog_format = STATEMENT: expect 1 warnings.
1469 EXECUTE prep_2;
1470 Warnings:
1471 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1472 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1473 * binlog_format = MIXED: expect row events in binlog and no warning.
1474 DROP PREPARE prep_2;
1475 DROP VIEW view_sidef_1;
1476
1477 Invoking prepared statement prep_1 invoking unsafe UDF.
1478 PREPARE prep_1 FROM "INSERT INTO t0 VALUES (myfunc_int(10))";
1479 * binlog_format = STATEMENT: expect 1 warnings.
1480 EXECUTE prep_1;
1481 Warnings:
1482 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1483 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1484 * binlog_format = MIXED: expect row events in binlog and no warning.
1485 DROP PREPARE prep_1;
1486
1487 Invoking unsafe UDF.
1488 * binlog_format = STATEMENT: expect 1 warnings.
1489 INSERT INTO t0 VALUES (myfunc_int(10));
1490 Warnings:
1491 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1492 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1493 * binlog_format = MIXED: expect row events in binlog and no warning.
1494
1495 ==== Testing unsafeness of access to mysql.general_log ====
1496
1497 Invoking function func_sidef_1 invoking unsafe use of mysql.general_log.
1498 CREATE FUNCTION func_sidef_1() RETURNS VARCHAR(100) BEGIN INSERT INTO ta1 VALUES (47); INSERT INTO t0 SELECT COUNT(*) FROM mysql.general_log; RETURN 0; END;
1499 * binlog_format = STATEMENT: expect 1 warnings.
1500 INSERT INTO t1 SELECT func_sidef_1();
1501 Warnings:
1502 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1503 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1504 * binlog_format = MIXED: expect row events in binlog and no warning.
1505
1506 Invoking function func_sidef_2 invoking function func_sidef_1 invoking unsafe use of mysql.general_log.
1507 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); RETURN 0; END;
1508 * binlog_format = STATEMENT: expect 1 warnings.
1509 INSERT INTO t2 SELECT func_sidef_2();
1510 Warnings:
1511 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1512 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1513 * binlog_format = MIXED: expect row events in binlog and no warning.
1514 DROP FUNCTION func_sidef_2;
1515
1516 Invoking procedure proc_2 invoking function func_sidef_1 invoking unsafe use of mysql.general_log.
1517 CREATE PROCEDURE proc_2() BEGIN INSERT INTO t1 SELECT func_sidef_1(); INSERT INTO ta2 VALUES (47); END;
1518 * binlog_format = STATEMENT: expect 1 warnings.
1519 CALL proc_2();
1520 Warnings:
1521 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1522 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1523 * binlog_format = MIXED: expect row events in binlog and no warning.
1524 DROP PROCEDURE proc_2;
1525
1526 Invoking trigger trig_2 invoking function func_sidef_1 invoking unsafe use of mysql.general_log.
1527 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); END;
1528 * binlog_format = STATEMENT: expect 1 warnings.
1529 INSERT INTO trigger_table_2 VALUES (1);
1530 Warnings:
1531 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1532 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1533 * binlog_format = MIXED: expect row events in binlog and no warning.
1534 DROP TRIGGER trig_2;
1535
1536 Invoking view view_sidef_2 invoking function func_sidef_1 invoking unsafe use of mysql.general_log.
1537 CREATE VIEW view_sidef_2 AS SELECT func_sidef_1();
1538 * binlog_format = STATEMENT: expect 1 warnings.
1539 INSERT INTO t2 SELECT * FROM view_sidef_2;
1540 Warnings:
1541 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1542 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1543 * binlog_format = MIXED: expect row events in binlog and no warning.
1544 DROP VIEW view_sidef_2;
1545
1546 Invoking prepared statement prep_2 invoking function func_sidef_1 invoking unsafe use of mysql.general_log.
1547 PREPARE prep_2 FROM "INSERT INTO t1 SELECT func_sidef_1()";
1548 * binlog_format = STATEMENT: expect 1 warnings.
1549 EXECUTE prep_2;
1550 Warnings:
1551 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1552 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1553 * binlog_format = MIXED: expect row events in binlog and no warning.
1554 DROP PREPARE prep_2;
1555 DROP FUNCTION func_sidef_1;
1556
1557 Invoking procedure proc_1 invoking unsafe use of mysql.general_log.
1558 CREATE PROCEDURE proc_1() BEGIN INSERT INTO t0 SELECT COUNT(*) FROM mysql.general_log; INSERT INTO ta1 VALUES (47); END;
1559 * binlog_format = STATEMENT: expect 1 warnings.
1560 CALL proc_1();
1561 Warnings:
1562 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1563 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1564 * binlog_format = MIXED: expect row events in binlog and no warning.
1565
1566 Invoking function func_sidef_2 invoking procedure proc_1 invoking unsafe use of mysql.general_log.
1567 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); RETURN 0; END;
1568 * binlog_format = STATEMENT: expect 1 warnings.
1569 INSERT INTO t2 SELECT func_sidef_2();
1570 Warnings:
1571 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1572 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1573 * binlog_format = MIXED: expect row events in binlog and no warning.
1574 DROP FUNCTION func_sidef_2;
1575
1576 Invoking procedure proc_2 invoking procedure proc_1 invoking unsafe use of mysql.general_log.
1577 CREATE PROCEDURE proc_2() BEGIN CALL proc_1(); INSERT INTO ta2 VALUES (47); END;
1578 * binlog_format = STATEMENT: expect 1 warnings.
1579 CALL proc_2();
1580 Warnings:
1581 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1582 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1583 * binlog_format = MIXED: expect row events in binlog and no warning.
1584 DROP PROCEDURE proc_2;
1585
1586 Invoking trigger trig_2 invoking procedure proc_1 invoking unsafe use of mysql.general_log.
1587 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); END;
1588 * binlog_format = STATEMENT: expect 1 warnings.
1589 INSERT INTO trigger_table_2 VALUES (1);
1590 Warnings:
1591 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1592 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1593 * binlog_format = MIXED: expect row events in binlog and no warning.
1594 DROP TRIGGER trig_2;
1595
1596 Invoking prepared statement prep_2 invoking procedure proc_1 invoking unsafe use of mysql.general_log.
1597 PREPARE prep_2 FROM "CALL proc_1()";
1598 * binlog_format = STATEMENT: expect 1 warnings.
1599 EXECUTE prep_2;
1600 Warnings:
1601 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1602 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1603 * binlog_format = MIXED: expect row events in binlog and no warning.
1604 DROP PREPARE prep_2;
1605 DROP PROCEDURE proc_1;
1606
1607 Invoking trigger trig_1 invoking unsafe use of mysql.general_log.
1608 CREATE TRIGGER trig_1 BEFORE INSERT ON trigger_table_1 FOR EACH ROW BEGIN INSERT INTO ta1 VALUES (47); INSERT INTO t0 SELECT COUNT(*) FROM mysql.general_log; END;
1609 * binlog_format = STATEMENT: expect 1 warnings.
1610 INSERT INTO trigger_table_1 VALUES (1);
1611 Warnings:
1612 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1613 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1614 * binlog_format = MIXED: expect row events in binlog and no warning.
1615
1616 Invoking function func_sidef_2 invoking trigger trig_1 invoking unsafe use of mysql.general_log.
1617 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); RETURN 0; END;
1618 * binlog_format = STATEMENT: expect 1 warnings.
1619 INSERT INTO t2 SELECT func_sidef_2();
1620 Warnings:
1621 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1622 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1623 * binlog_format = MIXED: expect row events in binlog and no warning.
1624 DROP FUNCTION func_sidef_2;
1625
1626 Invoking procedure proc_2 invoking trigger trig_1 invoking unsafe use of mysql.general_log.
1627 CREATE PROCEDURE proc_2() BEGIN INSERT INTO trigger_table_1 VALUES (1); INSERT INTO ta2 VALUES (47); END;
1628 * binlog_format = STATEMENT: expect 1 warnings.
1629 CALL proc_2();
1630 Warnings:
1631 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1632 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1633 * binlog_format = MIXED: expect row events in binlog and no warning.
1634 DROP PROCEDURE proc_2;
1635
1636 Invoking trigger trig_2 invoking trigger trig_1 invoking unsafe use of mysql.general_log.
1637 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); END;
1638 * binlog_format = STATEMENT: expect 1 warnings.
1639 INSERT INTO trigger_table_2 VALUES (1);
1640 Warnings:
1641 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1642 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1643 * binlog_format = MIXED: expect row events in binlog and no warning.
1644 DROP TRIGGER trig_2;
1645
1646 Invoking prepared statement prep_2 invoking trigger trig_1 invoking unsafe use of mysql.general_log.
1647 PREPARE prep_2 FROM "INSERT INTO trigger_table_1 VALUES (1)";
1648 * binlog_format = STATEMENT: expect 1 warnings.
1649 EXECUTE prep_2;
1650 Warnings:
1651 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1652 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1653 * binlog_format = MIXED: expect row events in binlog and no warning.
1654 DROP PREPARE prep_2;
1655 DROP TRIGGER trig_1;
1656
1657 Invoking view view_retval_1 returning value from unsafe use of mysql.general_log.
1658 CREATE VIEW view_retval_1 AS SELECT COUNT(*) FROM mysql.general_log;
1659 * binlog_format = STATEMENT: expect 1 warnings.
1660 INSERT INTO t1 SELECT * FROM view_retval_1;
1661 Warnings:
1662 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1663 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1664 * binlog_format = MIXED: expect row events in binlog and no warning.
1665 * Invoke statement so that return value is dicarded: expect no warning.
1666 SELECT * FROM view_retval_1;
1667
1668 Invoking function func_sidef_2 invoking view view_retval_1 returning value from unsafe use of mysql.general_log.
1669 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT * FROM view_retval_1; RETURN 0; END;
1670 * binlog_format = STATEMENT: expect 1 warnings.
1671 INSERT INTO t2 SELECT func_sidef_2();
1672 Warnings:
1673 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1674 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1675 * binlog_format = MIXED: expect row events in binlog and no warning.
1676 DROP FUNCTION func_sidef_2;
1677
1678 Invoking procedure proc_2 invoking view view_retval_1 returning value from unsafe use of mysql.general_log.
1679 CREATE PROCEDURE proc_2() BEGIN INSERT INTO t1 SELECT * FROM view_retval_1; INSERT INTO ta2 VALUES (47); END;
1680 * binlog_format = STATEMENT: expect 1 warnings.
1681 CALL proc_2();
1682 Warnings:
1683 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1684 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1685 * binlog_format = MIXED: expect row events in binlog and no warning.
1686 DROP PROCEDURE proc_2;
1687
1688 Invoking trigger trig_2 invoking view view_retval_1 returning value from unsafe use of mysql.general_log.
1689 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT * FROM view_retval_1; END;
1690 * binlog_format = STATEMENT: expect 1 warnings.
1691 INSERT INTO trigger_table_2 VALUES (1);
1692 Warnings:
1693 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1694 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1695 * binlog_format = MIXED: expect row events in binlog and no warning.
1696 DROP TRIGGER trig_2;
1697
1698 Invoking view view_retval_2 returning value from view view_retval_1 returning value from unsafe use of mysql.general_log.
1699 CREATE VIEW view_retval_2 AS SELECT * FROM view_retval_1;
1700 * binlog_format = STATEMENT: expect 1 warnings.
1701 INSERT INTO t2 SELECT * FROM view_retval_2;
1702 Warnings:
1703 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1704 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1705 * binlog_format = MIXED: expect row events in binlog and no warning.
1706 * Invoke statement so that return value is dicarded: expect no warning.
1707 SELECT * FROM view_retval_2;
1708 DROP VIEW view_retval_2;
1709
1710 Invoking prepared statement prep_2 invoking view view_retval_1 returning value from unsafe use of mysql.general_log.
1711 PREPARE prep_2 FROM "INSERT INTO t1 SELECT * FROM view_retval_1";
1712 * binlog_format = STATEMENT: expect 1 warnings.
1713 EXECUTE prep_2;
1714 Warnings:
1715 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1716 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1717 * binlog_format = MIXED: expect row events in binlog and no warning.
1718 DROP PREPARE prep_2;
1719 DROP VIEW view_retval_1;
1720
1721 Invoking prepared statement prep_1 invoking unsafe use of mysql.general_log.
1722 PREPARE prep_1 FROM "INSERT INTO t0 SELECT COUNT(*) FROM mysql.general_log";
1723 * binlog_format = STATEMENT: expect 1 warnings.
1724 EXECUTE prep_1;
1725 Warnings:
1726 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1727 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1728 * binlog_format = MIXED: expect row events in binlog and no warning.
1729 DROP PREPARE prep_1;
1730
1731 Invoking unsafe use of mysql.general_log.
1732 * binlog_format = STATEMENT: expect 1 warnings.
1733 INSERT INTO t0 SELECT COUNT(*) FROM mysql.general_log;
1734 Warnings:
1735 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1736 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1737 * binlog_format = MIXED: expect row events in binlog and no warning.
1738 * Invoke statement so that return value is dicarded: expect no warning.
1739 SELECT COUNT(*) FROM mysql.general_log;
1740
1741 ==== Testing a statement that is unsafe in many ways ====
1742
1743 Invoking function func_sidef_1 invoking statement that is unsafe in many ways.
1744 CREATE FUNCTION func_sidef_1() RETURNS VARCHAR(100) BEGIN INSERT INTO ta1 VALUES (47); INSERT DELAYED INTO double_autoinc_table SELECT CONCAT(UUID(), @@hostname, myfunc_int(), NULL) FROM mysql.general_log LIMIT 1; RETURN 0; END;
1745 * binlog_format = STATEMENT: expect 7 warnings.
1746 INSERT INTO t1 SELECT func_sidef_1();
1747 Warnings:
1748 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
1749 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1750 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
1751 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1752 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
1753 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
1754 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave
1755 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1756 * binlog_format = MIXED: expect row events in binlog and no warning.
1757
1758 Invoking function func_sidef_2 invoking function func_sidef_1 invoking statement that is unsafe in many ways.
1759 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); RETURN 0; END;
1760 * binlog_format = STATEMENT: expect 7 warnings.
1761 INSERT INTO t2 SELECT func_sidef_2();
1762 Warnings:
1763 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
1764 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1765 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
1766 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1767 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
1768 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
1769 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave
1770 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1771 * binlog_format = MIXED: expect row events in binlog and no warning.
1772 DROP FUNCTION func_sidef_2;
1773
1774 Invoking procedure proc_2 invoking function func_sidef_1 invoking statement that is unsafe in many ways.
1775 CREATE PROCEDURE proc_2() BEGIN INSERT INTO t1 SELECT func_sidef_1(); INSERT INTO ta2 VALUES (47); END;
1776 * binlog_format = STATEMENT: expect 7 warnings.
1777 CALL proc_2();
1778 Warnings:
1779 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
1780 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1781 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
1782 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1783 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
1784 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
1785 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave
1786 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1787 * binlog_format = MIXED: expect row events in binlog and no warning.
1788 DROP PROCEDURE proc_2;
1789
1790 Invoking trigger trig_2 invoking function func_sidef_1 invoking statement that is unsafe in many ways.
1791 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); END;
1792 * binlog_format = STATEMENT: expect 7 warnings.
1793 INSERT INTO trigger_table_2 VALUES (1);
1794 Warnings:
1795 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
1796 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1797 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
1798 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1799 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
1800 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
1801 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave
1802 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1803 * binlog_format = MIXED: expect row events in binlog and no warning.
1804 DROP TRIGGER trig_2;
1805
1806 Invoking view view_sidef_2 invoking function func_sidef_1 invoking statement that is unsafe in many ways.
1807 CREATE VIEW view_sidef_2 AS SELECT func_sidef_1();
1808 * binlog_format = STATEMENT: expect 7 warnings.
1809 INSERT INTO t2 SELECT * FROM view_sidef_2;
1810 Warnings:
1811 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
1812 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1813 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
1814 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1815 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
1816 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
1817 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave
1818 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1819 * binlog_format = MIXED: expect row events in binlog and no warning.
1820 DROP VIEW view_sidef_2;
1821
1822 Invoking prepared statement prep_2 invoking function func_sidef_1 invoking statement that is unsafe in many ways.
1823 PREPARE prep_2 FROM "INSERT INTO t1 SELECT func_sidef_1()";
1824 * binlog_format = STATEMENT: expect 7 warnings.
1825 EXECUTE prep_2;
1826 Warnings:
1827 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
1828 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1829 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
1830 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1831 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
1832 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
1833 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave
1834 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1835 * binlog_format = MIXED: expect row events in binlog and no warning.
1836 DROP PREPARE prep_2;
1837 DROP FUNCTION func_sidef_1;
1838
1839 Invoking procedure proc_1 invoking statement that is unsafe in many ways.
1840 CREATE PROCEDURE proc_1() BEGIN INSERT DELAYED INTO double_autoinc_table SELECT CONCAT(UUID(), @@hostname, myfunc_int(), NULL) FROM mysql.general_log LIMIT 1; INSERT INTO ta1 VALUES (47); END;
1841 * binlog_format = STATEMENT: expect 7 warnings.
1842 CALL proc_1();
1843 Warnings:
1844 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
1845 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1846 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
1847 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1848 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
1849 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
1850 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave
1851 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1852 * binlog_format = MIXED: expect row events in binlog and no warning.
1853
1854 Invoking function func_sidef_2 invoking procedure proc_1 invoking statement that is unsafe in many ways.
1855 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); RETURN 0; END;
1856 * binlog_format = STATEMENT: expect 7 warnings.
1857 INSERT INTO t2 SELECT func_sidef_2();
1858 Warnings:
1859 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
1860 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1861 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
1862 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1863 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
1864 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
1865 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave
1866 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1867 * binlog_format = MIXED: expect row events in binlog and no warning.
1868 DROP FUNCTION func_sidef_2;
1869
1870 Invoking procedure proc_2 invoking procedure proc_1 invoking statement that is unsafe in many ways.
1871 CREATE PROCEDURE proc_2() BEGIN CALL proc_1(); INSERT INTO ta2 VALUES (47); END;
1872 * binlog_format = STATEMENT: expect 7 warnings.
1873 CALL proc_2();
1874 Warnings:
1875 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
1876 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1877 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
1878 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1879 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
1880 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
1881 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave
1882 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1883 * binlog_format = MIXED: expect row events in binlog and no warning.
1884 DROP PROCEDURE proc_2;
1885
1886 Invoking trigger trig_2 invoking procedure proc_1 invoking statement that is unsafe in many ways.
1887 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); END;
1888 * binlog_format = STATEMENT: expect 7 warnings.
1889 INSERT INTO trigger_table_2 VALUES (1);
1890 Warnings:
1891 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
1892 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1893 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
1894 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1895 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
1896 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
1897 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave
1898 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1899 * binlog_format = MIXED: expect row events in binlog and no warning.
1900 DROP TRIGGER trig_2;
1901
1902 Invoking prepared statement prep_2 invoking procedure proc_1 invoking statement that is unsafe in many ways.
1903 PREPARE prep_2 FROM "CALL proc_1()";
1904 * binlog_format = STATEMENT: expect 7 warnings.
1905 EXECUTE prep_2;
1906 Warnings:
1907 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
1908 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1909 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
1910 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1911 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
1912 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
1913 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave
1914 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1915 * binlog_format = MIXED: expect row events in binlog and no warning.
1916 DROP PREPARE prep_2;
1917 DROP PROCEDURE proc_1;
1918
1919 Invoking trigger trig_1 invoking statement that is unsafe in many ways.
1920 CREATE TRIGGER trig_1 BEFORE INSERT ON trigger_table_1 FOR EACH ROW BEGIN INSERT INTO ta1 VALUES (47); INSERT DELAYED INTO double_autoinc_table SELECT CONCAT(UUID(), @@hostname, myfunc_int(), NULL) FROM mysql.general_log LIMIT 1; END;
1921 * binlog_format = STATEMENT: expect 7 warnings.
1922 INSERT INTO trigger_table_1 VALUES (1);
1923 Warnings:
1924 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
1925 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1926 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
1927 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1928 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
1929 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
1930 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave
1931 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1932 * binlog_format = MIXED: expect row events in binlog and no warning.
1933
1934 Invoking function func_sidef_2 invoking trigger trig_1 invoking statement that is unsafe in many ways.
1935 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); RETURN 0; END;
1936 * binlog_format = STATEMENT: expect 7 warnings.
1937 INSERT INTO t2 SELECT func_sidef_2();
1938 Warnings:
1939 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
1940 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1941 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
1942 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1943 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
1944 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
1945 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave
1946 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1947 * binlog_format = MIXED: expect row events in binlog and no warning.
1948 DROP FUNCTION func_sidef_2;
1949
1950 Invoking procedure proc_2 invoking trigger trig_1 invoking statement that is unsafe in many ways.
1951 CREATE PROCEDURE proc_2() BEGIN INSERT INTO trigger_table_1 VALUES (1); INSERT INTO ta2 VALUES (47); END;
1952 * binlog_format = STATEMENT: expect 7 warnings.
1953 CALL proc_2();
1954 Warnings:
1955 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
1956 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1957 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
1958 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1959 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
1960 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
1961 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave
1962 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1963 * binlog_format = MIXED: expect row events in binlog and no warning.
1964 DROP PROCEDURE proc_2;
1965
1966 Invoking trigger trig_2 invoking trigger trig_1 invoking statement that is unsafe in many ways.
1967 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); END;
1968 * binlog_format = STATEMENT: expect 7 warnings.
1969 INSERT INTO trigger_table_2 VALUES (1);
1970 Warnings:
1971 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
1972 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1973 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
1974 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1975 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
1976 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
1977 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave
1978 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1979 * binlog_format = MIXED: expect row events in binlog and no warning.
1980 DROP TRIGGER trig_2;
1981
1982 Invoking prepared statement prep_2 invoking trigger trig_1 invoking statement that is unsafe in many ways.
1983 PREPARE prep_2 FROM "INSERT INTO trigger_table_1 VALUES (1)";
1984 * binlog_format = STATEMENT: expect 7 warnings.
1985 EXECUTE prep_2;
1986 Warnings:
1987 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
1988 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
1989 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
1990 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
1991 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
1992 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
1993 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave
1994 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
1995 * binlog_format = MIXED: expect row events in binlog and no warning.
1996 DROP PREPARE prep_2;
1997 DROP TRIGGER trig_1;
1998
1999 Invoking prepared statement prep_1 invoking statement that is unsafe in many ways.
2000 PREPARE prep_1 FROM "INSERT DELAYED INTO double_autoinc_table SELECT CONCAT(UUID(), @@hostname, myfunc_int(), NULL) FROM mysql.general_log LIMIT 1";
2001 * binlog_format = STATEMENT: expect 7 warnings.
2002 EXECUTE prep_1;
2003 Warnings:
2004 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
2005 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
2006 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
2007 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
2008 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2009 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2010 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave
2011 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2012 * binlog_format = MIXED: expect row events in binlog and no warning.
2013 DROP PREPARE prep_1;
2014
2015 Invoking statement that is unsafe in many ways.
2016 * binlog_format = STATEMENT: expect 7 warnings.
2017 INSERT DELAYED INTO double_autoinc_table SELECT CONCAT(UUID(), @@hostname, myfunc_int(), NULL) FROM mysql.general_log LIMIT 1;
2018 Warnings:
2019 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
2020 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves
2021 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
2022 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a UDF which may not return the same value on the slave
2023 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2024 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2025 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave
2026 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2027 * binlog_format = MIXED: expect row events in binlog and no warning.
2028
2029 ==== Testing a statement that is unsafe several times ====
2030
2031 Invoking function func_sidef_1 invoking statement that is unsafe several times.
2032 CREATE FUNCTION func_sidef_1() RETURNS VARCHAR(100) BEGIN INSERT INTO ta1 VALUES (47); INSERT INTO ta0 VALUES (multi_unsafe_func()); RETURN 0; END;
2033 * binlog_format = STATEMENT: expect 2 warnings.
2034 INSERT INTO t1 SELECT func_sidef_1();
2035 Warnings:
2036 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2037 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2038 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2039 * binlog_format = MIXED: expect row events in binlog and no warning.
2040
2041 Invoking function func_sidef_2 invoking function func_sidef_1 invoking statement that is unsafe several times.
2042 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); RETURN 0; END;
2043 * binlog_format = STATEMENT: expect 2 warnings.
2044 INSERT INTO t2 SELECT func_sidef_2();
2045 Warnings:
2046 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2047 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2048 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2049 * binlog_format = MIXED: expect row events in binlog and no warning.
2050 DROP FUNCTION func_sidef_2;
2051
2052 Invoking procedure proc_2 invoking function func_sidef_1 invoking statement that is unsafe several times.
2053 CREATE PROCEDURE proc_2() BEGIN INSERT INTO t1 SELECT func_sidef_1(); INSERT INTO ta2 VALUES (47); END;
2054 * binlog_format = STATEMENT: expect 2 warnings.
2055 CALL proc_2();
2056 Warnings:
2057 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2058 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2059 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2060 * binlog_format = MIXED: expect row events in binlog and no warning.
2061 DROP PROCEDURE proc_2;
2062
2063 Invoking trigger trig_2 invoking function func_sidef_1 invoking statement that is unsafe several times.
2064 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT func_sidef_1(); END;
2065 * binlog_format = STATEMENT: expect 2 warnings.
2066 INSERT INTO trigger_table_2 VALUES (1);
2067 Warnings:
2068 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2069 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2070 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2071 * binlog_format = MIXED: expect row events in binlog and no warning.
2072 DROP TRIGGER trig_2;
2073
2074 Invoking view view_sidef_2 invoking function func_sidef_1 invoking statement that is unsafe several times.
2075 CREATE VIEW view_sidef_2 AS SELECT func_sidef_1();
2076 * binlog_format = STATEMENT: expect 2 warnings.
2077 INSERT INTO t2 SELECT * FROM view_sidef_2;
2078 Warnings:
2079 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2080 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2081 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2082 * binlog_format = MIXED: expect row events in binlog and no warning.
2083 DROP VIEW view_sidef_2;
2084
2085 Invoking prepared statement prep_2 invoking function func_sidef_1 invoking statement that is unsafe several times.
2086 PREPARE prep_2 FROM "INSERT INTO t1 SELECT func_sidef_1()";
2087 * binlog_format = STATEMENT: expect 2 warnings.
2088 EXECUTE prep_2;
2089 Warnings:
2090 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2091 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2092 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2093 * binlog_format = MIXED: expect row events in binlog and no warning.
2094 DROP PREPARE prep_2;
2095 DROP FUNCTION func_sidef_1;
2096
2097 Invoking procedure proc_1 invoking statement that is unsafe several times.
2098 CREATE PROCEDURE proc_1() BEGIN INSERT INTO ta0 VALUES (multi_unsafe_func()); INSERT INTO ta1 VALUES (47); END;
2099 * binlog_format = STATEMENT: expect 2 warnings.
2100 CALL proc_1();
2101 Warnings:
2102 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2103 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2104 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2105 * binlog_format = MIXED: expect row events in binlog and no warning.
2106
2107 Invoking function func_sidef_2 invoking procedure proc_1 invoking statement that is unsafe several times.
2108 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); RETURN 0; END;
2109 * binlog_format = STATEMENT: expect 2 warnings.
2110 INSERT INTO t2 SELECT func_sidef_2();
2111 Warnings:
2112 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2113 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2114 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2115 * binlog_format = MIXED: expect row events in binlog and no warning.
2116 DROP FUNCTION func_sidef_2;
2117
2118 Invoking procedure proc_2 invoking procedure proc_1 invoking statement that is unsafe several times.
2119 CREATE PROCEDURE proc_2() BEGIN CALL proc_1(); INSERT INTO ta2 VALUES (47); END;
2120 * binlog_format = STATEMENT: expect 2 warnings.
2121 CALL proc_2();
2122 Warnings:
2123 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2124 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2125 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2126 * binlog_format = MIXED: expect row events in binlog and no warning.
2127 DROP PROCEDURE proc_2;
2128
2129 Invoking trigger trig_2 invoking procedure proc_1 invoking statement that is unsafe several times.
2130 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); CALL proc_1(); END;
2131 * binlog_format = STATEMENT: expect 2 warnings.
2132 INSERT INTO trigger_table_2 VALUES (1);
2133 Warnings:
2134 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2135 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2136 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2137 * binlog_format = MIXED: expect row events in binlog and no warning.
2138 DROP TRIGGER trig_2;
2139
2140 Invoking prepared statement prep_2 invoking procedure proc_1 invoking statement that is unsafe several times.
2141 PREPARE prep_2 FROM "CALL proc_1()";
2142 * binlog_format = STATEMENT: expect 2 warnings.
2143 EXECUTE prep_2;
2144 Warnings:
2145 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2146 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2147 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2148 * binlog_format = MIXED: expect row events in binlog and no warning.
2149 DROP PREPARE prep_2;
2150 DROP PROCEDURE proc_1;
2151
2152 Invoking trigger trig_1 invoking statement that is unsafe several times.
2153 CREATE TRIGGER trig_1 BEFORE INSERT ON trigger_table_1 FOR EACH ROW BEGIN INSERT INTO ta1 VALUES (47); INSERT INTO ta0 VALUES (multi_unsafe_func()); END;
2154 * binlog_format = STATEMENT: expect 2 warnings.
2155 INSERT INTO trigger_table_1 VALUES (1);
2156 Warnings:
2157 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2158 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2159 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2160 * binlog_format = MIXED: expect row events in binlog and no warning.
2161
2162 Invoking function func_sidef_2 invoking trigger trig_1 invoking statement that is unsafe several times.
2163 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); RETURN 0; END;
2164 * binlog_format = STATEMENT: expect 2 warnings.
2165 INSERT INTO t2 SELECT func_sidef_2();
2166 Warnings:
2167 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2168 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2169 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2170 * binlog_format = MIXED: expect row events in binlog and no warning.
2171 DROP FUNCTION func_sidef_2;
2172
2173 Invoking procedure proc_2 invoking trigger trig_1 invoking statement that is unsafe several times.
2174 CREATE PROCEDURE proc_2() BEGIN INSERT INTO trigger_table_1 VALUES (1); INSERT INTO ta2 VALUES (47); END;
2175 * binlog_format = STATEMENT: expect 2 warnings.
2176 CALL proc_2();
2177 Warnings:
2178 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2179 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2180 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2181 * binlog_format = MIXED: expect row events in binlog and no warning.
2182 DROP PROCEDURE proc_2;
2183
2184 Invoking trigger trig_2 invoking trigger trig_1 invoking statement that is unsafe several times.
2185 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO trigger_table_1 VALUES (1); END;
2186 * binlog_format = STATEMENT: expect 2 warnings.
2187 INSERT INTO trigger_table_2 VALUES (1);
2188 Warnings:
2189 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2190 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2191 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2192 * binlog_format = MIXED: expect row events in binlog and no warning.
2193 DROP TRIGGER trig_2;
2194
2195 Invoking prepared statement prep_2 invoking trigger trig_1 invoking statement that is unsafe several times.
2196 PREPARE prep_2 FROM "INSERT INTO trigger_table_1 VALUES (1)";
2197 * binlog_format = STATEMENT: expect 2 warnings.
2198 EXECUTE prep_2;
2199 Warnings:
2200 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2201 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2202 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2203 * binlog_format = MIXED: expect row events in binlog and no warning.
2204 DROP PREPARE prep_2;
2205 DROP TRIGGER trig_1;
2206
2207 Invoking view view_sidef_1 invoking statement that is unsafe several times.
2208 CREATE VIEW view_sidef_1 AS SELECT multi_unsafe_func();
2209 * binlog_format = STATEMENT: expect 2 warnings.
2210 INSERT INTO t1 SELECT * FROM view_sidef_1;
2211 Warnings:
2212 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2213 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2214 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2215 * binlog_format = MIXED: expect row events in binlog and no warning.
2216
2217 Invoking function func_sidef_2 invoking view view_sidef_1 invoking statement that is unsafe several times.
2218 CREATE FUNCTION func_sidef_2() RETURNS VARCHAR(100) BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT * FROM view_sidef_1; RETURN 0; END;
2219 * binlog_format = STATEMENT: expect 2 warnings.
2220 INSERT INTO t2 SELECT func_sidef_2();
2221 Warnings:
2222 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2223 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2224 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2225 * binlog_format = MIXED: expect row events in binlog and no warning.
2226 DROP FUNCTION func_sidef_2;
2227
2228 Invoking procedure proc_2 invoking view view_sidef_1 invoking statement that is unsafe several times.
2229 CREATE PROCEDURE proc_2() BEGIN INSERT INTO t1 SELECT * FROM view_sidef_1; INSERT INTO ta2 VALUES (47); END;
2230 * binlog_format = STATEMENT: expect 2 warnings.
2231 CALL proc_2();
2232 Warnings:
2233 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2234 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2235 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2236 * binlog_format = MIXED: expect row events in binlog and no warning.
2237 DROP PROCEDURE proc_2;
2238
2239 Invoking trigger trig_2 invoking view view_sidef_1 invoking statement that is unsafe several times.
2240 CREATE TRIGGER trig_2 BEFORE INSERT ON trigger_table_2 FOR EACH ROW BEGIN INSERT INTO ta2 VALUES (47); INSERT INTO t1 SELECT * FROM view_sidef_1; END;
2241 * binlog_format = STATEMENT: expect 2 warnings.
2242 INSERT INTO trigger_table_2 VALUES (1);
2243 Warnings:
2244 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2245 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2246 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2247 * binlog_format = MIXED: expect row events in binlog and no warning.
2248 DROP TRIGGER trig_2;
2249
2250 Invoking view view_sidef_2 invoking view view_sidef_1 invoking statement that is unsafe several times.
2251 CREATE VIEW view_sidef_2 AS SELECT * FROM view_sidef_1;
2252 * binlog_format = STATEMENT: expect 2 warnings.
2253 INSERT INTO t2 SELECT * FROM view_sidef_2;
2254 Warnings:
2255 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2256 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2257 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2258 * binlog_format = MIXED: expect row events in binlog and no warning.
2259 DROP VIEW view_sidef_2;
2260
2261 Invoking prepared statement prep_2 invoking view view_sidef_1 invoking statement that is unsafe several times.
2262 PREPARE prep_2 FROM "INSERT INTO t1 SELECT * FROM view_sidef_1";
2263 * binlog_format = STATEMENT: expect 2 warnings.
2264 EXECUTE prep_2;
2265 Warnings:
2266 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2267 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2268 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2269 * binlog_format = MIXED: expect row events in binlog and no warning.
2270 DROP PREPARE prep_2;
2271 DROP VIEW view_sidef_1;
2272
2273 Invoking prepared statement prep_1 invoking statement that is unsafe several times.
2274 PREPARE prep_1 FROM "INSERT INTO ta0 VALUES (multi_unsafe_func())";
2275 * binlog_format = STATEMENT: expect 2 warnings.
2276 EXECUTE prep_1;
2277 Warnings:
2278 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2279 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2280 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2281 * binlog_format = MIXED: expect row events in binlog and no warning.
2282 DROP PREPARE prep_1;
2283
2284 Invoking statement that is unsafe several times.
2285 * binlog_format = STATEMENT: expect 2 warnings.
2286 INSERT INTO ta0 VALUES (multi_unsafe_func());
2287 Warnings:
2288 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2289 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2290 * SQL_LOG_BIN = 0: expect nothing logged and no warning.
2291 * binlog_format = MIXED: expect row events in binlog and no warning.
2292 DROP TRIGGER double_autoinc_trig;
2293 DROP TABLE t0, t1, t2, t3, ta0, ta1, ta2, ta3,
2294 autoinc_table, double_autoinc_table,
2295 data_table,
2296 trigger_table_1, trigger_table_2, trigger_table_3;
2297 DROP FUNCTION myfunc_int;
2298 DROP FUNCTION multi_unsafe_func;
2299 ==== Special system variables that should *not* be unsafe ====
2300 CREATE TABLE t1 (a VARCHAR(1000));
2301 CREATE TABLE autoinc_table (a INT PRIMARY KEY AUTO_INCREMENT);
2302 INSERT INTO t1 VALUES (@@session.auto_increment_increment);
2303 INSERT INTO t1 VALUES (@@session.auto_increment_offset);
2304 INSERT INTO t1 VALUES (@@session.character_set_client);
2305 INSERT INTO t1 VALUES (@@session.character_set_connection);
2306 INSERT INTO t1 VALUES (@@session.character_set_database);
2307 INSERT INTO t1 VALUES (@@session.character_set_server);
2308 INSERT INTO t1 VALUES (@@session.collation_connection);
2309 INSERT INTO t1 VALUES (@@session.collation_database);
2310 INSERT INTO t1 VALUES (@@session.collation_server);
2311 INSERT INTO t1 VALUES (@@session.foreign_key_checks);
2312 INSERT INTO t1 VALUES (@@session.identity);
2313 INSERT INTO t1 VALUES (@@session.last_insert_id);
2314 INSERT INTO t1 VALUES (@@session.lc_time_names);
2315 INSERT INTO t1 VALUES (@@session.pseudo_thread_id);
2316 INSERT INTO t1 VALUES (@@session.sql_auto_is_null);
2317 INSERT INTO t1 VALUES (@@session.timestamp);
2318 INSERT INTO t1 VALUES (@@session.time_zone);
2319 INSERT INTO t1 VALUES (@@session.unique_checks);
2320 SET @my_var= 4711;
2321 INSERT INTO t1 VALUES (@my_var);
2322 SET insert_id= 12;
2323 INSERT INTO autoinc_table VALUES (NULL);
2324 The following variables *should* give a warning, despite they are replicated.
2325 INSERT INTO t1 VALUES (@@session.sql_mode);
2326 Warnings:
2327 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2328 INSERT INTO t1 VALUES (@@session.insert_id);
2329 Warnings:
2330 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2331 DROP TABLE t1, autoinc_table;
2332 CREATE TABLE t1(a INT, b INT, KEY(a), PRIMARY KEY(b));
2333 INSERT INTO t1 SELECT * FROM t1 LIMIT 1;
2334 Warnings:
2335 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
2336 REPLACE INTO t1 SELECT * FROM t1 LIMIT 1;
2337 Warnings:
2338 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
2339 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. REPLACE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave
2340 UPDATE t1 SET a=1 LIMIT 1;
2341 DELETE FROM t1 LIMIT 1;
2342 CREATE PROCEDURE p1()
2343 BEGIN
2344 INSERT INTO t1 SELECT * FROM t1 LIMIT 1;
2345 REPLACE INTO t1 SELECT * FROM t1 LIMIT 1;
2346 UPDATE t1 SET a=1 LIMIT 1;
2347 DELETE FROM t1 LIMIT 1;
2348 END|
2349 CALL p1();
2350 Warnings:
2351 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
2352 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. REPLACE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave
2353 DROP PROCEDURE p1;
2354 DROP TABLE t1;
2355 DROP TABLE IF EXISTS t1;
2356 CREATE TABLE t1 (a VARCHAR(200), b VARCHAR(200));
2357 INSERT INTO t1 VALUES ('a','b');
2358 UPDATE t1 SET b = '%s%s%s%s%s%s%s%s%s%s%s%s%s%s' WHERE a = 'a' LIMIT 1;
2359 Warnings:
2360 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
2361 DROP TABLE t1;
2362 DROP TABLE IF EXISTS t1, t2;
2363 CREATE TABLE t1(i INT PRIMARY KEY);
2364 CREATE TABLE t2(i INT PRIMARY KEY);
2365 CREATE TABLE t3(i INT, ch CHAR(50));
2366 "Should issue message Statement may not be safe to log in statement format."
2367 INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
2368 Warnings:
2369 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
2370 CREATE FUNCTION func6()
2371 RETURNS INT
2372 BEGIN
2373 INSERT INTO t1 VALUES (10);
2374 INSERT INTO t1 VALUES (11);
2375 INSERT INTO t1 VALUES (12);
2376 RETURN 0;
2377 END|
2378 "Should issue message Statement may not be safe to log in statement format only once"
2379 INSERT INTO t3 VALUES(func6(), UUID());
2380 Warnings:
2381 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2382 "Check whether SET @@SQL_LOG_BIN = 0/1 doesn't work in substatements"
2383 CREATE FUNCTION fun_check_log_bin() RETURNS INT
2384 BEGIN
2385 SET @@SQL_LOG_BIN = 0;
2386 INSERT INTO t1 VALUES(@@global.sync_binlog);
2387 RETURN 200;
2388 END|
2389 "One unsafe warning should be issued in the following statement"
2390 SELECT fun_check_log_bin();
2391 ERROR HY000: Cannot change the sql_log_bin inside a stored function or trigger
2392 "SQL_LOG_BIN should be ON still"
2393 SHOW VARIABLES LIKE "SQL_LOG_BIN";
2394 Variable_name Value
2395 sql_log_bin ON
2396 set @save_log_bin = @@SESSION.SQL_LOG_BIN;
2397 set @@SESSION.SQL_LOG_BIN = 0;
2398 "Should NOT have any warning message issued in the following statements"
2399 INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
2400 DROP TABLE t1,t2;
2401 "Should NOT have any warning message issued in the following func7() and trig"
2402 CREATE TABLE t1 (a INT);
2403 CREATE TABLE t2 (a TEXT);
2404 CREATE TABLE trigger_table (a CHAR(7));
2405 CREATE FUNCTION func7()
2406 RETURNS INT
2407 BEGIN
2408 INSERT INTO t1 VALUES (@@global.sync_binlog);
2409 INSERT INTO t1 VALUES (@@session.insert_id);
2410 INSERT INTO t2 SELECT UUID();
2411 INSERT INTO t2 VALUES (@@session.sql_mode);
2412 INSERT INTO t2 VALUES (@@global.init_slave);
2413 RETURN 0;
2414 END|
2415 SHOW VARIABLES LIKE "SQL_LOG_BIN";
2416 Variable_name Value
2417 sql_log_bin OFF
2418 SELECT func7();
2419 func7()
2420 0
2421 ---- Insert from trigger ----
2422 CREATE TRIGGER trig
2423 BEFORE INSERT ON trigger_table
2424 FOR EACH ROW
2425 BEGIN
2426 INSERT INTO t1 VALUES (@@global.sync_binlog);
2427 INSERT INTO t1 VALUES (@@session.insert_id);
2428 INSERT INTO t1 VALUES (@@global.auto_increment_increment);
2429 INSERT INTO t2 SELECT UUID();
2430 INSERT INTO t2 VALUES (@@session.sql_mode);
2431 INSERT INTO t2 VALUES (@@global.init_slave);
2432 INSERT INTO t2 VALUES (@@hostname);
2433 END|
2434 INSERT INTO trigger_table VALUES ('bye.');
2435 DROP FUNCTION fun_check_log_bin;
2436 DROP FUNCTION func6;
2437 DROP FUNCTION func7;
2438 DROP TRIGGER trig;
2439 DROP TABLE t1, t2, t3, trigger_table;
2440 set @@SESSION.SQL_LOG_BIN = @save_log_bin;
2441 SET @save_sql_mode = @@SESSION.SQL_MODE;
2442 SET @@SESSION.SQL_MODE = STRICT_ALL_TABLES;
2443 CREATE TABLE t1(i INT PRIMARY KEY);
2444 CREATE TABLE t2(i INT PRIMARY KEY);
2445 INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
2446 Warnings:
2447 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
2448 INSERT INTO t1 VALUES(@@global.sync_binlog);
2449 Warnings:
2450 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
2451 UPDATE t1 SET i = 999 LIMIT 1;
2452 Warnings:
2453 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
2454 DELETE FROM t1 LIMIT 1;
2455 Warnings:
2456 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted
2457 DROP TABLE t1, t2;
2458 SET @@SESSION.SQL_MODE = @save_sql_mode;
2459 SET @old_binlog_format = @@session.binlog_format;
2460 SET binlog_format = MIXED;
2461 CREATE TABLE t1 (a INT);
2462 CREATE TABLE t2 (a INT);
2463 INSERT INTO t2 VALUES (1), (2);
2464 CREATE PROCEDURE proc_insert_delayed ()
2465 BEGIN
2466 INSERT DELAYED INTO t1 VALUES (1), (2);
2467 END|
2468 CREATE FUNCTION func_limit ()
2469 RETURNS INT
2470 BEGIN
2471 INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
2472 RETURN 1;
2473 END|
2474 RESET MASTER;
2475 CALL proc_insert_delayed();
2476 SELECT func_limit();
2477 func_limit()
2478 1
2479 include/show_binlog_events.inc
2480 Log_name Pos Event_type Server_id End_log_pos Info
2481 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
2482 master-bin.000001 # Query # # use `test`; INSERT DELAYED INTO t1 VALUES (1), (2)
2483 master-bin.000001 # Query # # COMMIT
2484 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
2485 master-bin.000001 # Annotate_rows # # INSERT INTO t1 SELECT * FROM t2 LIMIT 1
2486 master-bin.000001 # Table_map # # table_id: # (test.t1)
2487 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
2488 master-bin.000001 # Query # # COMMIT
2489 SET @@session.binlog_format = @old_binlog_format;
2490 DROP TABLE t1, t2;
2491 DROP PROCEDURE proc_insert_delayed;
2492 DROP FUNCTION func_limit;
2493 CREATE TABLE t1 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
2494 CREATE TABLE t2 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
2495 CREATE FUNCTION func_modify_t1 ()
2496 RETURNS INT
2497 BEGIN
2498 INSERT INTO t1 SET a = 1;
2499 RETURN 0;
2500 END|
2501 # The following statement causes auto-incrementation
2502 # of both t1 and t2. It is logged in statement format,
2503 # so it should produce unsafe warning.
2504 INSERT INTO t2 SET a = func_modify_t1();
2505 Warnings:
2506 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
2507 SET SESSION binlog_format = MIXED;
2508 # Check if the statement is logged in row format.
2509 INSERT INTO t2 SET a = func_modify_t1();
2510 include/show_binlog_events.inc
2511 Log_name Pos Event_type Server_id End_log_pos Info
2512 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
2513 master-bin.000001 # Annotate_rows # # INSERT INTO t1 SET a = 1
2514 master-bin.000001 # Table_map # # table_id: # (test.t2)
2515 master-bin.000001 # Table_map # # table_id: # (test.t1)
2516 master-bin.000001 # Write_rows_v1 # # table_id: #
2517 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
2518 master-bin.000001 # Query # # COMMIT
2519 DROP TABLE t1,t2;
2520 DROP FUNCTION func_modify_t1;
2521 SET SESSION binlog_format = STATEMENT;
2522 CREATE TABLE t1 (a INT);
2523 CREATE TABLE t2 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
2524 CREATE TABLE t3 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
2525 create trigger tri_modify_two_tables before insert on t1 for each row begin
2526 insert into t2(a) values(new.a);
2527 insert into t3(a) values(new.a);
2528 end |
2529 # The following statement causes auto-incrementation
2530 # of both t2 and t3. It is logged in statement format,
2531 # so it should produce unsafe warning
2532 INSERT INTO t1 SET a = 1;
2533 Warnings:
2534 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
2535 SET SESSION binlog_format = MIXED;
2536 # Check if the statement is logged in row format.
2537 INSERT INTO t1 SET a = 2;
2538 include/show_binlog_events.inc
2539 Log_name Pos Event_type Server_id End_log_pos Info
2540 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
2541 master-bin.000001 # Annotate_rows # # insert into t2(a) values(new.a)
2542 master-bin.000001 # Table_map # # table_id: # (test.t1)
2543 master-bin.000001 # Table_map # # table_id: # (test.t2)
2544 master-bin.000001 # Table_map # # table_id: # (test.t3)
2545 master-bin.000001 # Write_rows_v1 # # table_id: #
2546 master-bin.000001 # Write_rows_v1 # # table_id: #
2547 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
2548 master-bin.000001 # Query # # COMMIT
2549 DROP TABLE t1,t2,t3;
2550 SET SESSION binlog_format = STATEMENT;
2551 CREATE TABLE t1 (a VARBINARY(1000));
2552 INSERT INTO t1 VALUES (CURRENT_USER());
2553 Warnings:
2554 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2555 INSERT INTO t1 VALUES (FOUND_ROWS());
2556 Warnings:
2557 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2558 INSERT INTO t1 VALUES (GET_LOCK('tmp', 1));
2559 Warnings:
2560 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2561 INSERT INTO t1 VALUES (IS_FREE_LOCK('tmp'));
2562 Warnings:
2563 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2564 INSERT INTO t1 VALUES (IS_USED_LOCK('tmp'));
2565 Warnings:
2566 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2567 INSERT INTO t1 VALUES (LOAD_FILE('../../std_data/words2.dat'));
2568 Warnings:
2569 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2570 INSERT INTO t1 VALUES (MASTER_POS_WAIT('dummy arg', 4711, 1));
2571 Warnings:
2572 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2573 INSERT INTO t1 VALUES (RELEASE_LOCK('tmp'));
2574 Warnings:
2575 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2576 INSERT INTO t1 VALUES (ROW_COUNT());
2577 Warnings:
2578 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2579 INSERT INTO t1 VALUES (SESSION_USER());
2580 Warnings:
2581 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2582 INSERT INTO t1 VALUES (SLEEP(1));
2583 Warnings:
2584 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2585 INSERT INTO t1 VALUES (SYSDATE());
2586 Warnings:
2587 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2588 INSERT INTO t1 VALUES (SYSTEM_USER());
2589 Warnings:
2590 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2591 INSERT INTO t1 VALUES (USER());
2592 Warnings:
2593 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2594 INSERT INTO t1 VALUES (UUID());
2595 Warnings:
2596 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2597 INSERT INTO t1 VALUES (UUID_SHORT());
2598 Warnings:
2599 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2600 INSERT INTO t1 VALUES (VERSION());
2601 Warnings:
2602 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2603 INSERT INTO t1 VALUES (RAND());
2604 INSERT INTO t1 VALUES (RANDOM_BYTES(1000));
2605 Warnings:
2606 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
2607 DELETE FROM t1;
2608 SET TIME_ZONE= '+03:00';
2609 SET TIMESTAMP=1000000;
2610 INSERT INTO t1 VALUES
2611 (CURDATE()),
2612 (CURRENT_DATE()),
2613 (CURRENT_TIME()),
2614 (CURRENT_TIMESTAMP()),
2615 (CURTIME()),
2616 (LOCALTIME()),
2617 (LOCALTIMESTAMP()),
2618 (NOW()),
2619 (UNIX_TIMESTAMP()),
2620 (UTC_DATE()),
2621 (UTC_TIME()),
2622 (UTC_TIMESTAMP());
2623 SELECT * FROM t1;
2624 a
2625 1970-01-12
2626 1970-01-12
2627 16:46:40
2628 1970-01-12 16:46:40
2629 16:46:40
2630 16:46:40
2631 1970-01-12 16:46:40
2632 1970-01-12 16:46:40
2633 1000000
2634 1970-01-12
2635 13:46:40
2636 1970-01-12 13:46:40
2637 DROP TABLE t1;
2638 CREATE TABLE filler_table (a INT, b INT);
2639 INSERT INTO filler_table values (1,1),(1,2);
2640 CREATE TABLE insert_table (a INT, b INT, PRIMARY KEY(a));
2641 CREATE TABLE replace_table (a INT, b INT, PRIMARY KEY(a));
2642 INSERT INTO replace_table values (1,1),(2,2);
2643 CREATE TABLE update_table (a INT, b INT, PRIMARY KEY(a));
2644 INSERT INTO update_table values (1,1),(2,2);
2645 CREATE TABLE insert_2_keys (a INT UNIQUE KEY, b INT UNIQUE KEY);
2646 INSERT INTO insert_2_keys values (1, 1);
2647 INSERT IGNORE INTO insert_table SELECT * FROM filler_table;
2648 Warnings:
2649 Warning 1062 Duplicate entry '1' for key 'PRIMARY'
2650 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT IGNORE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are ignored. This order cannot be predicted and may differ on master and the slave
2651 TRUNCATE TABLE insert_table;
2652 INSERT INTO insert_table SELECT * FROM filler_table ON DUPLICATE KEY UPDATE a = 1;
2653 Warnings:
2654 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... SELECT... ON DUPLICATE KEY UPDATE is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are updated. This order cannot be predicted and may differ on master and the slave
2655 TRUNCATE TABLE insert_table;
2656 REPLACE INTO replace_table SELECT * FROM filler_table;
2657 Warnings:
2658 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. REPLACE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave
2659 UPDATE IGNORE update_table SET a=2;
2660 Warnings:
2661 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. UPDATE IGNORE is unsafe because the order in which rows are updated determines which (if any) rows are ignored. This order cannot be predicted and may differ on master and the slave
2662 CREATE TABLE create_ignore_test (a INT, b INT, PRIMARY KEY(b)) IGNORE SELECT * FROM filler_table;
2663 Warnings:
2664 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. CREATE... IGNORE SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are ignored. This order cannot be predicted and may differ on master and the slave
2665 CREATE TABLE create_replace_test (a INT, b INT, PRIMARY KEY(b)) REPLACE SELECT * FROM filler_table;
2666 Warnings:
2667 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. CREATE... REPLACE SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave
2668 CREATE TEMPORARY TABLE temp1 (a INT, b INT, PRIMARY KEY(b)) REPLACE SELECT * FROM filler_table;
2669 Warnings:
2670 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. CREATE... REPLACE SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave
2671 INSERT INTO insert_2_keys VALUES (1, 2)
2672 ON DUPLICATE KEY UPDATE a=VALUES(a)+10, b=VALUES(b)+10;
2673 Warnings:
2674 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe
2675 DROP TABLE filler_table;
2676 DROP TABLE insert_table;
2677 DROP TABLE update_table;
2678 DROP TABLE replace_table;
2679 DROP TABLE create_ignore_test;
2680 DROP TABLE create_replace_test;
2681 DROP TABLE insert_2_keys;
2682 "End of tests"