MySQL Bugs: #90415: SHOW CREATE TABLE not including table options

Bug #90415	SHOW CREATE TABLE not including table options
Submitted:	12 Apr 2018 19:24	Modified:	13 Apr 2018 13:57
Reporter:	Todd Keup	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Microsoft Windows (Windows 7 SP1)
Assigned to:		CPU Architecture:	x86 (64bit)
Tags:	cli, windows

Description:
The SHOW CREATE TABLE command line execution is not showing the table_options section in the definition output.

How to repeat:
Login to command line interface with proper privileges (in this case, FULL privileges):

CREATE TABLE t (
    myId INTEGER UNSIGNED AUTO_INCREMENT NOT NULL,
    col1 CHAR(1) DEFAULT 'A',
    PRIMARY KEY(myId),
    INDEX(col1)
) ENGINE=InnoDB AUTO_INCREMENT=1000001;
SHOW CREATE TABLE t\G

*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE "t" (
  "myId" int(10) unsigned NOT NULL AUTO_INCREMENT,
  "col1" char(1) COLLATE utf8_unicode_ci DEFAULT 'A',
  PRIMARY KEY ("myId"),
  KEY "col1" ("col1")
)
1 row in set (0.00 sec)

Insert some values, with and without AUTO_INCREMENT

INSERT INTO t (myId,col1) VALUES(1,'A'),(2,'A');
SHOW CREATE TABLE t\G
INSERT INTO t (myId,col1) VALUES(NULL,'A');
SHOW CREATE TABLE t\G

Output for SHOW CREATE TABLE every time is without table_options, same as shown earlier.

Suggested fix:
Add table_options to output for command line on Windows architecture.

Thank you for the bug report. I couldn't repeat with source build:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.23-log Source distribution 2018-MAR-08

Copyright (c) 2000, 2018, 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 5.7 > USE q
Database changed
mysql 5.7 > CREATE TABLE t (
    ->     myId INTEGER UNSIGNED AUTO_INCREMENT NOT NULL,
    ->     col1 CHAR(1) DEFAULT 'A',
    ->     PRIMARY KEY(myId),
    ->     INDEX(col1)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1000001;
Query OK, 0 rows affected (0.06 sec)

mysql 5.7 > SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `myId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col1` char(1) DEFAULT 'A',
  PRIMARY KEY (`myId`),
  KEY `col1` (`col1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql 5.7 >

obviously an sql_mode issue.  Try:  set sql_mode='';   first.

You are spot on, Shane.  I'm running in ANSI mode.  From my startup options:

sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ANSI

And the command line for my session:

SELECT @@SESSION.sql_mode\G
*************************** 1. row ***************************
@@SESSION.sql_mode: REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

SET SESSION sql_mode = "NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES";
SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `myId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col1` char(1) COLLATE utf8_unicode_ci DEFAULT 'A',
  PRIMARY KEY (`myId`),
  KEY `col1` (`col1`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I didn't realize that ANSI mode would have this effect.  Back to the INFORMATION_SCHEMA tables I guess :)

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't'\G

Although it would be nice to see the MySQL SHOW statement updated to reflect the table_options information.
Can we change this non-bug to a feature request now instead?
:-)