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

[29 Jun 2005 10:20] Josh Chamas
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
[7 Jul 2005 12:44] 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/26761
[7 Jul 2005 12:59] 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/26762
[7 Jul 2005 13:49] 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/26767
[8 Jul 2005 9:15] Magnus Blåudd
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
[14 Jul 2005 18:49] Paul Dubois
Noted in 4.1.13 changelog.