MySQL Bugs: #65846: INSERT DELAYED on a BLACKHOLE table hangs forever

Bug #65846	INSERT DELAYED on a BLACKHOLE table hangs forever
Submitted:	8 Jul 2012 18:17	Modified:	9 Sep 2016 11:23
Reporter:	Elena Stepanova	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.63, 5.5.25, 5.5.26	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The manual (http://dev.mysql.com/doc/refman/5.5/en/insert-delayed.html) says that INSERT DELAYED is supported for BLACKHOLE, but it does not work. It does not even work like a simple INSERT, it just hangs, even if the table is not being used by a concurrent thread. 

Setting lock_wait_timeout to a low value does not help (commented in the test; for 5.1, it should be global table_lock_wait_timeout). 

The same test with MyISAM works as expected. 

How to repeat:
CREATE TABLE t (i INT) ENGINE=BLACKHOLE;
SHOW CREATE TABLE t;
LOCK TABLE t READ;

--connect (con1,localhost,root,,)
#SET lock_wait_timeout=1;
INSERT DELAYED INTO t VALUES (1);

--connection default
UNLOCK TABLES;
DROP TABLE t;

Thank you for the bug report. Verified with 5.5.26:

macbook-pro:mysql-test openxs$ ./mtr bug65846
Logging: ./mtr  bug65846
120708 22:17:24 [Warning] Setting lower_case_table_names=2 because file system for /var/folders/dX/dXCzvuSlHX4Op1g-o1jIWk+++TI/-Tmp-/OxNGgaZIJD/ is case insensitive
120708 22:17:24 [Note] Plugin 'FEDERATED' is disabled.
MySQL Version 5.5.26
Checking supported features...
 - skipping ndbcluster
 - SSL connections supported
 - binaries are debug compiled
Collecting tests...
vardir: /Users/openxs/dbs/5.5/mysql-test/var
Checking leftover processes...
Removing old var directory...
Creating var directory '/Users/openxs/dbs/5.5/mysql-test/var'...
Installing system database...
Using server port 61686

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
worker[1] Trying to dump core for [mysqltest - pid: 56912, winpid: 56912]
worker[1] Trying to dump core for [mysqld.1 - pid: 56908, winpid: 56908]
main.bug65846                            [ fail ]  timeout after 900 seconds
        Test ended at 2012-07-08 22:33:45

Test case timeout after 900 seconds

== /Users/openxs/dbs/5.5/mysql-test/var/log/bug65846.log == 
CREATE TABLE t (i INT) ENGINE=BLACKHOLE;
SHOW CREATE TABLE t;
Table	Create Table
t	CREATE TABLE `t` (
  `i` int(11) DEFAULT NULL
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
LOCK TABLE t READ;

 == /Users/openxs/dbs/5.5/mysql-test/var/tmp/analyze-timeout-mysqld.1.err ==
mysqltest: Could not open connection 'default' after 500 attempts: 2002 Can't connect to local MySQL server through socket '/Users/openxs/dbs/5.5/mysql-test/var/tmp/mysqld.1.sock' (61)

 - saving '/Users/openxs/dbs/5.5/mysql-test/var/log/main.bug65846/' to '/Users/openxs/dbs/5.5/mysql-test/var/log/main.bug65846/'
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 0.000 of 985 seconds executing testcases
...