MySQL Bugs: #35386: Insert delayed statements insert 1 + limit records instead of just limit records

Bug #35386	Insert delayed statements insert 1 + limit records instead of just limit records
Submitted:	18 Mar 2008 9:39	Modified:	17 Sep 2014 4:39
Reporter:	Rizwan Maredia	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.22	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	delayed_insert_limit, insert delayed

Description:
If an insert delayed statements are queued and there has been a limit set on max insert delayed statements to be executed at once. The insert delayed statements that actually get executed are 1 more than the limit set.

How to repeat:
Here is how can reproduce this bug in mysql test environment

CREATE TABLE t1 (a varchar(10));
SET GLOBAL delayed_insert_limit = 2;

connect (con0,localhost,root,,);
connect (con1,localhost,root,,);

connection default;
LOCK TABLE t1 WRITE;

connection con1;

delimiter |;
send
INSERT DELAYED INTO t1 VALUES('1');
INSERT DELAYED INTO t1 VALUES('2');
INSERT DELAYED INTO t1 VALUES('3');
INSERT DELAYED INTO t1 VALUES('4');
INSERT DELAYED INTO t1 VALUES('5');|
delimiter ;|

connection con0;
send SELECT * FROM t1;

connection default;
--sleep 1
UNLOCK TABLES;

connection con0;
reap;

# the reap outputs following result

a
1
2
3

This shows that 3 records are inserted instead of 2.

Suggested fix:
The number of executed delayed inserts executed should be equal to the limit provided.

Thank you for the report.

Verified as described.

Workaround: Set limit to x-1

INSERT DELAYED has been deprecated in GA versions (5.5, 5.6). 

In 5.7.1, the INSERT/REPLACE DELAYED syntax remains, but it gets converted to INSERT/REPLACE with a warning before executing.