Bug #25543 Replication of wrong values if using rand() in stored procedure
Submitted: 11 Jan 2007 13:19 Modified: 3 Apr 2007 22:48
Reporter: Corrado Pandiani Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.0.34-BK, 5.0.30 / 5.1.14 / 5.0.20 OS:Linux (Linux / MacOS X / Windows XP)
Assigned to: Chuck Bell CPU Architecture:Any
Tags: binlog, rand, replication, stored procedure

[11 Jan 2007 13:19] Corrado Pandiani
Description:
Using rand() function in a stored procedure more than one time causes replication to write the wrong numbers in the slaves. Only the first value is correctly replicated, the others are not.

The reason is that the master writes to the binlog always the same number seed for the function rand().

Here is my sample SP:

mysql> create procedure prova()
-> begin
-> create table t(a double);
-> insert into t values(rand());
-> insert into t values(rand());
-> select * from t;
-> end//

mysql> call prova()//
+--------------------+
| a                  |
+--------------------+
| 0.0546931066128361 | 
|  0.355094222682616 | 
+--------------------+

let's see the values in table t in a slave

mysql> select * from t;
+--------------------+
| a                  |
+--------------------+
| 0.0546931066128361 | <-- right 
| 0.0546931066128361 | <-- wrong
+--------------------+

and now have a look to my binlog content

mysql> show binlog events in 'computer-di-corrado-pandiani-bin.000001' from 102 limit 6\G
*************************** 1. row ***************************
   Log_name: computer-di-corrado-pandiani-bin.000001
        Pos: 102
 Event_type: Query
  Server_id: 1
End_log_pos: 329
       Info: use `test`; CREATE DEFINER=`root`@`localhost` procedure prova()
begin
create table t(a double);
insert into t values(rand());
insert into t values(rand());
select * from t;
end
*************************** 2. row ***************************
   Log_name: computer-di-corrado-pandiani-bin.000001
        Pos: 329
 Event_type: Query
  Server_id: 1
End_log_pos: 424
       Info: use `test`; create table t(a double)
*************************** 3. row ***************************
   Log_name: computer-di-corrado-pandiani-bin.000001
        Pos: 424
 Event_type: RAND
  Server_id: 1
End_log_pos: 459
       Info: rand_seed1=56433538,rand_seed2=677610263
*************************** 4. row ***************************
   Log_name: computer-di-corrado-pandiani-bin.000001
        Pos: 459
 Event_type: Query
  Server_id: 1
End_log_pos: 558
       Info: use `test`; insert into t values(rand())
*************************** 5. row ***************************
   Log_name: computer-di-corrado-pandiani-bin.000001
        Pos: 558
 Event_type: RAND
  Server_id: 1
End_log_pos: 593
       Info: rand_seed1=56433538,rand_seed2=677610263
*************************** 6. row ***************************
   Log_name: computer-di-corrado-pandiani-bin.000001
        Pos: 593
 Event_type: Query
  Server_id: 1
End_log_pos: 692
       Info: use `test`; insert into t values(rand())
6 rows in set (0.00 sec)

Taking a look to record 3 and 5, the same number seed is logged, and this cause the slave to have always the same replicated values, different from the ones on the master. 

I have verified this behaviour on these platforms:
5.0.30 Debian GNU/Linux 
5.1.14-beta MacOS X
5.0.20 Windows XP

How to repeat:
delimiter //
create procedure test_replication_sp()
begin
create table t(a double);
insert into t values(rand());
insert into t values(rand());
select * from t;
drop table t;
end//

delimiter ;

call test_replication_sp();

show binlog events in 'your_binlog' from 'your_starting_position';
[11 Jan 2007 13:37] Valeriy Kravchuk
Thank you for a problem report. Looks like this is expected and even a bit documented behaviour. Read http://dev.mysql.com/doc/refman/5.0/en/stored-procedure-logging.html, for example:

"RAND() also replicates correctly as long as it is invoked only once within a function. (You can consider the function execution timestamp and random number seed as implicit inputs that are identical on the master and slave.)"

So, this is either a (valid) feature request (problem should be solved with row-based replication in 5.1.x, BTW), or a request for more clear documentation of this current limitation. Do you agree?
[11 Jan 2007 14:33] Corrado Pandiani
I have read all this chapter before sending the bug report and it seems to me to refer explicitly to stored function, not to stored procedure. In fact the paragraph about RAND() is a sub-paragraph of this one:
"When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication. Two sets of function characteristics apply here:"
It says "stored function" explicitly.

