3
# Some statements can not be written to the binlog in a safe manner
4
# with statement-based replication, either because they rely on
5
# features that are local to the server they are replicated from
6
# (e.g., @@variables), or because they include nondeterministic
7
# queries (e.g., LIMIT), or because the time at which the query is
8
# executed cannot be determined (e.g., INSERT DELAYED). Such
9
# statements should be marked unsafe. All unsafe statements should
11
# Yet the warning/error message isn't issued when SQL_LOG_BIN is turned off.
13
# This test verifies that a warning is generated for statements that
14
# should be unsafe, when they are executed under statement mode
17
# All variables should be unsafe, with some exceptions. Therefore,
18
# this test also verifies that the exceptions do *not* generare a
24
# We try an INSERT DELAYED statement and verify that a warning is
27
# We try to insert unsafe variables into a table in several ways:
28
# directly with an INSERT statement, from a stored procedure, from a
29
# stored function, from a trigger, from a prepared statement, and from
30
# a complicated nesting of triggers, functions, procedures, and
31
# prepared statements. In all cases, a warning should be issued.
33
# We try to insert the variables that should not be unsafe into a
34
# table, and verify that *no* warning is issued.
36
# Execute a unsafe statement calling a trigger or stored function
37
# or neither when SQL_LOG_BIN is turned ON, a warning/error should be issued
38
# Execute a unsafe statement calling a trigger or stored function
39
# or neither when @@SQL_LOG_BIN is turned OFF,
40
# no warning/error is issued
42
# ==== Related bugs and worklogs ====
44
# WL#3339: Issue warnings when statement-based replication may fail
45
# BUG#31168: @@hostname does not replicate
46
# BUG#34732: mysqlbinlog does not print default values for auto_increment variables
47
# BUG#34768: nondeterministic INSERT using LIMIT logged in stmt mode if binlog_format=mixed
48
# BUG#41980, SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0
49
# BUG#42640: mysqld crashes when unsafe statements are executed (STRICT_TRANS_TABLES mode)
50
# BUG#47995: Mark user functions as unsafe
51
# BUG#49222: Mare RAND() unsafe
53
# ==== Related test cases ====
55
# rpl.rpl_variables verifies that variables which cannot be replicated
56
# safely in statement mode are replicated correctly in mixed or row
59
# rpl.rpl_variables_stm tests the small subset of variables that
60
# actually can be replicated safely in statement mode.
65
# There are several other ways to create unsafe statements: see, e.g.,
68
source include/have_log_bin.inc;
69
source include/have_binlog_format_statement.inc;
71
--echo ==== Setup tables ====
73
CREATE TABLE t1 (a INT);
74
CREATE TABLE t2 (a CHAR(40));
75
CREATE TABLE t3 (a INT AUTO_INCREMENT PRIMARY KEY);
76
CREATE TABLE trigger_table (a CHAR(7));
77
CREATE TABLE trigger_table2 (a INT);
80
--echo ==== Non-deterministic statements ====
82
INSERT DELAYED INTO t1 VALUES (5);
85
--echo ==== Some variables that *should* be unsafe ====
87
--echo ---- Insert directly ----
89
INSERT INTO t1 VALUES (@@global.sync_binlog);
90
INSERT INTO t1 VALUES (@@session.insert_id);
91
INSERT INTO t1 VALUES (@@global.auto_increment_increment);
92
INSERT INTO t2 SELECT UUID();
93
INSERT INTO t2 VALUES (@@session.sql_mode);
94
INSERT INTO t2 VALUES (@@global.init_slave);
95
INSERT INTO t2 VALUES (@@hostname);
97
--echo ---- Insert from stored procedure ----
100
CREATE PROCEDURE proc()
102
INSERT INTO t1 VALUES (@@global.sync_binlog);
103
INSERT INTO t1 VALUES (@@session.insert_id);
104
INSERT INTO t1 VALUES (@@global.auto_increment_increment);
105
INSERT INTO t2 SELECT UUID();
106
INSERT INTO t2 VALUES (@@session.sql_mode);
107
INSERT INTO t2 VALUES (@@global.init_slave);
108
INSERT INTO t2 VALUES (@@hostname);
114
--echo ---- Insert from stored function ----
117
CREATE FUNCTION func()
120
INSERT INTO t1 VALUES (@@global.sync_binlog);
121
INSERT INTO t1 VALUES (@@session.insert_id);
122
INSERT INTO t1 VALUES (@@global.auto_increment_increment);
123
INSERT INTO t2 SELECT UUID();
124
INSERT INTO t2 VALUES (@@session.sql_mode);
125
INSERT INTO t2 VALUES (@@global.init_slave);
126
INSERT INTO t2 VALUES (@@hostname);
133
--echo ---- Insert from trigger ----
137
BEFORE INSERT ON trigger_table
140
INSERT INTO t1 VALUES (@@global.sync_binlog);
141
INSERT INTO t1 VALUES (@@session.insert_id);
142
INSERT INTO t1 VALUES (@@global.auto_increment_increment);
143
INSERT INTO t2 SELECT UUID();
144
INSERT INTO t2 VALUES (@@session.sql_mode);
145
INSERT INTO t2 VALUES (@@global.init_slave);
146
INSERT INTO t2 VALUES (@@hostname);
150
INSERT INTO trigger_table VALUES ('bye.');
152
--echo ---- Insert from prepared statement ----
154
PREPARE p1 FROM 'INSERT INTO t1 VALUES (@@global.sync_binlog)';
155
PREPARE p2 FROM 'INSERT INTO t1 VALUES (@@session.insert_id)';
156
PREPARE p3 FROM 'INSERT INTO t1 VALUES (@@global.auto_increment_increment)';
157
PREPARE p4 FROM 'INSERT INTO t2 SELECT UUID()';
158
PREPARE p5 FROM 'INSERT INTO t2 VALUES (@@session.sql_mode)';
159
PREPARE p6 FROM 'INSERT INTO t2 VALUES (@@global.init_slave)';
160
PREPARE p7 FROM 'INSERT INTO t2 VALUES (@@hostname)';
162
EXECUTE p1; EXECUTE p2; EXECUTE p3; EXECUTE p4; EXECUTE p5;
163
EXECUTE p6; EXECUTE p7;
165
--echo ---- Insert from nested call of triggers / functions / procedures ----
169
# proc1: cause trigger 'trig' above to be triggered.
170
CREATE PROCEDURE proc1()
171
INSERT INTO trigger_table VALUES ('ha!')|
173
# func2: call proc1 above.
174
CREATE FUNCTION func2()
181
# trig3: call func2 above
183
BEFORE INSERT ON trigger_table2
187
SELECT func2() INTO tmp;
190
# proc4: cause trig3 above to be triggered.
191
CREATE PROCEDURE proc4()
192
INSERT INTO trigger_table2 VALUES (1)|
194
# func5: call proc4 above.
195
CREATE FUNCTION func5()
202
# prep6: call func5() above.
203
PREPARE prep6 FROM 'SELECT func5()'|
207
# try a complicated call path to trigger 'trig'.
211
--echo ==== Variables that should *not* be unsafe ====
213
INSERT INTO t1 VALUES (@@session.pseudo_thread_id);
214
INSERT INTO t1 VALUES (@@session.pseudo_thread_id);
215
INSERT INTO t1 VALUES (@@session.foreign_key_checks);
216
INSERT INTO t1 VALUES (@@session.sql_auto_is_null);
217
INSERT INTO t1 VALUES (@@session.unique_checks);
218
INSERT INTO t1 VALUES (@@session.auto_increment_increment);
219
INSERT INTO t1 VALUES (@@session.auto_increment_offset);
220
INSERT INTO t2 VALUES (@@session.character_set_client);
221
INSERT INTO t2 VALUES (@@session.collation_connection);
222
INSERT INTO t2 VALUES (@@session.collation_server);
223
INSERT INTO t2 VALUES (@@session.time_zone);
224
INSERT INTO t2 VALUES (@@session.lc_time_names);
225
INSERT INTO t2 VALUES (@@session.collation_database);
226
INSERT INTO t2 VALUES (@@session.timestamp);
227
INSERT INTO t2 VALUES (@@session.last_insert_id);
229
INSERT INTO t1 VALUES (@my_var);
231
# using insert_id implicitly should be ok.
233
INSERT INTO t3 VALUES (NULL);
236
--echo ==== Clean up ====
241
DROP PROCEDURE proc1;
244
DROP PROCEDURE proc4;
247
DROP TABLE t1, t2, t3, trigger_table, trigger_table2;
249
# BUG#34768 - nondeterministic INSERT using LIMIT logged in stmt mode if
250
# binlog_format=mixed
252
CREATE TABLE t1(a INT, b INT, KEY(a), PRIMARY KEY(b));
253
INSERT INTO t1 SELECT * FROM t1 LIMIT 1;
254
REPLACE INTO t1 SELECT * FROM t1 LIMIT 1;
255
UPDATE t1 SET a=1 LIMIT 1;
256
DELETE FROM t1 LIMIT 1;
258
CREATE PROCEDURE p1()
260
INSERT INTO t1 SELECT * FROM t1 LIMIT 1;
261
REPLACE INTO t1 SELECT * FROM t1 LIMIT 1;
262
UPDATE t1 SET a=1 LIMIT 1;
263
DELETE FROM t1 LIMIT 1;
271
# Bug#42634: % character in query can cause mysqld signal 11 segfault
275
DROP TABLE IF EXISTS t1;
278
CREATE TABLE t1 (a VARCHAR(100), b VARCHAR(100));
279
INSERT INTO t1 VALUES ('a','b');
280
UPDATE t1 SET b = '%s%s%s%s%s%s%s%s%s%s%s%s%s%s' WHERE a = 'a' LIMIT 1;
284
#For bug#41980, SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0
288
DROP TABLE IF EXISTS t1, t2;
290
CREATE TABLE t1(i INT PRIMARY KEY);
291
CREATE TABLE t2(i INT PRIMARY KEY);
292
CREATE TABLE t3(i INT, ch CHAR(50));
294
--echo "Should issue message Statement may not be safe to log in statement format."
295
INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
298
CREATE FUNCTION func6()
301
INSERT INTO t1 VALUES (10);
302
INSERT INTO t1 VALUES (11);
303
INSERT INTO t1 VALUES (12);
307
--echo "Should issue message Statement may not be safe to log in statement format only once"
308
INSERT INTO t3 VALUES(func6(), UUID());
310
--echo "Check whether SET @@SQL_LOG_BIN = 0/1 doesn't work in substatements"
312
CREATE FUNCTION fun_check_log_bin() RETURNS INT
314
SET @@SQL_LOG_BIN = 0;
315
INSERT INTO t1 VALUES(@@global.sync_binlog);
319
--echo "One unsafe warning should be issued in the following statement"
320
SELECT fun_check_log_bin();
321
--echo "SQL_LOG_BIN should be ON still"
322
SHOW VARIABLES LIKE "SQL_LOG_BIN";
324
set @save_log_bin = @@SESSION.SQL_LOG_BIN;
325
set @@SESSION.SQL_LOG_BIN = 0;
326
--echo "Should NOT have any warning message issued in the following statements"
327
INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
330
--echo "Should NOT have any warning message issued in the following func7() and trig"
331
CREATE TABLE t1 (a INT);
332
CREATE TABLE t2 (a CHAR(40));
333
CREATE TABLE trigger_table (a CHAR(7));
335
CREATE FUNCTION func7()
338
INSERT INTO t1 VALUES (@@global.sync_binlog);
339
INSERT INTO t1 VALUES (@@session.insert_id);
340
INSERT INTO t2 SELECT UUID();
341
INSERT INTO t2 VALUES (@@session.sql_mode);
342
INSERT INTO t2 VALUES (@@global.init_slave);
346
SHOW VARIABLES LIKE "SQL_LOG_BIN";
349
--echo ---- Insert from trigger ----
353
BEFORE INSERT ON trigger_table
356
INSERT INTO t1 VALUES (@@global.sync_binlog);
357
INSERT INTO t1 VALUES (@@session.insert_id);
358
INSERT INTO t1 VALUES (@@global.auto_increment_increment);
359
INSERT INTO t2 SELECT UUID();
360
INSERT INTO t2 VALUES (@@session.sql_mode);
361
INSERT INTO t2 VALUES (@@global.init_slave);
362
INSERT INTO t2 VALUES (@@hostname);
366
INSERT INTO trigger_table VALUES ('bye.');
369
DROP FUNCTION fun_check_log_bin;
373
DROP TABLE t1, t2, t3, trigger_table;
374
set @@SESSION.SQL_LOG_BIN = @save_log_bin;
377
# For BUG#42640: mysqld crashes when unsafe statements are executed (STRICT_TRANS_TABLES mode)
379
SET @save_sql_mode = @@SESSION.SQL_MODE;
380
SET @@SESSION.SQL_MODE = STRICT_ALL_TABLES;
382
CREATE TABLE t1(i INT PRIMARY KEY);
383
CREATE TABLE t2(i INT PRIMARY KEY);
385
INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
386
INSERT INTO t1 VALUES(@@global.sync_binlog);
388
UPDATE t1 SET i = 999 LIMIT 1;
389
DELETE FROM t1 LIMIT 1;
392
SET @@SESSION.SQL_MODE = @save_sql_mode;
395
# BUG#47995: Mark user functions as unsafe
396
# BUG#49222: Mare RAND() unsafe
398
# Test that the system functions that are supposed to be marked unsafe
399
# generate a warning. Each INSERT statement below should generate a
403
CREATE TABLE t1 (a VARCHAR(1000));
404
INSERT INTO t1 VALUES (CURRENT_USER()); #marked unsafe before BUG#47995
405
INSERT INTO t1 VALUES (FOUND_ROWS()); #marked unsafe before BUG#47995
406
INSERT INTO t1 VALUES (GET_LOCK('tmp', 1)); #marked unsafe in BUG#47995
407
INSERT INTO t1 VALUES (IS_FREE_LOCK('tmp')); #marked unsafe in BUG#47995
408
INSERT INTO t1 VALUES (IS_USED_LOCK('tmp')); #marked unsafe in BUG#47995
409
INSERT INTO t1 VALUES (LOAD_FILE('../../std_data/words2.dat')); #marked unsafe in BUG#39701
410
INSERT INTO t1 VALUES (MASTER_POS_WAIT('dummy arg', 4711, 1));
411
INSERT INTO t1 VALUES (RELEASE_LOCK('tmp')); #marked unsafe in BUG#47995
412
INSERT INTO t1 VALUES (ROW_COUNT()); #marked unsafe before BUG#47995
413
INSERT INTO t1 VALUES (SESSION_USER()); #marked unsafe before BUG#47995
414
INSERT INTO t1 VALUES (SLEEP(1)); #marked unsafe in BUG#47995
415
INSERT INTO t1 VALUES (SYSDATE()); #marked unsafe in BUG#47995
416
INSERT INTO t1 VALUES (SYSTEM_USER()); #marked unsafe before BUG#47995
417
INSERT INTO t1 VALUES (USER()); #marked unsafe before BUG#47995
418
INSERT INTO t1 VALUES (UUID()); #marked unsafe before BUG#47995
419
INSERT INTO t1 VALUES (UUID_SHORT()); #marked unsafe before BUG#47995
420
INSERT INTO t1 VALUES (VERSION()); #marked unsafe in BUG#47995
421
INSERT INTO t1 VALUES (RAND()); #marked unsafe in BUG#49222
424
# Since we replicate the TIMESTAMP variable, functions affected by the
425
# TIMESTAMP variable are safe to replicate. So we check that the
426
# following following functions that depend on the TIMESTAMP variable
427
# are not unsafe and don't generate a warning.
429
SET TIMESTAMP=1000000;
430
INSERT INTO t1 VALUES
434
(CURRENT_TIMESTAMP()),
447
--echo "End of tests"