List:Commits« Previous MessageNext Message »
From:cbell Date:February 28 2007 7:27pm
Subject:bk commit into 5.0 tree (cbell:1.2425) BUG#25543
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of cbell. When cbell does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-02-28 14:10:45-05:00, cbell@mysql_cab_desk. +3 -0
  Bug #25543 Replication of wrong values if using rand() in stored procedure
  
  When rand() is called multiple times inside a stored procedure or function, 
  the server does not forward the correct random seed values from the master 
  to the slave. In fact, the same random seed values are used for the repeated
  calls to rand() on the slave resulting in incorrect values.
  
  This patch corrects the problem by identifying the condition described above
  and updating the saved random seeds so they can be replicated to the slave.

  mysql-test/r/rpl_misc_functions.result@stripped, 2007-02-28 14:10:40-05:00, cbell@mysql_cab_desk. +25 -2
    Bug #25543 Replication of wrong values if using rand() in stored procedure
    
    The result file was modified to include the correct processing of the new
    additions to the test. The results from execution are written to files on 
    both the master and the slave. The files are compared to ensure the values 
    from rand() generated on the master are correctly generated on the slave.

  mysql-test/t/rpl_misc_functions.test@stripped, 2007-02-28 14:10:41-05:00, cbell@mysql_cab_desk. +66 -2
    Bug #25543 Replication of wrong values if using rand() in stored procedure
    
    The test was modified to include a test of a stored procedure that calls
    the rand() function multiple times. A stored function was also added to
    ensure both are replicating correctly. 
    
    The results from execution are written to files on both the master and the
    slave. The files are compared to ensure the values from rand() generated
    on the master are correctly generated on the slave.

  sql/sql_class.cc@stripped, 2007-02-28 14:10:41-05:00, cbell@mysql_cab_desk. +14 -0
    Bug #25543 Replication of wrong values if using rand() in stored procedure
    
    The code was modified to detect the condition where multiple calls are being
    made to the rand() function. This is accomplished by adding a gate to the 
    cleanup_after_query method that resets rand_used=0 if we are not in a sub
    statement. This allows the code to save the generated random seeds. The
    seeds are then replicated to the slave so that the slave can generate the 
    correct random values for multiple calls to rand(). 

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	cbell
# Host:	mysql_cab_desk.
# Root:	C:/source/c++/mysql-5.0_BUG_25543

--- 1.260/sql/sql_class.cc	2007-02-28 14:10:55 -05:00
+++ 1.261/sql/sql_class.cc	2007-02-28 14:10:55 -05:00
@@ -575,6 +575,20 @@
     clear_next_insert_id= 0;
     next_insert_id= 0;
   }
+  /*
+   When the execution of a command is complete, reset rand_used so that 
+   the server binlogs the random seeds. This allows the slave to execute 
+   multiple calls to rand() with the same values as those generated on 
+   the master.
+ 
+   The gate also ensures that the random seeds are binlogged after a 
+   stored function or trigger is complete. Resetting rand_used
+   during the execution of the stored function or trigger will cause
+   an early Rand_log_event write which may generate incorrect values 
+   on the slave.
+  */
+  if (!in_sub_stmt)
+    rand_used= 0;
   /* Free Items that were created during this execution */
   free_items();
   /* Reset where. */

--- 1.4/mysql-test/r/rpl_misc_functions.result	2007-02-28 14:10:55 -05:00
+++ 1.5/mysql-test/r/rpl_misc_functions.result	2007-02-28 14:10:55 -05:00
@@ -18,6 +18,29 @@
 load data local infile 'MYSQLTEST_VARDIR/master-data/test/rpl_misc_functions.outfile' into table t2;
 select * from t1, t2 where (t1.id=t2.id) and not(t1.i=t2.i and t1.r1=t2.r1 and t1.r2=t2.r2 and t1.p=t2.p);
 id	i	r1	r2	p	id	i	r1	r2	p
-stop slave;
-drop table t1;
 drop table t1;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (col_a double default NULL);
