MySQL Bugs: #62072: string 'default' is a valid ENGINE specification?

Bug #62072	string 'default' is a valid ENGINE specification?
Submitted:	3 Aug 2011 15:22	Modified:	21 Mar 2013 2:26
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.58, 5.1.59	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
This gotcha I don't find documented in 
http://dev.mysql.com/doc/refman/5.1/en/create-table.html

An ENGINE specification of CREATE TABLE you may quote the engine name. Even "ENGINE= 'default'" is a valid specification, ENGINE = DEFAULT is not (inconsistence! :-))

How to repeat:
SET SQL_MODE = 'no_engine_substitution';
DROP TABLE IF EXISTS `test`.`tt`;
CREATE TABLE `test`.`tt`( `txt` VARCHAR(50) ) ENGINE= DEFAULT; -- 1064 syntax error
CREATE TABLE `test`.`tt`( `txt` VARCHAR(50) ) ENGINE= 'default'; -- success
CREATE TABLE `test`.`ttt`( `txt` VARCHAR(50) ) ENGINE= 'peter'; -- Error Code: 1286 - Unknown table engine 'peter'
CREATE TABLE `test`.`tttt`( `txt` VARCHAR(50) ) ENGINE= 'archive'; -- success

(
table *does* get created as 

CREATE TABLE `tttt` (
  `txt` VARCHAR(50) DEFAULT NULL
) ENGINE=ARCHIVE DEFAULT CHARSET=utf8)

Suggested fix:
Document it?

Verified just as described with current mysql-5.1 from bzr on Mac OS X.

What is worse, the resulting table definition from SHOW CREATE TABLE can NOT be copy/pasted to get a valid table:

macbook-pro:5.1 openxs$ 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 10
Server version: 5.1.59 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show create table tt\G
*************************** 1. row ***************************
       Table: tt
Create Table: CREATE TABLE `tt` (
  `txt` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> CREATE TABLE `test`.`tt2`( `txt` VARCHAR(50) ) ENGINE=MyISAM DEFAULT; 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> CREATE TABLE `test`.`tt2`( `txt` VARCHAR(50) ) ENGINE=DEFAULT; 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT' at line 1
mysql> CREATE TABLE `test`.`tt2`( `txt` VARCHAR(50) ) ENGINE=MyISAM; 
Query OK, 0 rows affected (0.18 sec)

So I expect problems for mysqdump with resulting table definition, and so on...

There is no problem with the SHOW CREATE TABLE output, it says "DEFAULT CHARSET", which is a perfectly valid. Also, 'DEFAULT' and DEFAULT are completely different things for the parser, the former is a string while the latter is a token.

OK, my fault, I had to copy/paste properly before making conclusions:

mysql> CREATE TABLE `tt` (
    ->   `txt` varchar(50) DEFAULT NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
    -> ;
Query OK, 0 rows affected (0.06 sec)

But facts that engine name can be quoted:

mysql> CREATE TABLE `tt3` (   `txt` varchar(50) DEFAULT NULL ) ENGINE='InnoDB';
Query OK, 0 rows affected (0.27 sec)

and that 'DEFAULT' can be used to use default engine (unlike DEFAULT as a token):

mysql> CREATE TABLE `tt` (   `txt` varchar(50) DEFAULT NULL ) ENGINE='DEFAULT';
Query OK, 0 rows affected (0.06 sec)

still should be documented.

@Valeriy .. I do not understand you copy/paste backup/restore remark/concern.

CREATE TABLE xxx (txt VARCHAR(20)) ENGINE = 'default';
SHOW CREATE TABLE xxx;

-- returns (as my default engine is InnoDB)
/*
CREATE TABLE `xxx` (
  `txt` VARCHAR(20) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8
*/

This will restore.  From where/what client do you copy/paste?

Peter,

Ignore that my wrong statement about mysqldump. Davi already corrected me.

This is still a valid documentation request at least.

sorry .. I did not see Davi's comment before pasting.

I do not see any *functional* problems (and agree wiht Davi here). But ..

1) in this particular context the string 'archive' is considered the same as the TOKEN/KEYWORD/RESERVERED WORD "ARCHIVE". This is very 'weak typing' in the parser. A 'quoted string' should not be confused with a TOKEN (but some client may use it so better keep it for a while at least). Also see #3) below.

2) the particular string 'default' is accepted as ann ENGINE specification and creates a table with *default* engine. This is not documented as far as I can see. 

3) #2) is inconsistent with this:
CREATE TABLE yyy (txt VARCHAR(20)) CHARSET = 'default';
-- Error Code: 1115 Unknown character set: 'default'
.. if 'default' is valid as an ENGINE specification why is it not as a CHARSET specification (or whatever parameters CREATE TABLE takes)? 

I have been so long with MySQL related business that I can understand what happened here *historically*. TYPE/ENGINE is a very old parameter for CREATE TABLE (from version 3.x at least) . CHARSET parameter for CREATE TABLE is newer (from 4.1). What tells that features introduced in recent versions have better code quality than very old versions.  But recent versions still have inherited some bad code quality (here at least) from old versions.

Looks like 'DEFAULT' creates a table with the default storage engine up through 5.5.

In 5.6+, 'DEFAULT' is accepted but ignored.

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.

Revision for 5.0 - 5.5:

The ENGINE table option specifies the storage engine for the table,
using one of the names shown in the following table. The engine name
can be unquoted or quoted. The quoted name 'DEFAULT' is equivalent to
specifying the default storage engine name.

Revision for 5.6+:

The ENGINE table option specifies the storage engine for the table,
using one of the names shown in the following table. The engine name
can be unquoted or quoted. The quoted name 'DEFAULT' is recognized
but ignored.