Bug #7233 SHOW CREATE TABLE uses non-preferred syntax
Submitted: 13 Dec 2004 13:48 Modified: 9 Jan 2005 1:33
Reporter: Carsten Pedersen Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.7-standard-log OS:Linux (SuSE Linux 9.1)
Assigned to: Jim Winstead

[13 Dec 2004 13:48] Carsten Pedersen
The manual states that (http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html)

   TYPE type_name can be used as a synonym for USING type_name to specify an index type.

However, SHOW CREATE TABLE uses TYPE type_name

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

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`)
1 row in set (0.00 sec)

Suggested fix:
Replace "TYPE" with "USING"
[13 Dec 2004 13:50] Carsten Pedersen
The following part got lost in the cut'n'paste from the manual:

   However, USING is the preferred form.

(which is the key sentence for this)
[9 Jan 2005 0:30] Jim Winstead
This was pushed to the 5.0 tree. It was decided that changing the output of SHOW CREATE TABLE 
this late in the 4.1 cycle is too likely to cause problems for tools vendors.
[9 Jan 2005 1:33] Paul Dubois
Noted in 5.0.3 changelog.