List:Commits« Previous MessageNext Message »
From:Sven Sandberg Date:January 5 2010 4:05pm
Subject:bzr commit into mysql-5.1-bugteam branch (sven.sandberg:3276) Bug#49222
View as plain text  
#At file:///home/sven/bzr/b49222-rand_unsafe/5.1-bugteam/ based on revid:mattias.jonsson@stripped

 3276 Sven Sandberg	2010-01-05
      BUG#49222: Mark RAND() as unsafe
      Problem: When RAND() is binlogged in statement mode, the seed is
      binlogged too, so the replication slave generates the same
      sequence of random numbers. This makes replication work in many
      cases, but not in all cases: the order of rows is not guaranteed
      for, e.g., UPDATE or INSERT...SELECT statements, so the row data
      will be different if master and slave retrieve the rows in
      different orders.
      Fix: Mark RAND() as unsafe. It will generate a warning if
      binlog_format=STATEMENT and switch to row-logging if
      binlog_format=ROW.
     @ mysql-test/extra/rpl_tests/rpl_row_func003.test
        updated test case to ignore new warnings
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        updated result file
     @ mysql-test/suite/binlog/t/binlog_unsafe.test
        Added test for RAND().
        Also clarified some old comments.
     @ mysql-test/suite/rpl/r/rpl_misc_functions.result
        updated result file
     @ mysql-test/suite/rpl/r/rpl_nondeterministic_functions.result
        updated test case to ignore new warnings
     @ mysql-test/suite/rpl/r/rpl_optimize.result
        updated result file
     @ mysql-test/suite/rpl/t/rpl_misc_functions.test
        updated test case to ignore new warnings
     @ mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test
        updated test case to ignore new warnings
     @ mysql-test/suite/rpl/t/rpl_optimize.test
        updated test case to ignore new warnings
     @ mysql-test/suite/rpl/t/rpl_trigger.test
        updated test case to ignore new warnings
     @ mysql-test/suite/rpl_ndb/r/rpl_ndb_func003.result
        updated result file
     @ sql/item_create.cc
        Mark RAND() unsafe.

    modified:
      mysql-test/extra/rpl_tests/rpl_row_func003.test
      mysql-test/suite/binlog/r/binlog_unsafe.result
      mysql-test/suite/binlog/t/binlog_unsafe.test
      mysql-test/suite/rpl/r/rpl_misc_functions.result
      mysql-test/suite/rpl/r/rpl_nondeterministic_functions.result
      mysql-test/suite/rpl/r/rpl_optimize.result
      mysql-test/suite/rpl/t/rpl_misc_functions.test
      mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test
      mysql-test/suite/rpl/t/rpl_optimize.test
      mysql-test/suite/rpl/t/rpl_trigger.test
      mysql-test/suite/rpl_ndb/r/rpl_ndb_func003.result
      sql/item_create.cc
=== modified file 'mysql-test/extra/rpl_tests/rpl_row_func003.test'
--- a/mysql-test/extra/rpl_tests/rpl_row_func003.test	2007-06-18 13:36:10 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_row_func003.test	2010-01-05 16:05:46 +0000
@@ -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;

=== modified file 'mysql-test/suite/binlog/r/binlog_unsafe.result'
--- a/mysql-test/suite/binlog/r/binlog_unsafe.result	2009-11-18 14:50:31 +0000
+++ b/mysql-test/suite/binlog/r/binlog_unsafe.result	2010-01-05 16:05:46 +0000
@@ -379,6 +379,9 @@ Note	1592	Statement may not be safe to l
 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

=== modified file 'mysql-test/suite/binlog/t/binlog_unsafe.test'
--- a/mysql-test/suite/binlog/t/binlog_unsafe.test	2009-11-18 14:50:31 +0000
+++ b/mysql-test/suite/binlog/t/binlog_unsafe.test	2010-01-05 16:05:46 +0000
@@ -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

=== modified file 'mysql-test/suite/rpl/r/rpl_misc_functions.result'
--- a/mysql-test/suite/rpl/r/rpl_misc_functions.result	2008-10-07 12:22:28 +0000
+++ b/mysql-test/suite/rpl/r/rpl_misc_functions.result	2010-01-05 16:05:46 +0000
@@ -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, "");

=== modified file 'mysql-test/suite/rpl/r/rpl_nondeterministic_functions.result'
--- a/mysql-test/suite/rpl/r/rpl_nondeterministic_functions.result	2009-11-18 14:50:31 +0000
+++ b/mysql-test/suite/rpl/r/rpl_nondeterministic_functions.result	2010-01-05 16:05:46 +0000
@@ -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());

=== modified file 'mysql-test/suite/rpl/r/rpl_optimize.result'
--- a/mysql-test/suite/rpl/r/rpl_optimize.result	2007-06-27 12:28:02 +0000
+++ b/mysql-test/suite/rpl/r/rpl_optimize.result	2010-01-05 16:05:46 +0000
@@ -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;

=== modified file 'mysql-test/suite/rpl/t/rpl_misc_functions.test'
--- a/mysql-test/suite/rpl/t/rpl_misc_functions.test	2008-10-07 08:25:12 +0000
+++ b/mysql-test/suite/rpl/t/rpl_misc_functions.test	2010-01-05 16:05:46 +0000
@@ -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
 

=== modified file 'mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test'
--- a/mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test	2009-11-18 14:50:31 +0000
+++ b/mysql-test/suite/rpl/t/rpl_nondeterministic_functions.test	2010-01-05 16:05:46 +0000
@@ -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());
 

=== modified file 'mysql-test/suite/rpl/t/rpl_optimize.test'
--- a/mysql-test/suite/rpl/t/rpl_optimize.test	2008-03-25 13:28:12 +0000
+++ b/mysql-test/suite/rpl/t/rpl_optimize.test	2010-01-05 16:05:46 +0000
@@ -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
 

=== modified file 'mysql-test/suite/rpl/t/rpl_trigger.test'
--- a/mysql-test/suite/rpl/t/rpl_trigger.test	2009-11-18 14:50:31 +0000
+++ b/mysql-test/suite/rpl/t/rpl_trigger.test	2010-01-05 16:05:46 +0000
@@ -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;

=== modified file 'mysql-test/suite/rpl_ndb/r/rpl_ndb_func003.result'
--- a/mysql-test/suite/rpl_ndb/r/rpl_ndb_func003.result	2007-06-27 12:28:02 +0000
+++ b/mysql-test/suite/rpl_ndb/r/rpl_ndb_func003.result	2010-01-05 16:05:46 +0000
@@ -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;

=== modified file 'sql/item_create.cc'
--- a/sql/item_create.cc	2009-11-18 14:50:31 +0000
+++ b/sql/item_create.cc	2010-01-05 16:05:46 +0000
@@ -4178,6 +4178,16 @@ Create_func_rand::create_native(THD *thd
   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:
   {


Attachment: [text/bzr-bundle] bzr/sven.sandberg@sun.com-20100105160546-ew27exac805bifaj.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (sven.sandberg:3276) Bug#49222Sven Sandberg5 Jan
  • Re: bzr commit into mysql-5.1-bugteam branch (sven.sandberg:3276)Bug#49222Luís Soares6 Jan
  • Re: bzr commit into mysql-5.1-bugteam branch (sven.sandberg:3276)Bug#49222Alfranio Correia12 Jan