MySQL Bugs: #20633: INSERT DELAYED RAND() or @user_var does not replicate statement-based

Bug #20633	INSERT DELAYED RAND() or @user_var does not replicate statement-based
Submitted:	22 Jun 2006 19:09	Modified:	4 Aug 2006 0:32
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1-bk	OS:	Linux (linux)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
didn't test 5.0.
After running this test:
source include/master-slave.inc;
create table t1 (a int, b float);
insert delayed into t1 values(1,rand());
set @a=2.345;
insert delayed into t1 values(2,@a);
select * from t1;
sync_slave_with_master;
select * from t1;
connection master;
drop table t1;
sync_slave_with_master;
the content of t1 on master and slave differ (the random numbers differ, and the 2nd line of t1 on slave contains NULL in its second column)

How to repeat:
see testcase above

Here is a proposed patch which, if the server is in mixed mode, makes the delayed_insert system thread always use RBB. The comment is not very good.

sql/sql_insert.cc:

@@ -1259,6 +1259,15 @@
     thd.command=COM_DELAYED_INSERT;
     thd.lex->current_select= 0; 		// for my_message_sql
     thd.lex->sql_command= SQLCOM_INSERT;        // For innodb::store_lock()
+#ifdef HAVE_ROW_BASED_REPLICATION
+    /*
+      In mixed mode, using row-based for the delayed thread solves many
+      problems (otherwise we can have in the same bunch, some rows
+      statement-based and some rows row-based, and it's too late to generate
+      the table maps then).
+    */
+    thd.set_current_stmt_binlog_row_based_if_mixed();
+#endif
 
     bzero((char*) &thd.net, sizeof(thd.net));		// Safety
     bzero((char*) &table_list, sizeof(table_list));	// Safety

See also BUG#20649

will be fixed only in the mixed mode (INSERT DELAYED will then always use row-based), by fix of #20649. As #20649 is approved, so is this one; simply a testcase (like the one in this bug report) will be added.

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/8864

Will not be fixed in 5.0.
Pushed in replication team tree 5.1, will be in 5.1.12.
ChangeSet
  guilhem@gbichot3.local|ChangeSet|20060706155404|16502    2006/07/06 17:54:04+02:00 guilhem@gbichot3.local +4 -0
  Fix for BUG#20649 "mixed replication mode does not work with INSERT DELAYED".
  The bug was that if the server was running in mixed binlogging mode,
  and an INSERT DELAYED used some needing-row-based components like UUID(),
  the server didn't binlog this row-based but statement-based, which
  thus failed to insert correct data on the slave.
  This changeset implements that when a delayed_insert thread is created,
  if the server's global binlog mode is "mixed", that thread will use row-based.
  This also fixes BUG#20633 "INSERT DELAYED RAND() or @user_var does not
  replicate statement-based": we don't fix it in statement-based mode (would
  require bookeeping of rand seeds and user variables used by each row),
  but at least it will now work in mixed mode (as row-based will be used).

By the way, patch had been approved by email by Lars (but he's on vacation now).

Noted in 5.1.12 changelog.