MySQL Bugs: #73325: Incorrect behavior of 'MYSQL40' sql

Bug #73325	Incorrect behavior of 'MYSQL40' sql_mode
Submitted:	18 Jul 2014 11:25	Modified:	15 May 2015 9:44
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.5, 5.6	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Docs at http://dev.mysql.com/doc/refman/5.5/en/sql-mode.html#sqlmode_mysql40 says that "MYSQL40" is uquivalent to "NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE" - but it is not.

How to repeat:
1) 
SET sql_mode = 'NO_FIELD_OPTIONS';
SHOW CREATE TABLE t1;
/*
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `txt` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/

2) 
SET sql_mode = 'HIGH_NOT_PRECEDENCE';
SHOW CREATE TABLE t1;
/*
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `txt` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/

3)
SET sql_mode = 'mysql40';
SHOW VARIABLES LIKE 'sql_mode'; -- returns "MYSQL40,HIGH_NOT_PRECEDENCE" -- << this is nonsense in any case!
SHOW CREATE TABLE t1;
/*
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `txt` varchar(20) DEFAULT NULL
) TYPE=InnoDB
*/

With "MYSQL40" observe in particular that the old TYPE keyword replaces ENGINE. This is nowhere documented as far as I can see. Also none of the sql_modes "NO_FIELD_OPTIONS" and "HIGH_NOT_PRECEDENCE" causes TYPE to appear in SHOW CREATE TABLE. So "MYSQL40" is definitely != "NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE" as docs say.

The TYPE keyword causes issues with various copy/sync tools.

Suggested fix:
I don't know if the behavior is wrong, or if behavior is intended and the docs are wrong. Fix either.

In principle I'd like to see those ridiculous old sql_modes buried forever! And as soon as possible! :-)

SET sql_mode = 'NO_FIELD_OPTIONS,HIGH_NOT_PRECEDENCE';

SHOW VARIABLES LIKE 'sql_mode';
-- "NO_FIELD_OPTIONS,HIGH_NOT_PRECEDENCE"

SHOW CREATE TABLE t1; 
/*
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `txt` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/

Fixed typo!

Thank you for the report.

Verified as described.

I believe this is documentation bug, because if we care about compatibility with MySQL 4.0 we have to use keyword TYPE and if we use other SQL modes we have to use keyword ENGINE. Regarding to table option (DEFAULT CHARSET) I believe this can be 4.1+ feature (http://dev.mysql.com/doc/refman/4.1/en/news-4-1-0.html, "Character sets to be defined per column, table and database.").

well .. there are 3 issues actually.

1) TYPE appearing and not ENGINE.  This can indeed be considered intended behavior and a docs issue (a note should be added to description of MYSQL3 and MYSQL40 sql_modes about this).

2) But look at this:

SET sql_mode = 'mysql40';
SHOW VARIABLES LIKE 'sql_mode'; -- returns "MYSQL40,HIGH_NOT_PRECEDENCE"

This is inconsistent and contradicts the way "compound modes" are normaly returded - compare

SET sql_mode = 'traditional';
SHOW VARIABLES LIKE 'sql_mode'; -- returns STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION  
-- (id. the 'compound mode' itself is not returned).
-- this requires change in code and not only docs IMO.

3) It is incorrect when docs state that "MYSQL40" is equivalent to NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE (I don't know actually if sql_mode is set to NO_FIELD_OPTIONS, but "SHOW VARIABLES LIKE 'sql_mode';" does not list it.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_mysql323
http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_mysql40