MySQL Bugs: #30845: InnoDB: misleading error message for key creation

Bug #30845	InnoDB: misleading error message for key creation
Submitted:	5 Sep 2007 22:28	Modified:	1 Oct 2007 10:26
Reporter:	Paul DuBois	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	4.1 and up	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
For InnoDB, if you create a multi-column index that includes
an AUTO_INCREMENT column, you might see an error message.

Example input:

drop table if exists t;
create table t (
  i int unsigned not null auto_increment,
  j int not null,
  index (j, i)
) engine=innodb;

Result:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

This message is misleading. The real problem is that for
InnoDB tables, the AUTO_INCREMENT column in such an
index must be listed first.  Thus, reversing the order
of the indexed columns succeeds:

drop table if exists t;
create table t (
  i int unsigned not null auto_increment,
  j int not null,
  index (i, j)
) engine=innodb;

How to repeat:
See above.

Suggested fix:
The error message implies that there are multiple
AUTO_INCREMENT columns or that it was not
defined as a key, neither of which is true. A better
message would indicate that the AUTO_INCREMENT
column is not listed first in the index.

Thank you for the bug report. Verified as described.

The error message texts are not inside InnoDB. Reclassifying this as a MySQL Server bug.

The error message could be clearer.

This is not a solution, on MyISAM, if you declare the auto_increment after another column, on the key. MyISAM, make separate numbers of auto_increment. Changue the text not fix the problem.

Sorry by my poor english.

Clarification: The original report is specific to InnoDB and applies only to InnoDB.

This is still not fixed:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.54-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists t;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t (
    ->   i int unsigned not null auto_increment,
    ->   j int not null,
    ->   index (j, i)
    -> ) engine=innodb;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> drop table if exists t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t (
    ->   i int unsigned not null auto_increment,
    ->   j int not null,
    ->   index (i, j)
    -> ) engine=innodb;
Query OK, 0 rows affected (0.07 sec)