Bug #72367 DELAYED is documented as deprecated, but it is acutally non-functional

• Submitted: 17 Apr 2014 11:48
• Modified: 17 Apr 2014 13:03
• Reporter: Peter Laursen (Basic Quality Contributor)
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.7.4
• OS: Any
• CPU Architecture: Any

[17 Apr 2014 11:48] Peter Laursen

Description:
We have a customer who is not able to restore a backup due to this.

http://dev.mysql.com/doc/refman/5.7/en/insert-delayed.html tells:

"DELAYED inserts and replaces were deprecated in MySQL 5.6.6. In MySQL 5.7, DELAYED is not supported. The server recognizes but ignores the DELAYED keyword, handles the insert as a nondelayed insert, and generates an ER_WARN_LEGACY_SYNTAX_CONVERTED warning. (“INSERT DELAYED is no longer supported. The statement was converted to INSERT.”) The DELAYED keyword will be removed in a future release."

.. but is is not the case with 5.7.4.  DELAYED retruns a syntax error and not a warning

How to repeat:
SELECT VERSION(); -- 5.7.4-m14-log 
SHOW VARIABLES LIKE 'sql_mode'; -- STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (if it matters)
CREATE TABLE delay (id INT); -- success
INSERT DELAYED INTO delay VALUES (1); -- syntax error 1064
INSERT INTO delay VALUES (1); -- success

Suggested fix:
It is critical that documentation is fully up to date with actual server support when it comes to DDL adn DML as it afffects data restore from a SQL dump.

[17 Apr 2014 12:58] Peter Laursen

hmmm .. MyISAM works as expected:

CREATE TABLE moredelay (id INT) ENGINE = MYISAM;
INSERT DELAYED INTO moredelay VALUES (1);
-- Warning Code : 1888
-- INSERT DELAYED is no longer supported. The statement was converted to INSERT

[17 Apr 2014 13:03] Peter Laursen

and MySQL 5.5:

CREATE TABLE delay (id INT) ENGINE = INNODB; -- success
INSERT DELAYED INTO delay VALUES (1);
-- Error Code: 1616
-- DELAYED option not supported for table 'delay'

.. so this is 'not a bug'.