Bug #7235 default index type not shown for SHOW CREATE TABLE
Submitted: 13 Dec 2004 13:56 Modified: 20 Jan 2005 18:50
Reporter: Carsten Pedersen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.7-standard-log OS:Linux (SuSE Linux 9.1)
Assigned to: Jim Winstead CPU Architecture:Any

[13 Dec 2004 13:56] Carsten Pedersen
Description:
When using SHOW CREATE TABLE, the index type is not shown if it is the default. 

Apart from being inconsistent, this might create conflicts in future releases if the default index type of a table comes under user/DBA control.

How to repeat:
mysql> drop table if exists t;
Query OK, 0 rows affected (0.00 sec)

# Use the non-default BTREE index type and note that it appears in SHOW CREATE TABLE

mysql> CREATE TABLE t (i int, index using btree (i)) engine=memory;
Query OK, 0 rows affected (0.06 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `i` int(11) default NULL,
  KEY `i` TYPE BTREE (`i`)
) ENGINE=HEAP DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> drop table if exists t;
Query OK, 0 rows affected (0.01 sec)

# Use default index type HASH -- it does not show up in SHOW CREATE TABLE

mysql> CREATE TABLE t (i int, index using hash (i)) engine=memory;
Query OK, 0 rows affected (0.06 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `i` int(11) default NULL,
  KEY `i` (`i`)
) ENGINE=HEAP DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Suggested fix:
Make SHOW CREATE TABLE always indicate the index type, irrespective of whether it's the default.
[8 Jan 2005 5:19] Jim Winstead
The index type will now be displayed for all indexes in tables that support
multiple index types.
[13 Jan 2005 23:17] Jim Winstead
Fixed in 4.1 tree.
[20 Jan 2005 18:50] Paul Dubois
Mentioned in 4.1.10 change notes.