Debian Codegraph @

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

raw
1 # BUG#42851: Spurious "Statement is not safe to log in statement
2 # format." warnings
3 #
4 # WHY
5 # ===
6 #
7 # This test aims at checking that the fix that removes spurious
8 # entries in the error log when the statement is filtered out from
9 # binlog, is working.
10 #
11 # HOW
12 # ===
13 #
14 # The test case is split into three assertions when issuing statements
15 # containing LIMIT and ORDER BY:
16 #
17 # i) issue statements in database that is not filtered => check
18 # that warnings ARE shown;
19 #
20 # ii) issue statements in database that is not filtered, but with
21 # binlog disabled => check that warnings ARE NOT shown;
22 #
23 # iii) issue statements in database that is filtered => check that
24 # warnings ARE NOT shown.
25
26 -- source include/have_log_bin.inc
27 -- source include/have_binlog_format_statement.inc
28 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.");
29
30 -- echo ### NOT filtered database => assertion: warnings ARE shown
31
32 -- disable_warnings
33 DROP TABLE IF EXISTS t1;
34 -- enable_warnings
35
36 CREATE TABLE t1 (a int, b int, primary key (a));
37 INSERT INTO t1 VALUES (1,2), (2,3);
38 UPDATE t1 SET b='4' WHERE a=1 LIMIT 1;
39 UPDATE t1 SET b='5' WHERE a=2 ORDER BY a LIMIT 1;
40 DROP TABLE t1;
41
42 -- echo ### NOT filtered database => assertion: binlog disabled and warnings ARE NOT shown
43
44 SET SQL_LOG_BIN= 0;
45
46 -- disable_warnings
47 DROP TABLE IF EXISTS t1;
48 -- enable_warnings
49
50 CREATE TABLE t1 (a int, b int, primary key (a));
51 INSERT INTO t1 VALUES (1,2), (2,3);
52 UPDATE t1 SET b='4' WHERE a=1 LIMIT 1;
53 UPDATE t1 SET b='5' WHERE a=2 ORDER BY a LIMIT 1;
54 DROP TABLE t1;
55
56 SET SQL_LOG_BIN= 1;
57
58 -- echo ### FILTERED database => assertion: warnings ARE NOT shown
59
60 let $old_db= `SELECT DATABASE()`;
61
62 CREATE DATABASE b42851;
63 USE b42851;
64
65 -- disable_warnings
66 DROP TABLE IF EXISTS t1;
67 -- enable_warnings
68
69 CREATE TABLE t1 (a int, b int, primary key (a));
70 INSERT INTO t1 VALUES (1,2), (2,3);
71 UPDATE t1 SET b='4' WHERE a=1 LIMIT 1;
72 UPDATE t1 SET b='5' WHERE a=2 ORDER BY a LIMIT 1;
73 DROP TABLE t1;
74
75 CREATE TABLE t1 (a VARCHAR(1000));
76 INSERT INTO t1 VALUES (CURRENT_USER()); #marked unsafe before BUG#47995
77 INSERT INTO t1 VALUES (FOUND_ROWS()); #marked unsafe before BUG#47995
78 INSERT INTO t1 VALUES (GET_LOCK('tmp', 1)); #marked unsafe in BUG#47995
79 INSERT INTO t1 VALUES (IS_FREE_LOCK('tmp')); #marked unsafe in BUG#47995
80 INSERT INTO t1 VALUES (IS_USED_LOCK('tmp')); #marked unsafe in BUG#47995
81 INSERT INTO t1 VALUES (LOAD_FILE('../../std_data/words2.dat')); #marked unsafe in BUG#39701
82 INSERT INTO t1 VALUES (MASTER_POS_WAIT('dummy arg', 4711, 1));
83 INSERT INTO t1 VALUES (RELEASE_LOCK('tmp')); #marked unsafe in BUG#47995
84 INSERT INTO t1 VALUES (ROW_COUNT()); #marked unsafe before BUG#47995
85 INSERT INTO t1 VALUES (SESSION_USER()); #marked unsafe before BUG#47995
86 INSERT INTO t1 VALUES (SLEEP(1)); #marked unsafe in BUG#47995
87 INSERT INTO t1 VALUES (SYSDATE()); #marked unsafe in BUG#47995
88 INSERT INTO t1 VALUES (SYSTEM_USER()); #marked unsafe before BUG#47995
89 INSERT INTO t1 VALUES (USER()); #marked unsafe before BUG#47995
90 INSERT INTO t1 VALUES (UUID()); #marked unsafe before BUG#47995
91 INSERT INTO t1 VALUES (UUID_SHORT()); #marked unsafe before BUG#47995
92 INSERT INTO t1 VALUES (VERSION()); #marked unsafe in BUG#47995
93 INSERT INTO t1 VALUES (RAND()); #marked unsafe in BUG#49222
94
95 # clean up
96 DROP DATABASE b42851;
97
98 eval USE $old_db;
99
100 --echo #
101 --echo # Bug#46265: Can not disable warning about unsafe statements for binary logging
102 --echo #
103
104 let BINLOG_COUNTER1= `select CONVERT(NOW(),UNSIGNED) as timestmap from dual`;
105
106 SET @old_log_warnings = @@log_warnings;
107
108 --disable_warnings
109 DROP TABLE IF EXISTS t1;
110 --enable_warnings
111 CREATE TABLE t1 (a VARCHAR(36), b VARCHAR(15));
112
113 SET GLOBAL LOG_WARNINGS = 0;
114 # Replacing the result file content here.
115 # Instead of writing $BINLOG_COUNTER1 value to result file,
116 # writing a fixed string timestamp to it.
117 --replace_result $BINLOG_COUNTER1 timestamp
118 eval INSERT INTO t1 VALUES(UUID(), '$BINLOG_COUNTER1');
119 SET GLOBAL LOG_WARNINGS = 1;
120 --replace_result $BINLOG_COUNTER1 timestamp
121 eval INSERT INTO t1 VALUES(UUID(), '$BINLOG_COUNTER1');
122 DROP TABLE t1;
123
124 SET GLOBAL log_warnings = @old_log_warnings;
125
126 let $log_error_= `SELECT @@GLOBAL.log_error`;
127 if(!$log_error_)
128 {
129 # MySQL Server on windows is started with --console and thus
130 # does not know the location of its .err log, use default location
131 let $log_error_ = $MYSQLTEST_VARDIR/log/mysqld.1.err;
132 }
133
134 # Assign env variable LOG_ERROR
135 let LOG_ERROR=$log_error_;
136
137 --echo # Count the number of times the "Unsafe" message was printed
138 --echo # to the error log.
139
140 perl;
141 use strict;
142 use Cwd;
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' from directory " . cwd() . " :$! \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 #
153 # Check how RAND() can be used with replication
154 #
155
156 create table t1 (n1 int, n2 int, n3 int,
157 key (n1, n2, n3),
158 key (n2, n3, n1),
159 key (n3, n1, n2));
160 insert into t1 values (1,1,1);
161 # This should work fine
162 insert into t1 values (RAND()*1000+10, RAND()*1000+10, RAND()*1000+10);
163 # This should need row based logging.
164 update t1 set n1=rand() where n1=1;
165 delete from t1 where n2=1 + rand()*0;
166 drop table t1;
167
168 # bug#50192: diplaying the unsafe warning comes out to the user warning stack
169 -- disable_warnings
170 DROP TABLE IF EXISTS t1, t2;
171 -- enable_warnings
172
173 CREATE TABLE t1 (a int);
174 CREATE TABLE t2 (a int auto_increment primary key, b int);
175 CREATE TRIGGER tr_bug50192 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 (b) VALUES (1);
176
177 DELIMITER |;
178
179 CREATE FUNCTION sf_bug50192() RETURNS INTEGER
180 BEGIN
181 INSERT INTO t2(b) VALUES(2);
182 RETURN 1;
183 END |
184
185 DELIMITER ;|
186
187 INSERT INTO t1 VALUES (0);
188 SHOW WARNINGS;
189 SELECT sf_bug50192();
190 SHOW WARNINGS;
191
192 # The test proves MDEV-24617 fixes leave in force
193 # unsafe warnings in non-deterministic CREATE..SELECT cases.
194 # Below an inserted default value to `b` of the target table is replication
195 # unsafe. A warning must be out.
196 CREATE TABLE t3 (a INT(11) DEFAULT NULL);
197 INSERT INTO t3 VALUES (1);
198 CREATE TABLE t4 (a INT(11) DEFAULT NULL, b BIGINT(20) DEFAULT uuid_short()) SELECT * FROM t3;
199 SHOW WARNINGS;
200 # no warning out of a deterministic "rhs" of SELECT
201 CREATE OR REPLACE TABLE t4 (a INT(11) DEFAULT NULL) SELECT * FROM t3;
202 SHOW WARNINGS;
203
204 # cleanup
205
206 DROP FUNCTION sf_bug50192;
207 DROP TRIGGER tr_bug50192;
208 DROP TABLE t1, t2, t3, t4;