Debian Codegraph @

mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test

raw
1
2 # BUG#42851: Spurious "Statement is not safe to log in statement
3 # format." warnings
4 #
5 # WHY
6 # ===
7 #
8 # This test aims at checking that the fix that removes spurious
9 # entries in the error log when the statement is filtered out from
10 # binlog, is working.
11 #
12 # HOW
13 # ===
14 #
15 # The test case is split into three assertions when issuing statements
16 # containing LIMIT and ORDER BY:
17 #
18 # i) issue statements in database that is not filtered => check
19 # that warnings ARE shown;
20 #
21 # ii) issue statements in database that is not filtered, but with
22 # binlog disabled => check that warnings ARE NOT shown;
23 #
24 # iii) issue statements in database that is filtered => check that
25 # warnings ARE NOT shown.
26
27 -- source include/have_log_bin.inc
28 -- source include/have_binlog_format_statement.inc
29 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.");
30
31 -- echo ### NOT filtered database => assertion: warnings ARE shown
32
33 -- disable_warnings
34 DROP TABLE IF EXISTS t1;
35 -- enable_warnings
36
37 CREATE TABLE t1 (a int, b int, primary key (a));
38 INSERT INTO t1 VALUES (1,2), (2,3);
39 UPDATE t1 SET b='4' WHERE a=1 LIMIT 1;
40 UPDATE t1 SET b='5' WHERE a=2 ORDER BY a LIMIT 1;
41 DROP TABLE t1;
42
43 -- echo ### NOT filtered database => assertion: binlog disabled and warnings ARE NOT shown
44
45 SET SQL_LOG_BIN= 0;
46
47 -- disable_warnings
48 DROP TABLE IF EXISTS t1;
49 -- enable_warnings
50
51 CREATE TABLE t1 (a int, b int, primary key (a));
52 INSERT INTO t1 VALUES (1,2), (2,3);
53 UPDATE t1 SET b='4' WHERE a=1 LIMIT 1;
54 UPDATE t1 SET b='5' WHERE a=2 ORDER BY a LIMIT 1;
55 DROP TABLE t1;
56
57 SET SQL_LOG_BIN= 1;
58
59 -- echo ### FILTERED database => assertion: warnings ARE NOT shown
60
61 let $old_db= `SELECT DATABASE()`;
62
63 CREATE DATABASE b42851;
64 USE b42851;
65
66 -- disable_warnings
67 DROP TABLE IF EXISTS t1;
68 -- enable_warnings
69
70 CREATE TABLE t1 (a int, b int, primary key (a));
71 INSERT INTO t1 VALUES (1,2), (2,3);
72 UPDATE t1 SET b='4' WHERE a=1 LIMIT 1;
73 UPDATE t1 SET b='5' WHERE a=2 ORDER BY a LIMIT 1;
74 DROP TABLE t1;
75
76
77 CREATE TABLE t1 (a VARCHAR(1000));
78 INSERT INTO t1 VALUES (CURRENT_USER()); #marked unsafe before BUG#47995
79 INSERT INTO t1 VALUES (FOUND_ROWS()); #marked unsafe before BUG#47995
80 INSERT INTO t1 VALUES (GET_LOCK('tmp', 1)); #marked unsafe in BUG#47995
81 INSERT INTO t1 VALUES (IS_FREE_LOCK('tmp')); #marked unsafe in BUG#47995
82 INSERT INTO t1 VALUES (IS_USED_LOCK('tmp')); #marked unsafe in BUG#47995
83 INSERT INTO t1 VALUES (LOAD_FILE('../../std_data/words2.dat')); #marked unsafe in BUG#39701
84 INSERT INTO t1 VALUES (SOURCE_POS_WAIT('dummy arg', 4711, 1));
85 INSERT INTO t1 VALUES (RELEASE_LOCK('tmp')); #marked unsafe in BUG#47995
86 INSERT INTO t1 VALUES (ROW_COUNT()); #marked unsafe before BUG#47995
87 INSERT INTO t1 VALUES (SESSION_USER()); #marked unsafe before BUG#47995
88 INSERT INTO t1 VALUES (SLEEP(1)); #marked unsafe in BUG#47995
89 INSERT INTO t1 VALUES (SYSDATE()); #marked unsafe in BUG#47995
90 INSERT INTO t1 VALUES (SYSTEM_USER()); #marked unsafe before BUG#47995
91 INSERT INTO t1 VALUES (USER()); #marked unsafe before BUG#47995
92 INSERT INTO t1 VALUES (UUID()); #marked unsafe before BUG#47995
93 INSERT INTO t1 VALUES (UUID_SHORT()); #marked unsafe before BUG#47995
94 INSERT INTO t1 VALUES (VERSION()); #marked unsafe in BUG#47995
95 INSERT INTO t1 VALUES (RAND()); #marked unsafe in BUG#49222
96
97 # clean up
98 DROP DATABASE b42851;
99
100 eval USE $old_db;
101
102 --echo #
103 --echo # Bug#46265: Can not disable warning about unsafe statements for binary logging
104 --echo #
105
106 let BINLOG_COUNTER1= `select CONVERT(NOW(),UNSIGNED) as timestmap from dual`;
107
108 SET @old_log_error_verbosity = @@log_error_verbosity;
109
110 --disable_warnings
111 DROP TABLE IF EXISTS t1;
112 --enable_warnings
113 CREATE TABLE t1 (a VARCHAR(36), b VARCHAR(15));
114
115 SET GLOBAL log_error_verbosity = 1;
116 # Replacing the result file content here.
117 # Instead of writing $BINLOG_COUNTER1 value to result file,
118 # writing a fixed string timestamp to it.
119 --replace_result $BINLOG_COUNTER1 timestamp
120 eval INSERT INTO t1 VALUES(UUID(), '$BINLOG_COUNTER1');
121 SET GLOBAL log_error_verbosity = 2;
122 --replace_result $BINLOG_COUNTER1 timestamp
123 eval INSERT INTO t1 VALUES(UUID(), '$BINLOG_COUNTER1');
124 DROP TABLE t1;
125
126 SET GLOBAL log_error_verbosity = @old_log_error_verbosity;
127
128 let $log_error_= `SELECT @@GLOBAL.log_error`;
129 if(!$log_error_)
130 {
131 # MySQL Server on windows is started with --console and thus
132 # does not know the location of its .err log, use default location
133 let $log_error_ = $MYSQLTEST_VARDIR/log/mysqld.1.err;
134 }
135 # Assign env variable LOG_ERROR
136 let LOG_ERROR=$log_error_;
137
138 --echo # Count the number of times the "Unsafe" message was printed
139 --echo # to the error log.
140
141 perl;
142 use strict;
143 my $log_error= $ENV{'LOG_ERROR'} or die "LOG_ERROR not set";
144 open(FILE, "$log_error") or die("Unable to open $log_error: $!\n");
145 my $binlog_counter= $ENV{'BINLOG_COUNTER1'} or die "BINLOG_COUNTER1 not set";
146 my $count = () = grep(/$binlog_counter/g,<FILE>);
147 # Grep the timestamp value from the error file.
148 print "Occurrences: $count\n";
149 close(FILE);
150 EOF
151
152 # bug#50192: diplaying the unsafe warning comes out to the user warning stack
153 -- disable_warnings
154 DROP TABLE IF EXISTS t1, t2;
155 -- enable_warnings
156
157 CREATE TABLE t1 (a int);
158 CREATE TABLE t2 (a int auto_increment primary key, b int);
159 CREATE TRIGGER tr_bug50192 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 (b) VALUES (1);
160
161 DELIMITER |;
162
163 CREATE FUNCTION sf_bug50192() RETURNS INTEGER
164 BEGIN
165 INSERT INTO t2(b) VALUES(2);
166 RETURN 1;
167 END |
168
169 DELIMITER ;|
170
171 INSERT INTO t1 VALUES (0);
172 SHOW WARNINGS;
173 SELECT sf_bug50192();
174 SHOW WARNINGS;
175
176 # cleanup
177
178 DROP FUNCTION sf_bug50192;
179 DROP TRIGGER tr_bug50192;
180 DROP TABLE t1, t2;