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