MySQL Bugs: #25956: Create Table with Multiple keys are not created

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:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.27-community-nt	OS:	Microsoft Windows (Win 2000 SP 4)
Assigned to:	Sveta Smirnova	CPU Architecture:	Any
Tags:	create, Multiple keys, table

Description:
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"

Hi!

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?

Regards,

Heikki

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!