MySQL Bugs: #2138: difference InnoDB / MyISAM in create index

Bug #2138	difference InnoDB / MyISAM in create index
Submitted:	17 Dec 2003 3:08	Modified:	17 Dec 2003 8:10
Reporter:	Werner Stuerenburg	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	4.0.15, 4.1.1	OS:	Windows (Win2K)
Assigned to:		CPU Architecture:	Any

Description:
bug 2137 lead by accident to an interesting "bug/feature", when converting to InnoDB; the original primary key was defined on 3 columns, 2 of which were identical - I don't know if this makes sense, may pretty well have been a mistake. MyISAM has no problems with that, InnoDB reports an error.

How to repeat:
DROP TABLE IF EXISTS `test_y`;
CREATE TABLE `test_y` (
 `id` smallint(5) unsigned NOT NULL auto_increment,
 `val` varchar(5) NOT NULL default '',
 PRIMARY KEY  (`id`,`val`),
 UNIQUE KEY `val` (`val`)
) TYPE=InnoDB DEFAULT CHARSET=latin1 COMMENT='test for dü-dy';

ALTER TABLE `test_y` ADD INDEX `test3` ( `id` , `val` , `id` );

# 1005 - Can't create table '.\world\#sql-4bc_63.frm' (errno: 126)
# D:\mysql\4.0.15\bin>perror 126
# Error code 126:  Unknown error
# 126 = Index file is crashed

ALTER TABLE `test_y` TYPE = MYISAM; 

ALTER TABLE `test_y` ADD INDEX `test3` ( `id` , `val` , `id` );

# fine...

Verified in 4.0.17 (and changing category).  This seems an incredibly unlikely thing for someone to want to do.  :)  May just need to note it in the InnODB Restrictions chapter.

Werner,

InnoDB really does not allow creation of an index where the same column appears twice. This is only documented in a changelog note:

"
MySQL/InnoDB-3.23.54, December 12, 2002
...
Fixed a bug: if an index contains some column twice, and that column is updated, the table will become corrupt. From now on InnoDB prevents creation of such indexes. 
"

Regards,

Heikki

Well, yes, I don't think that the bug is with InnoDB; it should be impossible for MyISAM as well, I guess. The behaviour should be identical, right?