Bug #20821 INSERT DELAYED fails to write some rows to binlog
Submitted: 3 Jul 2006 13:41 Modified: 13 Jul 2006 20:09
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: Mats Kindahl CPU Architecture:Any

[3 Jul 2006 13:41] Guilhem Bichot
Description:
Under concurrency, several INSERT DELAYED queries issued by different threads get groupped and executed as one bunch by the system delayed_insert thread:
bool delayed_insert::handle_inserts(void)
{

<cut>
  while ((row=rows.get()))
  {
<cut>
    if (write_record(&thd, table, &info))
    {
      info.error_count++;				// Ignore errors
      thread_safe_increment(delayed_insert_errors,&LOCK_delayed_status);
      row->log_query = 0;
    }
<cut>
  }

  thd.proc_info=0;
  pthread_mutex_unlock(&mutex);

  /* After releasing the mutex, to prevent deadlocks. */
  if (mysql_bin_log.is_open())
    thd.binlog_query(THD::ROW_QUERY_TYPE, query, query_length, FALSE, FALSE);

But (see above) we binlog only one query for the bunch.
Specific of 5.1.

How to repeat:
reset master;
create database if not exists mysqlslap;
use mysqlslap;

CREATE TABLE t1 (id int, name varchar(64));

--exec $MYSQL_SLAP --silent --concurrency=5 --iterations=200 --query="INSERT DELAYED INTO t1 VALUES (1, 'This is a test')" --delimiter=";"

sleep 5;
select count(*) from t1;

=> the COUNT(*) will say 1000, but if you do:
../client/mysqlbinlog var/log/master-bin.000001 |grep DELAYED | wc -l
645
Another run showed 610.
I.e. hundreds of INSERT DELAYED didn't find their way to binlog.

Suggested fix:
Move back the binlogging inside the while loop?
[5 Jul 2006 6:20] 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/8732
[5 Jul 2006 14:59] 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/8775
[8 Jul 2006 4:15] 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/8949
[13 Jul 2006 20:09] Paul Dubois
Noted in 5.1.12 changelog.

For multiple INSERT DELAYED statements executed in a batch thby e the delayed-insert handler thread, not all rows were written to the binary log.