2
CREATE TABLE t1 (a INT);
3
CREATE TABLE t2 (a CHAR(40));
4
CREATE TABLE t3 (a INT AUTO_INCREMENT PRIMARY KEY);
5
CREATE TABLE trigger_table (a CHAR(7));
6
CREATE TABLE trigger_table2 (a INT);
7
==== Non-deterministic statements ====
8
INSERT DELAYED INTO t1 VALUES (5);
9
==== Some variables that *should* be unsafe ====
10
---- Insert directly ----
11
INSERT INTO t1 VALUES (@@global.sync_binlog);
13
Note 1592 Statement may not be safe to log in statement format.
14
INSERT INTO t1 VALUES (@@session.insert_id);
16
Note 1592 Statement may not be safe to log in statement format.
17
INSERT INTO t1 VALUES (@@global.auto_increment_increment);
19
Note 1592 Statement may not be safe to log in statement format.
20
INSERT INTO t2 SELECT UUID();
22
Note 1592 Statement may not be safe to log in statement format.
23
INSERT INTO t2 VALUES (@@session.sql_mode);
25
Note 1592 Statement may not be safe to log in statement format.
26
INSERT INTO t2 VALUES (@@global.init_slave);
28
Note 1592 Statement may not be safe to log in statement format.
29
INSERT INTO t2 VALUES (@@hostname);
31
Note 1592 Statement may not be safe to log in statement format.
32
---- Insert from stored procedure ----
33
CREATE PROCEDURE proc()
35
INSERT INTO t1 VALUES (@@global.sync_binlog);
36
INSERT INTO t1 VALUES (@@session.insert_id);
37
INSERT INTO t1 VALUES (@@global.auto_increment_increment);
38
INSERT INTO t2 SELECT UUID();
39
INSERT INTO t2 VALUES (@@session.sql_mode);
40
INSERT INTO t2 VALUES (@@global.init_slave);
41
INSERT INTO t2 VALUES (@@hostname);
45
Note 1592 Statement may not be safe to log in statement format.
46
Note 1592 Statement may not be safe to log in statement format.
47
Note 1592 Statement may not be safe to log in statement format.
48
Note 1592 Statement may not be safe to log in statement format.
49
Note 1592 Statement may not be safe to log in statement format.
50
Note 1592 Statement may not be safe to log in statement format.
51
Note 1592 Statement may not be safe to log in statement format.
52
---- Insert from stored function ----
53
CREATE FUNCTION func()
56
INSERT INTO t1 VALUES (@@global.sync_binlog);
57
INSERT INTO t1 VALUES (@@session.insert_id);
58
INSERT INTO t1 VALUES (@@global.auto_increment_increment);
59
INSERT INTO t2 SELECT UUID();
60
INSERT INTO t2 VALUES (@@session.sql_mode);
61
INSERT INTO t2 VALUES (@@global.init_slave);
62
INSERT INTO t2 VALUES (@@hostname);
69
Note 1592 Statement may not be safe to log in statement format.
70
Note 1592 Statement may not be safe to log in statement format.
71
Note 1592 Statement may not be safe to log in statement format.
72
Note 1592 Statement may not be safe to log in statement format.
73
Note 1592 Statement may not be safe to log in statement format.
74
Note 1592 Statement may not be safe to log in statement format.
75
Note 1592 Statement may not be safe to log in statement format.
76
---- Insert from trigger ----
78
BEFORE INSERT ON trigger_table
81
INSERT INTO t1 VALUES (@@global.sync_binlog);
82
INSERT INTO t1 VALUES (@@session.insert_id);
83
INSERT INTO t1 VALUES (@@global.auto_increment_increment);
84
INSERT INTO t2 SELECT UUID();
85
INSERT INTO t2 VALUES (@@session.sql_mode);
86
INSERT INTO t2 VALUES (@@global.init_slave);
87
INSERT INTO t2 VALUES (@@hostname);
89
INSERT INTO trigger_table VALUES ('bye.');
91
Note 1592 Statement may not be safe to log in statement format.
92
Note 1592 Statement may not be safe to log in statement format.
93
Note 1592 Statement may not be safe to log in statement format.
94
Note 1592 Statement may not be safe to log in statement format.
95
Note 1592 Statement may not be safe to log in statement format.
96
Note 1592 Statement may not be safe to log in statement format.
97
Note 1592 Statement may not be safe to log in statement format.
98
Note 1592 Statement may not be safe to log in statement format.
99
---- Insert from prepared statement ----
100
PREPARE p1 FROM 'INSERT INTO t1 VALUES (@@global.sync_binlog)';
101
PREPARE p2 FROM 'INSERT INTO t1 VALUES (@@session.insert_id)';
102
PREPARE p3 FROM 'INSERT INTO t1 VALUES (@@global.auto_increment_increment)';
103
PREPARE p4 FROM 'INSERT INTO t2 SELECT UUID()';
104
PREPARE p5 FROM 'INSERT INTO t2 VALUES (@@session.sql_mode)';
105
PREPARE p6 FROM 'INSERT INTO t2 VALUES (@@global.init_slave)';
106
PREPARE p7 FROM 'INSERT INTO t2 VALUES (@@hostname)';
109
Note 1592 Statement may not be safe to log in statement format.
112
Note 1592 Statement may not be safe to log in statement format.
115
Note 1592 Statement may not be safe to log in statement format.
118
Note 1592 Statement may not be safe to log in statement format.
121
Note 1592 Statement may not be safe to log in statement format.
124
Note 1592 Statement may not be safe to log in statement format.
127
Note 1592 Statement may not be safe to log in statement format.
128
---- Insert from nested call of triggers / functions / procedures ----
129
CREATE PROCEDURE proc1()
130
INSERT INTO trigger_table VALUES ('ha!')|
131
CREATE FUNCTION func2()
138
BEFORE INSERT ON trigger_table2
142
SELECT func2() INTO tmp;
144
CREATE PROCEDURE proc4()
145
INSERT INTO trigger_table2 VALUES (1)|
146
CREATE FUNCTION func5()
152
PREPARE prep6 FROM 'SELECT func5()'|
157
Note 1592 Statement may not be safe to log in statement format.
158
Note 1592 Statement may not be safe to log in statement format.
159
Note 1592 Statement may not be safe to log in statement format.
160
Note 1592 Statement may not be safe to log in statement format.
161
Note 1592 Statement may not be safe to log in statement format.
162
Note 1592 Statement may not be safe to log in statement format.
163
Note 1592 Statement may not be safe to log in statement format.
164
==== Variables that should *not* be unsafe ====
165
INSERT INTO t1 VALUES (@@session.pseudo_thread_id);
166
INSERT INTO t1 VALUES (@@session.pseudo_thread_id);
167
INSERT INTO t1 VALUES (@@session.foreign_key_checks);
168
INSERT INTO t1 VALUES (@@session.sql_auto_is_null);
169
INSERT INTO t1 VALUES (@@session.unique_checks);
170
INSERT INTO t1 VALUES (@@session.auto_increment_increment);
171
INSERT INTO t1 VALUES (@@session.auto_increment_offset);
172
INSERT INTO t2 VALUES (@@session.character_set_client);
173
INSERT INTO t2 VALUES (@@session.collation_connection);
174
INSERT INTO t2 VALUES (@@session.collation_server);
175
INSERT INTO t2 VALUES (@@session.time_zone);
176
INSERT INTO t2 VALUES (@@session.lc_time_names);
177
INSERT INTO t2 VALUES (@@session.collation_database);
178
INSERT INTO t2 VALUES (@@session.timestamp);
179
INSERT INTO t2 VALUES (@@session.last_insert_id);
181
INSERT INTO t1 VALUES (@my_var);
183
INSERT INTO t3 VALUES (NULL);
188
DROP PROCEDURE proc1;
191
DROP PROCEDURE proc4;
194
DROP TABLE t1, t2, t3, trigger_table, trigger_table2;
195
CREATE TABLE t1(a INT, b INT, KEY(a), PRIMARY KEY(b));
196
INSERT INTO t1 SELECT * FROM t1 LIMIT 1;
198
Note 1592 Statement may not be safe to log in statement format.
199
REPLACE INTO t1 SELECT * FROM t1 LIMIT 1;
201
Note 1592 Statement may not be safe to log in statement format.
202
UPDATE t1 SET a=1 LIMIT 1;
204
Note 1592 Statement may not be safe to log in statement format.
205
DELETE FROM t1 LIMIT 1;
207
Note 1592 Statement may not be safe to log in statement format.
208
CREATE PROCEDURE p1()
210
INSERT INTO t1 SELECT * FROM t1 LIMIT 1;
211
REPLACE INTO t1 SELECT * FROM t1 LIMIT 1;
212
UPDATE t1 SET a=1 LIMIT 1;
213
DELETE FROM t1 LIMIT 1;
217
Note 1592 Statement may not be safe to log in statement format.
218
Note 1592 Statement may not be safe to log in statement format.
219
Note 1592 Statement may not be safe to log in statement format.
220
Note 1592 Statement may not be safe to log in statement format.
223
DROP TABLE IF EXISTS t1;
224
CREATE TABLE t1 (a VARCHAR(100), b VARCHAR(100));
225
INSERT INTO t1 VALUES ('a','b');
226
UPDATE t1 SET b = '%s%s%s%s%s%s%s%s%s%s%s%s%s%s' WHERE a = 'a' LIMIT 1;
228
Note 1592 Statement may not be safe to log in statement format.
230
DROP TABLE IF EXISTS t1, t2;
231
CREATE TABLE t1(i INT PRIMARY KEY);
232
CREATE TABLE t2(i INT PRIMARY KEY);
233
CREATE TABLE t3(i INT, ch CHAR(50));
234
"Should issue message Statement may not be safe to log in statement format."
235
INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
237
Note 1592 Statement may not be safe to log in statement format.
238
CREATE FUNCTION func6()
241
INSERT INTO t1 VALUES (10);
242
INSERT INTO t1 VALUES (11);
243
INSERT INTO t1 VALUES (12);
246
"Should issue message Statement may not be safe to log in statement format only once"
247
INSERT INTO t3 VALUES(func6(), UUID());
249
Note 1592 Statement may not be safe to log in statement format.
250
"Check whether SET @@SQL_LOG_BIN = 0/1 doesn't work in substatements"
251
CREATE FUNCTION fun_check_log_bin() RETURNS INT
253
SET @@SQL_LOG_BIN = 0;
254
INSERT INTO t1 VALUES(@@global.sync_binlog);
257
"One unsafe warning should be issued in the following statement"
258
SELECT fun_check_log_bin();
262
Note 1592 Statement may not be safe to log in statement format.
263
"SQL_LOG_BIN should be ON still"
264
SHOW VARIABLES LIKE "SQL_LOG_BIN";
267
set @save_log_bin = @@SESSION.SQL_LOG_BIN;
268
set @@SESSION.SQL_LOG_BIN = 0;
269
"Should NOT have any warning message issued in the following statements"
270
INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
272
"Should NOT have any warning message issued in the following func7() and trig"
273
CREATE TABLE t1 (a INT);
274
CREATE TABLE t2 (a CHAR(40));
275
CREATE TABLE trigger_table (a CHAR(7));
276
CREATE FUNCTION func7()
279
INSERT INTO t1 VALUES (@@global.sync_binlog);
280
INSERT INTO t1 VALUES (@@session.insert_id);
281
INSERT INTO t2 SELECT UUID();
282
INSERT INTO t2 VALUES (@@session.sql_mode);
283
INSERT INTO t2 VALUES (@@global.init_slave);
286
SHOW VARIABLES LIKE "SQL_LOG_BIN";
292
---- Insert from trigger ----
294
BEFORE INSERT ON trigger_table
297
INSERT INTO t1 VALUES (@@global.sync_binlog);
298
INSERT INTO t1 VALUES (@@session.insert_id);
299
INSERT INTO t1 VALUES (@@global.auto_increment_increment);
300
INSERT INTO t2 SELECT UUID();
301
INSERT INTO t2 VALUES (@@session.sql_mode);
302
INSERT INTO t2 VALUES (@@global.init_slave);
303
INSERT INTO t2 VALUES (@@hostname);
305
INSERT INTO trigger_table VALUES ('bye.');
306
DROP FUNCTION fun_check_log_bin;
310
DROP TABLE t1, t2, t3, trigger_table;
311
set @@SESSION.SQL_LOG_BIN = @save_log_bin;
312
SET @save_sql_mode = @@SESSION.SQL_MODE;
313
SET @@SESSION.SQL_MODE = STRICT_ALL_TABLES;
314
CREATE TABLE t1(i INT PRIMARY KEY);
315
CREATE TABLE t2(i INT PRIMARY KEY);
316
INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
318
Note 1592 Statement may not be safe to log in statement format.
319
INSERT INTO t1 VALUES(@@global.sync_binlog);
321
Note 1592 Statement may not be safe to log in statement format.
322
UPDATE t1 SET i = 999 LIMIT 1;
324
Note 1592 Statement may not be safe to log in statement format.
325
DELETE FROM t1 LIMIT 1;
327
Note 1592 Statement may not be safe to log in statement format.
329
SET @@SESSION.SQL_MODE = @save_sql_mode;
330
CREATE TABLE t1 (a VARCHAR(1000));
331
INSERT INTO t1 VALUES (CURRENT_USER());
333
Note 1592 Statement may not be safe to log in statement format.
334
INSERT INTO t1 VALUES (FOUND_ROWS());
336
Note 1592 Statement may not be safe to log in statement format.
337
INSERT INTO t1 VALUES (GET_LOCK('tmp', 1));
339
Note 1592 Statement may not be safe to log in statement format.
340
INSERT INTO t1 VALUES (IS_FREE_LOCK('tmp'));
342
Note 1592 Statement may not be safe to log in statement format.
343
INSERT INTO t1 VALUES (IS_USED_LOCK('tmp'));
345
Note 1592 Statement may not be safe to log in statement format.
346
INSERT INTO t1 VALUES (LOAD_FILE('../../std_data/words2.dat'));
348
Note 1592 Statement may not be safe to log in statement format.
349
INSERT INTO t1 VALUES (MASTER_POS_WAIT('dummy arg', 4711, 1));
351
Note 1592 Statement may not be safe to log in statement format.
352
INSERT INTO t1 VALUES (RELEASE_LOCK('tmp'));
354
Note 1592 Statement may not be safe to log in statement format.
355
INSERT INTO t1 VALUES (ROW_COUNT());
357
Note 1592 Statement may not be safe to log in statement format.
358
INSERT INTO t1 VALUES (SESSION_USER());
360
Note 1592 Statement may not be safe to log in statement format.
361
INSERT INTO t1 VALUES (SLEEP(1));
363
Note 1592 Statement may not be safe to log in statement format.
364
INSERT INTO t1 VALUES (SYSDATE());
366
Note 1592 Statement may not be safe to log in statement format.
367
INSERT INTO t1 VALUES (SYSTEM_USER());
369
Note 1592 Statement may not be safe to log in statement format.
370
INSERT INTO t1 VALUES (USER());
372
Note 1592 Statement may not be safe to log in statement format.
373
INSERT INTO t1 VALUES (UUID());
375
Note 1592 Statement may not be safe to log in statement format.
376
INSERT INTO t1 VALUES (UUID_SHORT());
378
Note 1592 Statement may not be safe to log in statement format.
379
INSERT INTO t1 VALUES (VERSION());
381
Note 1592 Statement may not be safe to log in statement format.
382
INSERT INTO t1 VALUES (RAND());
384
Note 1592 Statement may not be safe to log in statement format.
386
SET TIMESTAMP=1000000;
387
INSERT INTO t1 VALUES
391
(CURRENT_TIMESTAMP()),