The manual says also that in 5.0.12 stored procedure replication switched from the replication of the CALL statement to the replication of single internal queries, and doing so could have been solved the problem of the RAND() values replication.   

Maybe it's not a bug, but the documentation is not clear and referers only to stored function. 
Do you agree?
[11 Jan 2007 14:53] Guilhem Bichot
Valeriy,
Corrado is right. The doc is about stored functions. For stored procedures, written to the binary log statement by statement, each statement could and should get the proper rand seed binlogged before this statement. A real bug :(
[11 Jan 2007 15:28] Valeriy Kravchuk
Sorry, for procedures this is really a (serious) bug. Verified just as described with latest 5.0.34-BK on Linux. I've got in the binary log:

...
*************************** 5. row ***************************
   Log_name: suse-bin.000015
        Pos: 518
 Event_type: Query
  Server_id: 1
End_log_pos: 613
       Info: use `test`; create table t(a double)
*************************** 6. row ***************************
   Log_name: suse-bin.000015
        Pos: 613
 Event_type: RAND
  Server_id: 1
End_log_pos: 648
       Info: rand_seed1=341358823,rand_seed2=729055694
*************************** 7. row ***************************
   Log_name: suse-bin.000015
        Pos: 648
 Event_type: Query
  Server_id: 1
End_log_pos: 747
       Info: use `test`; insert into t values(rand())
*************************** 8. row ***************************
   Log_name: suse-bin.000015
        Pos: 747
 Event_type: RAND
  Server_id: 1
End_log_pos: 782
       Info: rand_seed1=341358823,rand_seed2=729055694
*************************** 9. row ***************************
   Log_name: suse-bin.000015
        Pos: 782
 Event_type: Query
  Server_id: 1
End_log_pos: 881
       Info: use `test`; insert into t values(rand())
*************************** 10. row ***************************
   Log_name: suse-bin.000015
        Pos: 881
 Event_type: RAND
  Server_id: 1
End_log_pos: 916
       Info: rand_seed1=341358823,rand_seed2=729055694
*************************** 11. row ***************************
   Log_name: suse-bin.000015
        Pos: 916
 Event_type: Query
  Server_id: 1
End_log_pos: 999
       Info: use `test`; drop table t
11 rows in set (0.00 sec)
[8 Feb 2007 17:26] Chuck Bell
REFINED PROBLEM DESCRIPTION
The random seed values are not being used to calculate the value of the rand() function calls on the slave when used in a stored procedure or function. The code is designed to transmit the seed values from the master only once. Code needs to be changed to detect this condition and transmit the master's current seed values when rand() is called and replicated to the slave.
[8 Feb 2007 17:36] Chuck Bell
INITIAL PROPOSAL
Modify the code to add a condition to the existing code (Line # 2070).

    if (!thd->rand_used)
    {
      thd->rand_used= 1;
      thd->rand_saved_seed1= thd->rand.seed1;
      thd->rand_saved_seed2= thd->rand.seed2;
    }

This new code should detect when a second call is made to rand() that is not inside a sub statement (!thd->in_sub_stmt).

I also propose to add a test to rpl_misc_functions.test that tests a stored procedure and function that calls rand() multiple times.
[9 Feb 2007 10:01] Guilhem Bichot
Chuck,
it may be that you can get inspiration from THD::cleanup_after_query() as it is in 5.1; see how it resets the autoinc value used for binlogging, if not in a stored function/trigger.
[12 Feb 2007 22:56] Chuck Bell
Solution was to modify item_func.cc to include code to save the random seeds if executing multiple calls to rand() and not inside a sub statement.
[9 Mar 2007 17:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21636

ChangeSet@1.2425, 2007-03-09 12:18:28-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, the server does 
  not binlog the correct random seed values.
  
  This patch corrects the problem by resetting rand_used= 0 in 
  THD::cleanup_after_query() allowing the system to save the random seeds if needed
  for each command in a stored procedure body.
  
  However, rand_used is not reset if executing in a stored function or trigger 
  because these operations are binlogged by call and thus only the calling statement
  need detect the call to rand() made by its substatements. These substatements must 
  not set rand_used to 0 because it would remove the detection of rand() by the 
  calling statement.
[9 Mar 2007 17:54] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21640

ChangeSet@1.2425, 2007-03-09 12:18:28-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, the server does 
  not binlog the correct random seed values.
  
  This patch corrects the problem by resetting rand_used= 0 in 
  THD::cleanup_after_query() allowing the system to save the random seeds if needed
  for each command in a stored procedure body.
  
  However, rand_used is not reset if executing in a stored function or trigger 
  because these operations are binlogged by call and thus only the calling statement
  need detect the call to rand() made by its substatements. These substatements must 
  not set rand_used to 0 because it would remove the detection of rand() by the 
  calling statement.
[9 Mar 2007 17:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21642

ChangeSet@1.2425, 2007-03-09 12:18:28-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, the server does 
  not binlog the correct random seed values.
  
  This patch corrects the problem by resetting rand_used= 0 in 
  THD::cleanup_after_query() allowing the system to save the random seeds if needed
  for each command in a stored procedure body.
  
  However, rand_used is not reset if executing in a stored function or trigger 
  because these operations are binlogged by call and thus only the calling statement
  need detect the call to rand() made by its substatements. These substatements must 
  not set rand_used to 0 because it would remove the detection of rand() by the 
  calling statement.
[10 Mar 2007 0:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21665

ChangeSet@1.2425, 2007-03-09 12:18:28-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, the server does 
  not binlog the correct random seed values.
  
  This patch corrects the problem by resetting rand_used= 0 in 
  THD::cleanup_after_query() allowing the system to save the random seeds if needed
  for each command in a stored procedure body.
  
  However, rand_used is not reset if executing in a stored function or trigger 
  because these operations are binlogged by call and thus only the calling statement
  need detect the call to rand() made by its substatements. These substatements must 
  not set rand_used to 0 because it would remove the detection of rand() by the 
  calling statement.
[19 Mar 2007 15:17] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22275

ChangeSet@1.2425, 2007-03-09 12:18:28-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, the server does 
  not binlog the correct random seed values.
  
  This patch corrects the problem by resetting rand_used= 0 in 
  THD::cleanup_after_query() allowing the system to save the random seeds if needed
  for each command in a stored procedure body.
  
  However, rand_used is not reset if executing in a stored function or trigger 
  because these operations are binlogged by call and thus only the calling statement
  need detect the call to rand() made by its substatements. These substatements must 
  not set rand_used to 0 because it would remove the detection of rand() by the 
  calling statement.
[19 Mar 2007 15:17] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22276

ChangeSet@1.2425, 2007-03-09 12:18:28-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, the server does 
  not binlog the correct random seed values.
  
  This patch corrects the problem by resetting rand_used= 0 in 
  THD::cleanup_after_query() allowing the system to save the random seeds if needed
  for each command in a stored procedure body.
  
  However, rand_used is not reset if executing in a stored function or trigger 
  because these operations are binlogged by call and thus only the calling statement
  need detect the call to rand() made by its substatements. These substatements must 
  not set rand_used to 0 because it would remove the detection of rand() by the 
  calling statement.
[19 Mar 2007 15:18] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22277

ChangeSet@1.2425, 2007-03-09 12:18:28-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, the server does 
  not binlog the correct random seed values.
  
  This patch corrects the problem by resetting rand_used= 0 in 
  THD::cleanup_after_query() allowing the system to save the random seeds if needed
  for each command in a stored procedure body.
  
  However, rand_used is not reset if executing in a stored function or trigger 
  because these operations are binlogged by call and thus only the calling statement
  need detect the call to rand() made by its substatements. These substatements must 
  not set rand_used to 0 because it would remove the detection of rand() by the 
  calling statement.
[26 Mar 2007 13:37] Chuck Bell
Patch pushed.
[31 Mar 2007 23:53] Bugs System
Pushed into 5.0.40
[31 Mar 2007 23:54] Bugs System
Pushed into 5.1.18-beta
[3 Apr 2007 22:48] Paul Dubois
Noted in 5.0.40, 5.1.18 changelogs.

When RAND() was called multiple times inside a stored procedure, the
server did not write the correct random seed values to the binary
log, resulting in incorrect replication.