binlog_unsafe.result 11.1 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12
==== Setup tables ====
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a CHAR(40));
CREATE TABLE t3 (a INT AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE trigger_table (a CHAR(7));
CREATE TABLE trigger_table2 (a INT);
==== Non-deterministic statements ====
INSERT DELAYED INTO t1 VALUES (5);
==== Some variables that *should* be unsafe ====
---- Insert directly ----
INSERT INTO t1 VALUES (@@global.sync_binlog);
Warnings:
13
Note	1592	Statement is not safe to log in statement format.
14 15
INSERT INTO t1 VALUES (@@session.insert_id);
Warnings:
16
Note	1592	Statement is not safe to log in statement format.
17 18
INSERT INTO t1 VALUES (@@global.auto_increment_increment);
Warnings:
19
Note	1592	Statement is not safe to log in statement format.
20 21
INSERT INTO t2 SELECT UUID();
Warnings:
22
Note	1592	Statement is not safe to log in statement format.
23 24
INSERT INTO t2 VALUES (@@session.sql_mode);
Warnings:
25
Note	1592	Statement is not safe to log in statement format.
26 27
INSERT INTO t2 VALUES (@@global.init_slave);
Warnings:
28
Note	1592	Statement is not safe to log in statement format.
29 30
INSERT INTO t2 VALUES (@@hostname);
Warnings:
31
Note	1592	Statement is not safe to log in statement format.
32 33 34 35 36 37 38
---- Insert from stored procedure ----
CREATE PROCEDURE proc()
BEGIN
INSERT INTO t1 VALUES (@@global.sync_binlog);
INSERT INTO t1 VALUES (@@session.insert_id);
INSERT INTO t1 VALUES (@@global.auto_increment_increment);
INSERT INTO t2 SELECT UUID();
39
INSERT INTO t2 VALUES (@@session.sql_mode);
40 41 42 43 44
INSERT INTO t2 VALUES (@@global.init_slave);
INSERT INTO t2 VALUES (@@hostname);
END|
CALL proc();
Warnings:
45 46 47 48 49 50 51
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
52 53 54 55 56 57 58 59
---- Insert from stored function ----
CREATE FUNCTION func()
RETURNS INT
BEGIN
INSERT INTO t1 VALUES (@@global.sync_binlog);
INSERT INTO t1 VALUES (@@session.insert_id);
INSERT INTO t1 VALUES (@@global.auto_increment_increment);
INSERT INTO t2 SELECT UUID();
60
INSERT INTO t2 VALUES (@@session.sql_mode);
61 62 63 64 65 66 67 68
INSERT INTO t2 VALUES (@@global.init_slave);
INSERT INTO t2 VALUES (@@hostname);
RETURN 0;
END|
SELECT func();
func()
0
Warnings:
69 70 71 72 73 74 75
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
76 77 78 79 80 81 82 83 84
---- Insert from trigger ----
CREATE TRIGGER trig
BEFORE INSERT ON trigger_table
FOR EACH ROW
BEGIN
INSERT INTO t1 VALUES (@@global.sync_binlog);
INSERT INTO t1 VALUES (@@session.insert_id);
INSERT INTO t1 VALUES (@@global.auto_increment_increment);
INSERT INTO t2 SELECT UUID();
85
INSERT INTO t2 VALUES (@@session.sql_mode);
86 87 88 89 90
INSERT INTO t2 VALUES (@@global.init_slave);
INSERT INTO t2 VALUES (@@hostname);
END|
INSERT INTO trigger_table VALUES ('bye.');
Warnings:
91 92 93 94 95 96 97 98
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
99 100 101 102 103
---- Insert from prepared statement ----
PREPARE p1 FROM 'INSERT INTO t1 VALUES (@@global.sync_binlog)';
PREPARE p2 FROM 'INSERT INTO t1 VALUES (@@session.insert_id)';
PREPARE p3 FROM 'INSERT INTO t1 VALUES (@@global.auto_increment_increment)';
PREPARE p4 FROM 'INSERT INTO t2 SELECT UUID()';
104 105 106
PREPARE p5 FROM 'INSERT INTO t2 VALUES (@@session.sql_mode)';
PREPARE p6 FROM 'INSERT INTO t2 VALUES (@@global.init_slave)';
PREPARE p7 FROM 'INSERT INTO t2 VALUES (@@hostname)';
107 108
EXECUTE p1;
Warnings:
109
Note	1592	Statement is not safe to log in statement format.
110 111
EXECUTE p2;
Warnings:
112
Note	1592	Statement is not safe to log in statement format.
113 114
EXECUTE p3;
Warnings:
115
Note	1592	Statement is not safe to log in statement format.
116 117
EXECUTE p4;
Warnings:
118
Note	1592	Statement is not safe to log in statement format.
119 120
EXECUTE p5;
Warnings:
121
Note	1592	Statement is not safe to log in statement format.
122 123
EXECUTE p6;
Warnings:
124
Note	1592	Statement is not safe to log in statement format.
125 126
EXECUTE p7;
Warnings:
127
Note	1592	Statement is not safe to log in statement format.
128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156
---- Insert from nested call of triggers / functions / procedures ----
CREATE PROCEDURE proc1()
INSERT INTO trigger_table VALUES ('ha!')|
CREATE FUNCTION func2()
RETURNS INT
BEGIN
CALL proc1();
RETURN 0;
END|
CREATE TRIGGER trig3
BEFORE INSERT ON trigger_table2
FOR EACH ROW
BEGIN
DECLARE tmp INT;
SELECT func2() INTO tmp;
END|
CREATE PROCEDURE proc4()
INSERT INTO trigger_table2 VALUES (1)|
CREATE FUNCTION func5()
RETURNS INT
BEGIN
CALL proc4;
RETURN 0;
END|
PREPARE prep6 FROM 'SELECT func5()'|
EXECUTE prep6;
func5()
0
Warnings:
157 158 159 160 161 162 163
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194
==== Variables that should *not* be unsafe ====
INSERT INTO t1 VALUES (@@session.pseudo_thread_id);
INSERT INTO t1 VALUES (@@session.pseudo_thread_id);
INSERT INTO t1 VALUES (@@session.foreign_key_checks);
INSERT INTO t1 VALUES (@@session.sql_auto_is_null);
INSERT INTO t1 VALUES (@@session.unique_checks);
INSERT INTO t1 VALUES (@@session.auto_increment_increment);
INSERT INTO t1 VALUES (@@session.auto_increment_offset);
INSERT INTO t2 VALUES (@@session.character_set_client);
INSERT INTO t2 VALUES (@@session.collation_connection);
INSERT INTO t2 VALUES (@@session.collation_server);
INSERT INTO t2 VALUES (@@session.time_zone);
INSERT INTO t2 VALUES (@@session.lc_time_names);
INSERT INTO t2 VALUES (@@session.collation_database);
INSERT INTO t2 VALUES (@@session.timestamp);
INSERT INTO t2 VALUES (@@session.last_insert_id);
SET @my_var= 4711;
INSERT INTO t1 VALUES (@my_var);
SET insert_id=12;
INSERT INTO t3 VALUES (NULL);
==== Clean up ====
DROP PROCEDURE proc;
DROP FUNCTION  func;
DROP TRIGGER   trig;
DROP PROCEDURE proc1;
DROP FUNCTION  func2;
DROP TRIGGER   trig3;
DROP PROCEDURE proc4;
DROP FUNCTION  func5;
DROP PREPARE   prep6;
DROP TABLE t1, t2, t3, trigger_table, trigger_table2;
195 196 197
CREATE TABLE t1(a INT, b INT, KEY(a), PRIMARY KEY(b));
INSERT INTO t1 SELECT * FROM t1 LIMIT 1;
Warnings:
198
Note	1592	Statement is not safe to log in statement format.
199 200
REPLACE INTO t1 SELECT * FROM t1 LIMIT 1;
Warnings:
201
Note	1592	Statement is not safe to log in statement format.
202 203
UPDATE t1 SET a=1 LIMIT 1;
Warnings:
204
Note	1592	Statement is not safe to log in statement format.
205 206
DELETE FROM t1 LIMIT 1;
Warnings:
207
Note	1592	Statement is not safe to log in statement format.
208 209 210 211 212 213 214 215 216
CREATE PROCEDURE p1()
BEGIN
INSERT INTO t1 SELECT * FROM t1 LIMIT 1;
REPLACE INTO t1 SELECT * FROM t1 LIMIT 1;
UPDATE t1 SET a=1 LIMIT 1;
DELETE FROM t1 LIMIT 1;
END|
CALL p1();
Warnings:
217 218 219 220
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
Note	1592	Statement is not safe to log in statement format.
221 222
DROP PROCEDURE p1;
DROP TABLE t1;
223 224 225 226 227
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(100), b VARCHAR(100));
INSERT INTO t1 VALUES ('a','b');
UPDATE t1 SET b = '%s%s%s%s%s%s%s%s%s%s%s%s%s%s' WHERE a = 'a' LIMIT 1;
Warnings:
228
Note	1592	Statement is not safe to log in statement format.
229
DROP TABLE t1;
230 231 232 233 234 235 236
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1(i INT PRIMARY KEY);
CREATE TABLE t2(i INT PRIMARY KEY);
CREATE TABLE t3(i INT, ch CHAR(50));
"Should issue message Statement is not safe to log in statement format."
INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
Warnings:
237
Note	1592	Statement is not safe to log in statement format.
238 239 240 241 242 243 244 245 246 247 248
CREATE FUNCTION func6()
RETURNS INT
BEGIN
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (11);
INSERT INTO t1 VALUES (12);
RETURN 0;
END|
"Should issue message Statement is not safe to log in statement format only once"
INSERT INTO t3 VALUES(func6(), UUID());
Warnings:
249
Note	1592	Statement is not safe to log in statement format.
250 251 252 253 254 255 256 257 258 259 260 261
"Check whether SET @@SQL_LOG_BIN = 0/1 doesn't work in substatements"
CREATE FUNCTION fun_check_log_bin() RETURNS INT
BEGIN
SET @@SQL_LOG_BIN = 0;
INSERT INTO t1 VALUES(@@global.sync_binlog);
RETURN 100;
END|
"One unsafe warning should be issued in the following statement"
SELECT fun_check_log_bin();
fun_check_log_bin()
100
Warnings:
262
Note	1592	Statement is not safe to log in statement format.
263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311
"SQL_LOG_BIN should be ON still"
SHOW VARIABLES LIKE "SQL_LOG_BIN";
Variable_name	Value
sql_log_bin	ON
set @save_log_bin = @@SESSION.SQL_LOG_BIN;
set @@SESSION.SQL_LOG_BIN = 0;
"Should NOT have any warning message issued in the following statements"
INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
DROP TABLE t1,t2;
"Should NOT have any warning message issued in the following func7() and trig"
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a CHAR(40));
CREATE TABLE trigger_table (a CHAR(7));
CREATE FUNCTION func7()
RETURNS INT
BEGIN
INSERT INTO t1 VALUES (@@global.sync_binlog);
INSERT INTO t1 VALUES (@@session.insert_id);
INSERT INTO t2 SELECT UUID();
INSERT INTO t2 VALUES (@@session.sql_mode);
INSERT INTO t2 VALUES (@@global.init_slave);
RETURN 0;
END|
SHOW VARIABLES LIKE "SQL_LOG_BIN";
Variable_name	Value
sql_log_bin	OFF
SELECT func7();
func7()
0
---- Insert from trigger ----
CREATE TRIGGER trig
BEFORE INSERT ON trigger_table
FOR EACH ROW
BEGIN
INSERT INTO t1 VALUES (@@global.sync_binlog);
INSERT INTO t1 VALUES (@@session.insert_id);
INSERT INTO t1 VALUES (@@global.auto_increment_increment);
INSERT INTO t2 SELECT UUID();
INSERT INTO t2 VALUES (@@session.sql_mode);
INSERT INTO t2 VALUES (@@global.init_slave);
INSERT INTO t2 VALUES (@@hostname);
END|
INSERT INTO trigger_table VALUES ('bye.');
DROP FUNCTION fun_check_log_bin;
DROP FUNCTION func6;
DROP FUNCTION func7;
DROP TRIGGER  trig;
DROP TABLE t1, t2, t3, trigger_table;
set @@SESSION.SQL_LOG_BIN = @save_log_bin;
312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329
SET @save_sql_mode = @@SESSION.SQL_MODE;
SET @@SESSION.SQL_MODE = STRICT_ALL_TABLES;
CREATE TABLE t1(i INT PRIMARY KEY);
CREATE TABLE t2(i INT PRIMARY KEY);
INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
Warnings:
Note	1592	Statement is not safe to log in statement format.
INSERT INTO t1 VALUES(@@global.sync_binlog);
Warnings:
Note	1592	Statement is not safe to log in statement format.
UPDATE t1 SET i = 999 LIMIT 1;
Warnings:
Note	1592	Statement is not safe to log in statement format.
DELETE FROM t1 LIMIT 1;
Warnings:
Note	1592	Statement is not safe to log in statement format.
DROP TABLE t1, t2;
SET @@SESSION.SQL_MODE = @save_sql_mode;
330
"End of tests"