MySQL Bugs: #43936: insert delayed doesn't work when multiple inserting and SBR are used

Bug #43936	insert delayed doesn't work when multiple inserting and SBR are used
Submitted:	29 Mar 2009 5:53	Modified:	28 Apr 2009 15:57
Reporter:	Hao Wu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.32	OS:	Linux
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
"insert delayed" does not work when inserting multiple rows in one sql.

How to repeat:
test@127.0.0.1>create table a (i int) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

test@127.0.0.1>insert delayed into a values (1);
Query OK, 1 row affected (0.00 sec)

test@127.0.0.1>show status like 'delayed%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_errors         | 0     |
| Delayed_insert_threads | 1     |
| Delayed_writes         | 1     |
+------------------------+-------+
3 rows in set (0.01 sec)

test@127.0.0.1>insert delayed into a values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

test@127.0.0.1>show status like 'delayed%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_errors         | 0     |
| Delayed_insert_threads | 1     |
| Delayed_writes         | 1     |
+------------------------+-------+
3 rows in set (0.00 sec)

For me on Windows (32 bit Vista w. 32 bit MySQL 5.1.32 server):

SELECT VERSION();

/*
version()       
----------------
5.1.32-community  */

CREATE TABLE a (i INT) ENGINE=MYISAM;
INSERT DELAYED INTO a VALUES (1),(2);
SHOW STATUS LIKE 'delayed%';

/*
Variable_name           Value 
----------------------  ------
Delayed_errors          0     
Delayed_insert_threads  1     
Delayed_writes          2   */

INSERT DELAYED INTO a VALUES (1);
SHOW STATUS LIKE 'delayed%';

/*
Variable_name           Value 
----------------------  ------
Delayed_errors          0     
Delayed_insert_threads  1     
Delayed_writes          3 */

-- now FLUSH STATUS (or restarting server)

FLUSH STATUS;
TRUNCATE TABLE a;

INSERT DELAYED INTO a VALUES (1);
SHOW STATUS LIKE 'delayed%';

/* 
Variable_name           Value 
----------------------  ------
Delayed_errors          0     
Delayed_insert_threads  1     
Delayed_writes          1     */

INSERT DELAYED INTO a VALUES (1),(2);
SHOW STATUS LIKE 'delayed%';

/*
Variable_name           Value 
----------------------  ------
Delayed_errors          0     
Delayed_insert_threads  1     
Delayed_writes          3     */

So for me the 'Delayed_writes' status variable displays expected values. But 'Delayed_insert_threads' does not count above one.  I think that is not correct (even when as here the 'delayed_thread's for the two inserts are operating on the same table).  But that is then an issue with that specific status variable. The number of delayed inserts are correct here.

Peter 
(not a MySQL person)

Sorry, but I also can't repeat this with recent 5.1.34 on Linux:

openxs@suse:/home2/openxs/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.34-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table a (i int) engine=myisam;
Query OK, 0 rows affected (0.09 sec)

mysql> insert delayed into a values(1);
Query OK, 1 row affected (0.00 sec)

mysql> show status like 'delayed%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_errors         | 0     |
| Delayed_insert_threads | 1     |
| Delayed_writes         | 1     |
+------------------------+-------+
3 rows in set (0.00 sec)

mysql> insert delayed into a values(2),(3);
Query OK, 2 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show status like 'delayed%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_errors         | 0     |
| Delayed_insert_threads | 1     |
| Delayed_writes         | 3     |
+------------------------+-------+
3 rows in set (0.00 sec)

Try running server with binary logging enabled....
Notice, the "Records: 2" indicates a normal insert was done:

mysql> insert delayed into a values (1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show status like 'delayed%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_errors         | 0     |
| Delayed_insert_threads | 1     |
| Delayed_writes         | 1     |
+------------------------+-------+
3 rows in set (0.00 sec)

workaround: use --binlog_format=row

Indeed, if statement-based logging is used, INSERT DELAYED works as simple INSERT when inserting multiple rows. This may be intended, but http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html does NOT describe it.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Addition to
http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html:

INSERT DELAYED is treated as a normal INSERT if the statement inserts
multiple rows and binary logging is enabled and the global logging
format is to use statement-based logging (binlog_format is set to
STATEMENT). This restriction does not apply to row-based binary
logging.