Bug #7977 in sql_mode=ANSI, show create table ignores auto_increment
Submitted: 18 Jan 2005 2:02 Modified: 6 Oct 2005 1:08
Reporter: Stewart Smith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0-ndb bk (probably others) OS:
Assigned to: Sergei Glukhov CPU Architecture:Any

[18 Jan 2005 2:02] Stewart Smith 
Description:
I noticed this when using sql_mode=ansi to help import a database from postgresql.

The manual states (on http://dev.mysql.com/doc/mysql/en/Server_SQL_mode.html) that ansi mode is:
Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ONLY_FULL_GROUP_BY. See section 1.5.3 Running MySQL in ANSI Mode.

but no mention of NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS - the ones that you would expect to mean that something was ignored with "show create table".

How to repeat:
mysql> create table pants (id integer auto_increment primary key);
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show create table pants;
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                       |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| pants | CREATE TABLE `pants` (
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set sql_mode=ansi;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table pants;
+-------+-------------------------------------------------------------------------+
| Table | Create Table   |
+-------+-------------------------------------------------------------------------+
| pants | CREATE TABLE "pants" (
  "id" int(11) NOT NULL,
  PRIMARY KEY  ("id")
) |
+-------+-------------------------------------------------------------------------+
1 row in set (0.01 sec)

Suggested fix:
auto_increment be shown in the "show create table" output or the documentation be updated with actual behaviour.
[29 Mar 2005 11:12] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/23432
[30 Jun 2005 9:19] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26549
[22 Sep 2005 11:55] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30202
[23 Sep 2005 9:31] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30256
[29 Sep 2005 11:17] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30503
[29 Sep 2005 11:47] Sergei Glukhov 
Fixed in 4.1.15
[6 Oct 2005 1:08] Paul DuBois 
Noted in 4.1.15 changelog.