Bug #25956 Create Table with Multiple keys are not created
Submitted: 30 Jan 2007 21:57 Modified: 5 Feb 2007 14:30
Reporter: Jim Risc Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.0.27-community-nt OS:Windows (Win 2000 SP 4)
Assigned to: Sveta Smirnova CPU Architecture:Any
Tags: create, Multiple keys, table

[30 Jan 2007 21:57] Jim Risc
If you try to create a new InnoDB-table with multiple keys at once, only the primary key and the first of the other keys are created.

Although the Command "SHOW CREATE tablename" shows the correct creation-Syntax, 
the command "SHOW columns from tablename" shows the real table: one primary key and only the first of the second keys.

SHOW columns is correct, cause the table is real slow. After adding the other keys manually, everything is ok.

The engine MyIsam has not this problem.

How to repeat:

SHOW CREATE oprechnung  ->

CREATE TABLE `oprechnung` (`id` int(11) NOT NULL auto_increment, `kontotyp` smallint(6) default '0',`kontoid` int(11) default '0',  PRIMARY KEY  (`id`),  KEY `kontotyp' (`kontotyp`),  KEY `kontoid` (`kontoid`)) ENGINE=InnoDB DEFAULT CHARSET=latin1

SHOW columns from oprechnung  -> kontotyp has "MUL", kontoid has NOT "MUL"
[31 Jan 2007 16:32] Heikki Tuuri

Are you sure that you are interpreting the MUL etc. correctly? I recall DESCRIBE TABLE has a somewhat unintuitive output.

Why do you think that SHOW CREATE TABLE does not show the correct indexes?


[5 Feb 2007 14:30] Jim Risc
Sorry, I found the error:

in the following example I used
create table abc (id int AUTO_INCREMENT, row1 int,row2 int,row3 int, PRIMARY KEY (ID), index(row1,row2,row3)) ENGINE=InnoDB

And only row1 got "MUL", cause of the summurized index.

But the following is correct:
create table abc (id int AUTO_INCREMENT, row1 int,row2 int,row3 int, PRIMARY KEY (ID), index(row1), index(row2), index(row3))  ENGINE=InnoDB

Thank you for your help!