Bug #3352 SHOW CREATE TABLE missing information if sql_mode='ansi'

• Submitted: 31 Mar 2004 17:29
• Modified: 1 Apr 2004 5:57
• Reporter: Peter Gulutzan
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.2-alpha
• OS: Linux (SuSE 8.2/Windows XP)
• Assigned to: Sergei Golubchik
• CPU Architecture: Any

[31 Mar 2004 17:29] Peter Gulutzan

Description:
Usually SHOW CREATE TABLE shows the engine and the default character set. This 
information is missing if sql_mode='ansi'. 
 

How to repeat:
mysql> create table t (s1 int) character set latin2 engine=innodb; 
Query OK, 0 rows affected (0.08 sec) 
 
mysql> show create table t; 
+-------+---------------------------------------------------------------------------------------+ 
| Table | Create Table                                                                          | 
+-------+---------------------------------------------------------------------------------------+ 
| t     | CREATE TABLE `t` ( 
  `s1` int(11) default NULL 
) ENGINE=InnoDB DEFAULT CHARSET=latin2 | 
+-------+---------------------------------------------------------------------------------------+ 
1 row in set (0.00 sec) 
 
mysql> set sql_mode='ansi'; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> show create table t; 
+-------+--------------------------------------------------+ 
| Table | Create Table                                     | 
+-------+--------------------------------------------------+ 
| t     | CREATE TABLE "t" ( 
  "s1" int(11) default NULL 
) | 
+-------+--------------------------------------------------+ 
1 row in set (0.00 sec)

[1 Apr 2004 5:57] Sergei Golubchik

not a bug.
These two clauses are not printed in ansi mode because they are not part of ANSI SQL.

Whether it should behave this way could be discussed, of course, but in any case it's not a bug (not a programming mistake) but a design decision.