MySQL Bugs: #6126: innodb index with duplicate column gives missleading error message

Bug #6126	innodb index with duplicate column gives missleading error message
Submitted:	16 Oct 2004 9:19	Modified:	22 Nov 2004 18:10
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	4.1.5	OS:
Assigned to:	Antony Curtis	CPU Architecture:	Any

Description:
> create table i (i int, primary key(i,i)) type=innodb;

ERROR 1005 (HY000): Can't create table './test/i.frm' (errno: 126)

$ perror 126

MySQL error:  126 = Index file is crashed

With the example above the cause of the error is more or less
obvious (although it works with type=myisam) and it is easy
to identify and remove the duplicate key column.

But given a longer column list with longer column names it is
easy to overlook the duplicate column and the error message
does not help to identify the problem at all, especially as the
InnoDB tablespace seems to be perfectly OK

How to repeat:
create table i (i int, primary key(i,i)) type=innodb;

Suggested fix:
either allow duplicate columns in InnoDB indexes 
or give a more meaningfull error message

merged from bug 6130 reported by georg@mysql.com:

By an accident I typed  PRIMARY KEY (a,a) instead of (a,b) on a MyISAM table -
there was no error  so it took a while until I recognized the problem. 
I don't know if I should call this a bug, but to allow this is complete nonsens!
:) 
(InnoDB complains about it, but not MyISAM)

Thinking of it again i agree with georg, any atempt to create an index with duplicate columns should lead to a (meaningfull) error message on all table handlers.

A change like this might lead to some backwards compatibility issues with existing applications but these would fail anyway if they ever switched from MyISAM to InnoDB
and it would be easy to fix for application maintainers if the error message read something like:

  Error: Duplicate column 'foo' in index 'bar'

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html