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: | |
Category: | MySQL Server: Command-line Clients | Severity: | S3 (Non-critical) |
Version: | 5.7 | OS: | Windows (Windows 7 SP1) |
Assigned to: | CPU Architecture: | x86 (64bit) | |
Tags: | cli, windows |
[12 Apr 2018 19:24]
Todd Keup
[13 Apr 2018 0:22]
MySQL Verification Team
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 >
[13 Apr 2018 5:34]
MySQL Verification Team
obviously an sql_mode issue. Try: set sql_mode=''; first.
[13 Apr 2018 13:57]
Todd Keup
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? :-)