summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/extra/rpl_tests/rpl_row_func003.test6
-rw-r--r--mysql-test/suite/binlog/r/binlog_unsafe.result3
-rw-r--r--mysql-test/suite/binlog/t/binlog_unsafe.test24
-rw-r--r--mysql-test/suite/rpl/r/rpl_misc_functions.result1
-rw-r--r--mysql-test/suite/rpl/r/rpl_nondeterministic_functions.result1
-rw-r--r--mysql-test/suite/rpl/r/rpl_optimize.result1
-rw-r--r--mysql-test/suite/rpl/r/rpl_row_func003.result1
-rw-r--r--mysql-test/suite/rpl/t/rpl_misc_functions.test8
-rw-r--r--mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test4
-rw-r--r--mysql-test/suite/rpl/t/rpl_optimize.test4
-rw-r--r--mysql-test/suite/rpl/t/rpl_trigger.test2
-rw-r--r--mysql-test/suite/rpl_ndb/r/rpl_ndb_func003.result1
-rw-r--r--sql/item_create.cc10
13 files changed, 55 insertions, 11 deletions
diff --git a/mysql-test/extra/rpl_tests/rpl_row_func003.test b/mysql-test/extra/rpl_tests/rpl_row_func003.test
index 8ee2d863527..b77465de39e 100644
--- a/mysql-test/extra/rpl_tests/rpl_row_func003.test
+++ b/mysql-test/extra/rpl_tests/rpl_row_func003.test
@@ -18,6 +18,8 @@
# Vs slave. #
#############################################################################
+CALL mtr.add_suppression('Statement may not be safe to log in statement format.');
+
# Begin clean up test section
connection master;
--disable_warnings
@@ -43,10 +45,12 @@ RETURN tmp;
END|
delimiter ;|
+--disable_warnings
INSERT INTO test.t1 VALUES (null,test.f1()),(null,test.f1()),(null,test.f1());
sleep 6;
INSERT INTO test.t1 VALUES (null,test.f1()),(null,test.f1()),(null,test.f1());
sleep 6;
+--enable_warnings
#Select in this test are used for debugging
#select * from test.t1;
@@ -56,7 +60,9 @@ sleep 6;
connection master;
SET AUTOCOMMIT=0;
START TRANSACTION;
+--disable_warnings
INSERT INTO test.t1 VALUES (null,test.f1());
+--enable_warnings
ROLLBACK;
SET AUTOCOMMIT=1;
#select * from test.t1;
diff --git a/mysql-test/suite/binlog/r/binlog_unsafe.result b/mysql-test/suite/binlog/r/binlog_unsafe.result
index 8bbf993a727..6b2a83c9483 100644
--- a/mysql-test/suite/binlog/r/binlog_unsafe.result
+++ b/mysql-test/suite/binlog/r/binlog_unsafe.result
@@ -379,6 +379,9 @@ Note 1592 Statement may not be safe to log in statement format.
INSERT INTO t1 VALUES (VERSION());
Warnings:
Note 1592 Statement may not be safe to log in statement format.
+INSERT INTO t1 VALUES (RAND());
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
DELETE FROM t1;
SET TIMESTAMP=1000000;
INSERT INTO t1 VALUES
diff --git a/mysql-test/suite/binlog/t/binlog_unsafe.test b/mysql-test/suite/binlog/t/binlog_unsafe.test
index 1acb4d090ca..5e399f3e602 100644
--- a/mysql-test/suite/binlog/t/binlog_unsafe.test
+++ b/mysql-test/suite/binlog/t/binlog_unsafe.test
@@ -47,6 +47,8 @@
# BUG#34768: nondeterministic INSERT using LIMIT logged in stmt mode if binlog_format=mixed
# BUG#41980, SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0
# BUG#42640: mysqld crashes when unsafe statements are executed (STRICT_TRANS_TABLES mode)
+# BUG#47995: Mark user functions as unsafe
+# BUG#49222: Mare RAND() unsafe
#
# ==== Related test cases ====
#
@@ -391,6 +393,7 @@ SET @@SESSION.SQL_MODE = @save_sql_mode;
#
# BUG#47995: Mark user functions as unsafe
+# BUG#49222: Mare RAND() unsafe
#
# Test that the system functions that are supposed to be marked unsafe
# generate a warning. Each INSERT statement below should generate a
@@ -400,27 +403,28 @@ SET @@SESSION.SQL_MODE = @save_sql_mode;
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));
-INSERT INTO t1 VALUES (IS_FREE_LOCK('tmp'));
-INSERT INTO t1 VALUES (IS_USED_LOCK('tmp'));
-INSERT INTO t1 VALUES (LOAD_FILE('../../std_data/words2.dat')); #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'));
+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));
-INSERT INTO t1 VALUES (SYSDATE());
+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());
+INSERT INTO t1 VALUES (VERSION()); #marked unsafe in BUG#47995
+INSERT INTO t1 VALUES (RAND()); #marked unsafe in BUG#49222
DELETE FROM t1;
# Since we replicate the TIMESTAMP variable, functions affected by the
# TIMESTAMP variable are safe to replicate. So we check that the
-# following following functions depend on the TIMESTAMP variable and
-# don't generate a warning.
+# following following functions that depend on the TIMESTAMP variable
+# are not unsafe and don't generate a warning.
SET TIMESTAMP=1000000;
INSERT INTO t1 VALUES
diff --git a/mysql-test/suite/rpl/r/rpl_misc_functions.result b/mysql-test/suite/rpl/r/rpl_misc_functions.result
index 28b777822e4..6d69235927e 100644
--- a/mysql-test/suite/rpl/r/rpl_misc_functions.result
+++ b/mysql-test/suite/rpl/r/rpl_misc_functions.result
@@ -4,6 +4,7 @@ reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
+CALL mtr.add_suppression('Statement may not be safe to log in statement format.');
create table t1(id int, i int, r1 int, r2 int, p varchar(100));
insert into t1 values(1, connection_id(), 0, 0, "");
insert into t1 values(2, 0, rand()*1000, rand()*1000, "");
diff --git a/mysql-test/suite/rpl/r/rpl_nondeterministic_functions.result b/mysql-test/suite/rpl/r/rpl_nondeterministic_functions.result
index c4842a284cd..3b9b741e040 100644
--- a/mysql-test/suite/rpl/r/rpl_nondeterministic_functions.result
+++ b/mysql-test/suite/rpl/r/rpl_nondeterministic_functions.result
@@ -4,6 +4,7 @@ reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
+CALL mtr.add_suppression('Statement may not be safe to log in statement format.');
CREATE TABLE t1 (a VARCHAR(1000));
INSERT INTO t1 VALUES (CONNECTION_ID());
INSERT INTO t1 VALUES (CONNECTION_ID());
diff --git a/mysql-test/suite/rpl/r/rpl_optimize.result b/mysql-test/suite/rpl/r/rpl_optimize.result
index 79891169fbc..1ae94a3ca36 100644
--- a/mysql-test/suite/rpl/r/rpl_optimize.result
+++ b/mysql-test/suite/rpl/r/rpl_optimize.result
@@ -4,6 +4,7 @@ reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
+CALL mtr.add_suppression('Statement may not be safe to log in statement format.');
create table t1 (a int not null auto_increment primary key, b int, key(b));
INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
INSERT INTO t1 (a) SELECT null FROM t1;
diff --git a/mysql-test/suite/rpl/r/rpl_row_func003.result b/mysql-test/suite/rpl/r/rpl_row_func003.result
index a5fd46a2ce3..94d01b50ce5 100644
--- a/mysql-test/suite/rpl/r/rpl_row_func003.result
+++ b/mysql-test/suite/rpl/r/rpl_row_func003.result
@@ -4,6 +4,7 @@ reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
+CALL mtr.add_suppression('Statement may not be safe to log in statement format.');
DROP FUNCTION IF EXISTS test.f1;
DROP TABLE IF EXISTS test.t1;
CREATE TABLE test.t1 (a INT NOT NULL AUTO_INCREMENT, c CHAR(16),PRIMARY KEY(a))ENGINE=INNODB;
diff --git a/mysql-test/suite/rpl/t/rpl_misc_functions.test b/mysql-test/suite/rpl/t/rpl_misc_functions.test
index d2e61d579e3..b84042160cd 100644
--- a/mysql-test/suite/rpl/t/rpl_misc_functions.test
+++ b/mysql-test/suite/rpl/t/rpl_misc_functions.test
@@ -3,12 +3,16 @@
#
source include/master-slave.inc;
+CALL mtr.add_suppression('Statement may not be safe to log in statement format.');
+
create table t1(id int, i int, r1 int, r2 int, p varchar(100));
insert into t1 values(1, connection_id(), 0, 0, "");
# don't put rand and password in the same query, to see if they replicate
# independently
# Pure rand test
+--disable_warnings
insert into t1 values(2, 0, rand()*1000, rand()*1000, "");
+--enable_warnings
# change the rand suite on the master (we do this because otherwise password()
# benefits from the fact that the above rand() is well replicated :
# it picks the same sequence element, which hides a possible bug in password() replication.
@@ -19,7 +23,9 @@ set sql_log_bin=1;
# Pure password test
insert into t1 values(3, 0, 0, 0, password('does_this_work?'));
# "altogether now"
+--disable_warnings
insert into t1 values(4, connection_id(), rand()*1000, rand()*1000, password('does_this_still_work?'));
+--enable_warnings
select * into outfile 'rpl_misc_functions.outfile' from t1;
let $MYSQLD_DATADIR= `select @@datadir`;
sync_slave_with_master;
@@ -73,11 +79,13 @@ DELIMITER ;|
# Exercise the functions and procedures then compare the results on
# the master to those on the slave.
+--disable_warnings
CALL test_replication_sp1();
CALL test_replication_sp2();
INSERT INTO t1 (col_a) VALUES (test_replication_sf());
INSERT INTO t1 (col_a) VALUES (test_replication_sf());
INSERT INTO t1 (col_a) VALUES (test_replication_sf());
+--enable_warnings
--sync_slave_with_master
diff --git a/mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test b/mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test
index a30eb3be374..9ff2e2d081e 100644
--- a/mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test
+++ b/mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test
@@ -17,6 +17,8 @@
--source include/master-slave.inc
+CALL mtr.add_suppression('Statement may not be safe to log in statement format.');
+
CREATE TABLE t1 (a VARCHAR(1000));
# We replicate the connection_id in the query_log_event
@@ -41,7 +43,9 @@ INSERT INTO t1 VALUES
(UTC_TIMESTAMP());
# We replicate the random seed in a rand_log_event
+--disable_warnings
INSERT INTO t1 VALUES (RAND());
+--enable_warnings
# We replicate the last_insert_id in an intvar_log_event
INSERT INTO t1 VALUES (LAST_INSERT_ID());
diff --git a/mysql-test/suite/rpl/t/rpl_optimize.test b/mysql-test/suite/rpl/t/rpl_optimize.test
index f4582ba1167..87eb9dce818 100644
--- a/mysql-test/suite/rpl/t/rpl_optimize.test
+++ b/mysql-test/suite/rpl/t/rpl_optimize.test
@@ -13,6 +13,8 @@
-- source include/not_ndb_default.inc
-- source include/master-slave.inc
+CALL mtr.add_suppression('Statement may not be safe to log in statement format.');
+
create table t1 (a int not null auto_increment primary key, b int, key(b));
INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
INSERT INTO t1 (a) SELECT null FROM t1;
@@ -30,8 +32,8 @@ INSERT INTO t1 (a) SELECT null FROM t1;
INSERT INTO t1 (a) SELECT null FROM t1;
save_master_pos;
# a few updates to force OPTIMIZE to do something
-update t1 set b=(a/2*rand());
--disable_warnings
+update t1 set b=(a/2*rand());
delete from t1 order by b limit 10000;
--enable_warnings
diff --git a/mysql-test/suite/rpl/t/rpl_trigger.test b/mysql-test/suite/rpl/t/rpl_trigger.test
index 4b40fbea719..e296da01bad 100644
--- a/mysql-test/suite/rpl/t/rpl_trigger.test
+++ b/mysql-test/suite/rpl/t/rpl_trigger.test
@@ -40,10 +40,12 @@ insert into t3 values(100,"log",0,0,0);
SET @@RAND_SEED1=658490765, @@RAND_SEED2=635893186;
# Emulate that we have rows 2-9 deleted on the slave
+--disable_warnings
insert into t1 values(1,1,rand()),(NULL,2,rand());
insert into t2 (b) values(last_insert_id());
insert into t2 values(3,0),(NULL,0);
insert into t2 values(NULL,0),(500,0);
+--enable_warnings
select a,b, truncate(rand_value,4) from t1;
select * from t2;
diff --git a/mysql-test/suite/rpl_ndb/r/rpl_ndb_func003.result b/mysql-test/suite/rpl_ndb/r/rpl_ndb_func003.result
index ad72a3fb244..7e750f2ce2a 100644
--- a/mysql-test/suite/rpl_ndb/r/rpl_ndb_func003.result
+++ b/mysql-test/suite/rpl_ndb/r/rpl_ndb_func003.result
@@ -4,6 +4,7 @@ reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
+CALL mtr.add_suppression('Statement may not be safe to log in statement format.');
DROP FUNCTION IF EXISTS test.f1;
DROP TABLE IF EXISTS test.t1;
CREATE TABLE test.t1 (a INT NOT NULL AUTO_INCREMENT, c CHAR(16),PRIMARY KEY(a))ENGINE=NDB;
diff --git a/sql/item_create.cc b/sql/item_create.cc
index 53aa8081da1..c309ccc06ca 100644
--- a/sql/item_create.cc
+++ b/sql/item_create.cc
@@ -4178,6 +4178,16 @@ Create_func_rand::create_native(THD *thd, LEX_STRING name,
if (item_list != NULL)
arg_count= item_list->elements;
+ /*
+ When RAND() is binlogged, the seed is binlogged too. So the
+ sequence of random numbers is the same on a replication slave as
+ on the master. However, if several RAND() values are inserted
+ into a table, the order in which the rows are modified may differ
+ between master and slave, because the order is undefined. Hence,
+ the statement is unsafe to log in statement format.
+ */
+ thd->lex->set_stmt_unsafe();
+
switch (arg_count) {
case 0:
{
View Lorry Controller Status