| 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: | |
| 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: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.
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';