Bug #39605 index_type dumped by show create table not at the right position
Submitted: 23 Sep 2008 16:35 Modified: 24 Sep 2008 8:33
Reporter: jocelyn fournier (Silver Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1.28-rc OS:Any
Assigned to: CPU Architecture:Any
Tags: qc

[23 Sep 2008 16:35] jocelyn fournier 
Description:
Hi,

With MySQL 5.1, when using explicitly an index_type during CREATE TABLE, SHOW CREATE TABLE places the [index_type] after index_col_name list, whereas MySQL 5.0 places it before.

e.g : 

In 5.1 :

| {INDEX|KEY} [index_name] (index_col_name,...) [index_type]
      [index_option]

In 5.0 :

| {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
      [index_option]

According to the 5.1 doc, it should be 

| {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
      [index_option]

for both 5.1 and 5.0.

The new format is incompatible with 5.0.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a int, KEY a USING BTREE (a));

SHOW CREATE TABLE t1;

In 5.1 :

+-------+--------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  KEY `a` (`a`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------+

In 5.0 : 

+-------+--------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` int(11) default NULL,
  KEY `a` USING BTREE (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------+

Regards,
  Jocelyn Fournier
[23 Sep 2008 18:49] Sveta Smirnova 
Thank you for the report.

Verified as described.

But in my opinion this can be documentation problem which doesn't reflect new syntax.

Also MySQL 5.0, at least in version 5.0.67 supports 5.1 syntax.

Workaround for earlier versions: load table into 5.0.67, then dump.
[24 Sep 2008 8:33] jocelyn fournier 
Hi,

You're right 5.0.62 seems to work as well. 
The version which was not working on my side is the 5.0.44.

Regards,
  Jocelyn