MySQL Bugs: #20649: mixed replication mode does not work with INSERT DELAYED

Bug #20649	mixed replication mode does not work with INSERT DELAYED
Submitted:	23 Jun 2006 7:42	Modified:	3 Aug 2006 17:05
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:
Rows of different INSERT DELAYED statements against the same table get groupped together by the delayed_insert thread: the table is locked and then the group is inserted and then the table is unlocked.
Currently the information that a row should be binlogged row-based is not passed to the delayed_insert thread.
Even if it was, it is not enough to use row-based for this row: as the table has been locked earlier, it's too late to switch to row-based (because row-based binlogging takes action when tables are locked, so cannot be switched on after the lock has been taken).

How to repeat:
CREATE TABLE t (a varchar(100)) engine=myisam;
INSERT DELAYED INTO t SELECT UUID();
# compare content of "t" on master and slave

Suggested fix:
I have a very short patch for this: in mixed mode, we just make the delayed_insert thread always use row-based binlogging. As INSERT DELAYED is limited to INSERT VALUES, row-based will not take more binlog space than statement-based.

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

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

Pushed into 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.
...

Noted in 5.1.12 changelog.

With mixed-format binary logging, INSERT DELAYED statements were
logged using statement-based logging, and they did not replicate
properly for statements that used values such as UUID() that require
row-based logging.  To correct this, the delayed handler thread how
switches to row-based logging if the logging format is mixed.