MySQL Bugs: #42181: Order of primairy key when using multiple fields hinders creating the table

Bug #42181	Order of primairy key when using multiple fields hinders creating the table
Submitted:	17 Jan 2009 17:29	Modified:	17 Jan 2009 20:22
Reporter:	Koen De Groote	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment, primairy key

Description:
I'm not entirely sure wheter this is a bug or not, but it sure is annoying.

I'm taking this table from the online reference manual:

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
);

Taken from: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
Taken at date: Jan 17th 2009

I've allready sent a request to alter the data on the site, but I'm reporting it here too since I'm not sure wheter it's a bug or not.

When entering that table in the command-line editor, I get this:

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

Now, if I change "PRIMARY KEY (grp,id)" to "PRIMARY KEY (id,grp)", this problem does not occur. In other words, the auto_increment field must be set as first argument or it won't work. Is this necessary for the inner workings of MySQL? Or is it a bug that can be fixed?

How to repeat:
Simply enter the code in the command line client.

Suggested fix:
Making it not necessary for the auto_increment field to be the first argument when using multiple fields in combination as primairy key.

Thank you for the bug report. I assume you have InnoDB as default storage engine, that is so a restriction of this engine and the Manual example assume MyISAM tables:

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.31-nt-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.1 >use test
Database changed

mysql 5.1 >set storage_engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1 >CREATE TABLE animals (
    ->     grp ENUM('fish','mammal','bird') NOT NULL,
    ->     id MEDIUMINT NOT NULL AUTO_INCREMENT,
    ->     name CHAR(30) NOT NULL,
    ->     PRIMARY KEY (grp,id)
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql 5.1 >drop table animals;
Query OK, 0 rows affected (0.02 sec)

mysql 5.1 >set storage_engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1 >CREATE TABLE animals (
    ->     grp ENUM('fish','mammal','bird') NOT NULL,
    ->     id MEDIUMINT NOT NULL AUTO_INCREMENT,
    ->     name CHAR(30) NOT NULL,
    ->     PRIMARY KEY (grp,id)
    -> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql 5.1 >CREATE TABLE animals (
    ->     grp ENUM('fish','mammal','bird') NOT NULL,
    ->     id MEDIUMINT NOT NULL AUTO_INCREMENT,
    ->     name CHAR(30) NOT NULL,
    ->     PRIMARY KEY (id,grp)
    ->     );
Query OK, 0 rows affected (0.19 sec)

mysql 5.1 >

Indeed, my default engine is set to InnoDB.

I've only been studying MySQL for a few months now and haven't gotten to engine-specifics yet.

So in other words, problem solved I think. I'll close the topic. Thanks for the help.