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

[23 Jun 2006 7:42] Guilhem Bichot
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.
[3 Jul 2006 14:48] Guilhem Bichot
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
[3 Jul 2006 14:52] Guilhem Bichot
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
[11 Jul 2006 8:22] Guilhem Bichot
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.
...
[3 Aug 2006 17:05] Paul Dubois
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.