MySQL Bugs: #11635: mysqldump exports TYPE instead of USING for PRIMARY KEY indexes

Bug #11635	mysqldump exports TYPE instead of USING for PRIMARY KEY indexes
Submitted:	29 Jun 2005 10:20	Modified:	14 Jul 2005 18:49
Reporter:	Josh Chamas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.12	OS:	Linux (Linux 2.6.4, SUSE)
Assigned to:	Magnus Blåudd	CPU Architecture:	Any

Description:
For a cluster table definition like:

create table t1 (
  c1 int NOT NULL,
  c2 int NOT NULL,
  PRIMARY KEY USING HASH (c1),
  INDEX USING BTREE(c2)
);

The mysqldump version of this will look like:

create table t1 (
  c1 int NOT NULL,
  c2 int NOT NULL,
  PRIMARY KEY TYPE HASH (c1),
  INDEX TYPE BTREE(c2)
);

and the "TYPE" keyword will fail when reloading the mysqldump generated backup SQL file.

How to repeat:
Create the above table, run mysqldump on it, and try to reimport the SQL dump file.

Suggested fix:
TYPE should not be output in the mysqldump

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/26761

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/26762

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/26767

The problem occured becuase when using the TYPE keyword ti indicate the index_type it must be prefixed with name of the index, and that is not output for the primary key.
Ex:
create table t1 (
  c1 int NOT NULL,
  c2 int NOT NULL,
  PRIMARY KEY TYPE HASH (c1)
                             ^^ index_name missing
);

The output from SHOW CREATE TABLE was changed to always use the USING keyword where the index_name is optional. This change was already incorporated into 5.0.

Pushed to 4.1.13

Noted in 4.1.13 changelog.