binlog_unsafe.result 10.6 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
==== 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:
Warning	1592	Statement is not safe to log in statement format.
INSERT INTO t1 VALUES (@@session.insert_id);
Warnings:
Warning	1592	Statement is not safe to log in statement format.
INSERT INTO t1 VALUES (@@global.auto_increment_increment);
Warnings:
Warning	1592	Statement is not safe to log in statement format.
INSERT INTO t2 SELECT UUID();
Warnings:
Warning	1592	Statement is not safe to log in statement format.
23 24 25
INSERT INTO t2 VALUES (@@session.sql_mode);
Warnings:
Warning	1592	Statement is not safe to log in statement format.
26 27 28 29 30 31 32 33 34 35 36 37 38
INSERT INTO t2 VALUES (@@global.init_slave);
Warnings:
Warning	1592	Statement is not safe to log in statement format.
INSERT INTO t2 VALUES (@@hostname);
Warnings:
Warning	1592	Statement is not safe to log in statement format.
---- 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 45 46 47 48 49 50
INSERT INTO t2 VALUES (@@global.init_slave);
INSERT INTO t2 VALUES (@@hostname);
END|
CALL proc();
Warnings:
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
51
Warning	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 69 70 71 72 73 74
INSERT INTO t2 VALUES (@@global.init_slave);
INSERT INTO t2 VALUES (@@hostname);
RETURN 0;
END|
SELECT func();
func()
0
Warnings:
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
75
Warning	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 91 92 93 94 95 96 97
INSERT INTO t2 VALUES (@@global.init_slave);
INSERT INTO t2 VALUES (@@hostname);
END|
INSERT INTO trigger_table VALUES ('bye.');
Warnings:
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
98
Warning	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 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124
EXECUTE p1;
Warnings:
Warning	1592	Statement is not safe to log in statement format.
EXECUTE p2;
Warnings:
Warning	1592	Statement is not safe to log in statement format.
EXECUTE p3;
Warnings:
Warning	1592	Statement is not safe to log in statement format.
EXECUTE p4;
Warnings:
Warning	1592	Statement is not safe to log in statement format.
EXECUTE p5;
Warnings:
Warning	1592	Statement is not safe to log in statement format.
EXECUTE p6;
Warnings:
Warning	1592	Statement is not safe to log in statement format.
125 126 127
EXECUTE p7;
Warnings:
Warning	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 157 158 159 160 161 162
---- 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:
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
163
Warning	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 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222
CREATE TABLE t1(a INT, b INT, KEY(a), PRIMARY KEY(b));
INSERT INTO t1 SELECT * FROM t1 LIMIT 1;
Warnings:
Warning	1592	Statement is not safe to log in statement format.
REPLACE INTO t1 SELECT * FROM t1 LIMIT 1;
Warnings:
Warning	1592	Statement is not safe to log in statement format.
UPDATE t1 SET a=1 LIMIT 1;
Warnings:
Warning	1592	Statement is not safe to log in statement format.
DELETE FROM t1 LIMIT 1;
Warnings:
Warning	1592	Statement is not safe to log in statement format.
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:
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
Warning	1592	Statement is not safe to log in statement format.
DROP PROCEDURE p1;
DROP TABLE t1;
223 224 225 226 227 228 229
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:
Warning	1592	Statement is not safe to log in statement format.
DROP TABLE t1;
230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 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 312
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:
Warning	1592	Statement is not safe to log in statement format.
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:
Warning	1592	Statement is not safe to log in statement format.
"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:
Warning	1592	Statement is not safe to log in statement format.
"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;
"End of tests"