MySQL Bugs: #41753: problems with DELAY_KEY

Bug #41753	problems with DELAY_KEY_WRITE
Submitted:	26 Dec 2008 10:28	Modified:	22 Feb 2009 15:30
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.67, 5.1.31	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
1) It is possible to create a table using DELAY_KEY_WRITE when this option is turned off globally. No error and no warning occurs!

I must assume the the table-level option is ignored if global setting is OFF (and takes effect should global setting later be set ON).  But nothing is documented and a warning should occur!

Also if tables using DELAY_KEY_WRITE option exists then the global variable can be turned OFF without the occurrence of a warning.

2) inconsistence: In SET statement values ON|OFF are accepted .. in CREATE TABLE they are not!

3) documentation http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_delay-key-write

.. mismatches hyphened form and underscored form here
"
Option Sets Variable	Yes, delay_key_write 
Variable Name	delay-key-write
"

How to repeat:
set GLOBAL DELAY_KEY_WRITE = off; -- success

CREATE TABLE `test`.`kwtest`(`id` int NOT NULL , PRIMARY KEY (`id`)) ENGINE = MyISAM DELAY_KEY_WRITE= on ; -- syntax error
CREATE TABLE `test`.`kwtest2`(`id` int NOT NULL , PRIMARY KEY (`id`)) ENGINE = MyISAM DELAY_KEY_WRITE=1 ; -- success
SHOW WARNINGS; -- empty set 

set session DELAY_KEY_WRITE = on; -- Variable 'delay_key_write' is a GLOBAL variable .... 

Suggested fix:
At least document and show warnings .. or maybe remove this (outdated ?) option from the server! Alternativly consider making it a session variable!

I agree with problems 2 and 3. http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_delay-key-write should be fixed for sure. But can you explain why do you think 1 is a problem/bug?

I think that if I create a table with an option (like DELAY_KEY_WRITE) I should *at least* be warned if the table-level option specified has no effect at the time of table creation (and same if option is turned OFF while tables with that create option exist).

Also it is not 100% clear to me what happens in this situation, really! I *assume* that the table create option is ignored as long as DELAY_KEY_WRITE is off globally and takes effect should it later be set ON (with or without server restart ??). But I find no clear docs on that!  Maybe the global setting at table create-time will prevent the table-level DELAY_KEY_WRITE *ever* to take effect? I cannot find any information!

I am not myself much interested in DELAY_KEY_WRITE (I would never use it myself probably), but we have a customer that uses it extensively.  This customer reported some problems with our application (that may or may not be related to use of DELAY_KEY_WRITE).  As long as the exact behaviour of this 'mismatched' server variable/table create option is not 100% clear we have difficulties doing progress debugging it!

So it would also be very nice if MySQL could confirm my assumption that the (table create) option is ignored as long as DELAY_KEY_WRITE is OFF globally and takes effect should it later be set ON globally and whether server restart is required or not!

and this is of course just a variation of same:

set GLOBAL DELAY_KEY_WRITE = all; -- success
create table `test`.`kwtest_more`(`id` int NOT NULL , PRIMARY KEY (`id`)) ENGINE = MyISAM DELAY_KEY_WRITE= 0 ; -- success
show warnings; -- empty set 

I may not necessarily know that some other user/application did set GLOBAL DELAY_KEY_WRITE = .. and I would be very surprised by a table behaviour different thna what was specified in the CREATE statement. Basically I think this is an example of a server variable that was forgotten/not handled when LOCAL|GLOBAL options for server variables were introduced in 5.0.3!

But I also think that this variable is some old crap originating back from server 2.x-3.x that could very well be removed now!

But it is still in MyISAM and Maria Engines of server 6.0.8 as this shows

set GLOBAL DELAY_key_write = all; -- success
create table `test`.`kwtest_more`(`id` int NOT NULL , PRIMARY KEY (`id`)) ENGINE = Maria DELAY_KEY_WRITE= 0 ; -- success
show warnings; -- empty set

OK. So, besides related documentation requests, we do have a problem that there no even warnings in case DELAY_KEY_WRITE setting for the table created contradicts global setting.