| Bug #49222 | Mark RAND() as unsafe | ||
|---|---|---|---|
| Submitted: | 30 Nov 2009 17:46 | Modified: | 15 Mar 2010 4:57 |
| Reporter: | Sven Sandberg | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.1+ | OS: | Any |
| Assigned to: | Sven Sandberg | CPU Architecture: | Any |
| Tags: | binlog, Rand(), unsafe | ||
[30 Nov 2009 18:15]
Valeriy Kravchuk
Verified just as described with 5.1.42 on Mac OS X:
77-52-1-11:mysql-test openxs$ ./mysql-test-run.pl bug_49222
Logging: ./mysql-test-run.pl bug_49222
091130 20:12:32 [Warning] Setting lower_case_table_names=2 because file system for /var/folders/dX/dXCzvuSlHX4Op1g-o1jIWk+++TI/-Tmp-/GNtSR16LRi/ is case insensitive
091130 20:12:32 [Note] Plugin 'FEDERATED' is disabled.
091130 20:12:32 [Note] Plugin 'ndbcluster' is disabled.
MySQL Version 5.1.42
Checking supported features...
- using ndbcluster when necessary, mysqld supports it
- SSL connections supported
- binaries are debug compiled
Collecting tests...
- adding combinations for rpl
vardir: /Users/openxs/dbs/5.1/mysql-test/var
Checking leftover processes...
Removing old var directory...
Creating var directory '/Users/openxs/dbs/5.1/mysql-test/var'...
Installing system database...
Using server port 56146
==============================================================================
TEST RESULT TIME (ms)
------------------------------------------------------------
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
rpl.bug_49222 'row' [ skipped ] Doesn't support --binlog-format='row'
rpl.bug_49222 'stmt' [ skipped ] Doesn't support --binlog-format='statement'
rpl.bug_49222 'mix' [ fail ]
Test ended at 2009-11-30 20:12:40
CURRENT_TEST: rpl.bug_49222
--- /Users/openxs/dbs/5.1/mysql-test/suite/rpl/r/bug_49222.result 2009-11-30 21:07:33.000000000 +0300
+++ /Users/openxs/dbs/5.1/mysql-test/suite/rpl/r/bug_49222.reject 2009-11-30 21:12:39.000000000 +0300
@@ -0,0 +1,24 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+[on master]
+CREATE TABLE t1 (a INT, b INT);
+SET SQL_LOG_BIN = 0;
+INSERT INTO t1 VALUES (1, 1), (2, 2);
+SET SQL_LOG_BIN = 1;
+[on slave]
+INSERT INTO t1 VALUES (2, 2), (1, 1);
+[on master]
+UPDATE t1 SET b = RAND() * 1000;
+SELECT * FROM t1;
+a b
+1 679
+2 460
+[on slave]
+SELECT * FROM t1;
+a b
+2 679
+1 460
[2 Dec 2009 12:36]
Sergei Golubchik
Nothing specific for RAND here - everything that depends on the record order has this problem, e.g. DELETE FROM t1 LIMIT 5
[2 Dec 2009 12:42]
Sven Sandberg
That's right, and for this reason we mark statements using LIMIT as unsafe.
[5 Jan 2010 16:06]
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/96002 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.
[13 Jan 2010 9:00]
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/96707 3313 Sven Sandberg 2010-01-13 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/r/rpl_row_func003.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.
[13 Jan 2010 16:53]
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/96798 3823 Sven Sandberg 2010-01-13 [merge] merged BUG#49222 from mysql-5.1-bugteam to mysql-pe
[13 Jan 2010 17:31]
Sven Sandberg
pushed to 5.1-bugteam and pe.
[15 Jan 2010 8:59]
Bugs System
Pushed into 5.1.43 (revid:joro@sun.com-20100115085139-qkh0i0fpohd9u9p5) (version source revid:martin.hansson@sun.com-20100113113806-5742ed1swr9rtb4b) (merge vers: 5.1.43) (pib:16)
[19 Jan 2010 9:49]
Jon Stephens
Documented in the 5.1.43 changelog as follows:
The RAND() function is now marked as unsafe for statement-based
replication. Using this function now generates a warning when
binlog_format=STATEMENT and causes the the format to switch to
row-based logging when binlog_format=MIXED.
This change is being introduced because, when RAND() was logged
in statement mode, the seed was also written to the binary log,
so the replication slave generated the same sequence of random
numbers as was generated on the master. While this could make
replication work in some cases, the order of affected rows was
still not guaranteed when this function was used in statements
such as UPDATE or INSERT...SELECT; if the master and the slave
retrieved rows in different order, they began to diverge.
Also noted change in RAND() function description and replication-features-functions.
Set NDI state, waiting for additional merges.
[5 Feb 2010 11:46]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100204063540-9czpdmpixi3iw2yb) (version source revid:alik@sun.com-20100119163614-172adculixyu26j5) (pib:16)
[5 Feb 2010 11:53]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100205113942-oqovjy0eoqbarn7i) (version source revid:alik@sun.com-20100204064210-ljwanqvrjs83s1gq) (merge vers: 6.0.14-alpha) (pib:16)
[5 Feb 2010 11:58]
Bugs System
Pushed into 5.5.2-m2 (revid:alik@sun.com-20100203172258-1n5dsotny40yufxw) (version source revid:alexey.kopytov@sun.com-20100115112653-e3a24041ag1cv6v3) (merge vers: 5.5.1-m2) (pib:16)
[8 Feb 2010 15:48]
Jon Stephens
Also documented in the 5.5.2 and 6.0.14 changelogs. Closed.
[12 Mar 2010 14:06]
Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:22]
Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:36]
Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[15 Mar 2010 4:57]
Jon Stephens
No additional changelog entries needed. Setting back to Closed state.
Description: When a statement that uses the RAND() function is replicated in statement format, the random seed is saved to the binlog so that the query shall replicate correctly in most cases. However, this is not enough in cases where the value of RAND() is inserted into several rows, because the order in which rows are retreived is not specified. In particular, if the order rows are retrieved on master differs from the order rows are retrieved on slave, then statements like the following can create differences on master and slave; UPDATE t1 SET a = RAND(); How to repeat: --source include/have_binlog_format_mixed.inc --source include/master-slave.inc --echo [on master] CREATE TABLE t1 (a INT, b INT); SET SQL_LOG_BIN = 0; INSERT INTO t1 VALUES (1, 1), (2, 2); SET SQL_LOG_BIN = 1; --echo [on slave] --sync_slave_with_master INSERT INTO t1 VALUES (2, 2), (1, 1); --echo [on master] --connection master UPDATE t1 SET b = RAND() * 1000; SELECT * FROM t1; --echo [on slave] --sync_slave_with_master SELECT * FROM t1; Suggested fix: Mark RAND() as unsafe: === modified file 'sql/item_create.cc' --- sql/item_create.cc 2009-08-28 16:21:54 +0000 +++ sql/item_create.cc 2009-11-30 17:33:42 +0000 @@ -4173,6 +4173,16 @@ 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: {