Bug #66530 RAND() not mentioned as unsafe for replication in 5.5
Submitted: 24 Aug 2012 15:34 Modified: 30 Aug 2012 16:16
Reporter: Baron Schwartz (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1.66, 5.5.27 OS:Any
Assigned to: Paul DuBois CPU Architecture:Any
Tags: qc

[24 Aug 2012 15:34] Baron Schwartz
The changelog for 5.5.2 mentions that RAND() is now on the list of unsafe functions: http://dev.mysql.com/doc/refman/5.5/en/news-5-5-2.html

But this page needs to be updated to match: http://dev.mysql.com/doc/refman/5.5/en/replication-sbr-rbr.html

It states "However, all other functions are replicated correctly using statement-based replication, including RAND(), NOW(), and so forth."

Using RAND() with an update in statement-based mode gives an error, like this:

mysql> insert into t(a) values(rand());
120824 11:23:53 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. Statement: insert into t(a) values(rand())

How to repeat:
Doc bug.
[24 Aug 2012 16:29] Valeriy Kravchuk
Thank you for the problem report. 5.1 is also affected (including corresponding manual page):

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.66-debug-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table tr(c1 int);
Query OK, 0 rows affected (0.13 sec)

mysql> insert into tr values (rand()*10);
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1592
Message: Statement may not be safe to log in statement format.
1 row in set (0.00 sec)
[30 Aug 2012 16:16] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.