+CREATE PROCEDURE test_replication_sp1()
+BEGIN
+INSERT INTO t1 VALUES (rand()), (rand());
+INSERT INTO t1 VALUES (rand());
+END|
+CREATE PROCEDURE test_replication_sp2()
+BEGIN
+CALL test_replication_sp1();
+CALL test_replication_sp1();
+END|
+CREATE FUNCTION test_replication_sf() RETURNS DOUBLE DETERMINISTIC
+BEGIN
+RETURN (rand() + rand());
+END|
+CALL test_replication_sp1();
+CALL test_replication_sp2();
+INSERT INTO t1 VALUES (test_replication_sf());
+INSERT INTO t1 VALUES (test_replication_sf());
+INSERT INTO t1 VALUES (test_replication_sf());
+DROP PROCEDURE IF EXISTS test_replication_sp1;
+DROP PROCEDURE IF EXISTS test_replication_sp2;
+DROP FUNCTION IF EXISTS test_replication_sf;
+DROP TABLE IF EXISTS t1;

--- 1.5/mysql-test/t/rpl_misc_functions.test	2007-02-28 14:10:55 -05:00
+++ 1.6/mysql-test/t/rpl_misc_functions.test	2007-02-28 14:10:55 -05:00
@@ -28,10 +28,74 @@
 eval load data local infile '$MYSQLTEST_VARDIR/master-data/test/rpl_misc_functions.outfile' into table t2;
 # compare them with the replica; the SELECT below should return no row
 select * from t1, t2 where (t1.id=t2.id) and not(t1.i=t2.i and t1.r1=t2.r1 and t1.r2=t2.r2 and t1.p=t2.p);
-stop slave;
-drop table t1;
 
 connection master;
 drop table t1;
 
 # End of 4.1 tests
+
+#
+# BUG#25543 test calling rand() multiple times on the master in
+# a stored procedure.
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (col_a double default NULL);
+
+DELIMITER |;
+
+# Use a SP that calls rand() multiple times
+CREATE PROCEDURE test_replication_sp1()
+BEGIN
+ INSERT INTO t1 VALUES (rand()), (rand());
+ INSERT INTO t1 VALUES (rand());
+END|
+
+# Use a SP that calls another SP to call rand() multiple times
+CREATE PROCEDURE test_replication_sp2()
+BEGIN
+  CALL test_replication_sp1();
+  CALL test_replication_sp1();
+END|
+
+# Use a SF that calls rand() multiple times
+CREATE FUNCTION test_replication_sf() RETURNS DOUBLE DETERMINISTIC
+BEGIN
+ RETURN (rand() + rand());
+END|
+
+DELIMITER ;|
+
+# Exercise the functions and procedures then compare the results on
+# the master to those on the slave.
+CALL test_replication_sp1();
+CALL test_replication_sp2();
+INSERT INTO t1 VALUES (test_replication_sf());
+INSERT INTO t1 VALUES (test_replication_sf());
+INSERT INTO t1 VALUES (test_replication_sf());
+
+# Record the results of the query on the master
+--exec $MYSQL --port=$MASTER_MYPORT test -e "SELECT * FROM test.t1" > $MYSQLTEST_VARDIR/tmp/rpl_rand_master.sql
+
+--sync_slave_with_master
+
+# Record the results of the query on the slave
+--exec $MYSQL --port=$SLAVE_MYPORT test -e "SELECT * FROM test.t1" > $MYSQLTEST_VARDIR/tmp/rpl_rand_slave.sql
+
+# Compare the results from the master to the slave.
+--exec diff $MYSQLTEST_VARDIR/tmp/rpl_rand_master.sql $MYSQLTEST_VARDIR/tmp/rpl_rand_slave.sql
+
+# Cleanup
+--disable_warnings
+DROP PROCEDURE IF EXISTS test_replication_sp1;
+DROP PROCEDURE IF EXISTS test_replication_sp2;
+DROP FUNCTION IF EXISTS test_replication_sf;
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+# If all is good, when can cleanup our dump files.
+--system rm $MYSQLTEST_VARDIR/tmp/rpl_rand_master.sql
+--system rm $MYSQLTEST_VARDIR/tmp/rpl_rand_slave.sql

Thread
bk commit into 5.0 tree (cbell:1.2425) BUG#25543cbell28 Feb