Bug #45987 ERROR 1075 (42000): Incorrect table definition

Description:
In http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html,
there is a sample code :

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)
);

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

When I try to "CREATE TABLE animals", I get "ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be def
ined as a key".

How to repeat:
mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| 321321         |
| person         |
| shirt          |
| shop           |
| t1             |
+----------------+
5 rows in set (0.13 sec)

mysql> 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 def
ined as a key
mysql>

Suggested fix:
Maybe Server error, maybe docs error...
Please just fix or modify one of them.

Thank you for the problem report. Actually, this is not a server bug, just a difference between MyISAM (assumed as default by that manual page) and InnoDB storage engine documented elsewhere (http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html). You (and me) have InnoDB as a default storage engine instead. Look:

mysql> 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 colum
n and it must be defined as a key
mysql> 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)
    -> ) engine=MyISAM;
Query OK, 0 rows affected (0.20 sec)

mysql> alter table animals engine=InnoDB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto colum
n and it must be defined as a key

So, this page in documentation should be clarified.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added an explicit ENGINE=MyISAM clause to the CREATE TABLE statement.

Bug: http://bugs.mysql.com/bug.php?id=60104 marked as duplicate of this one.

only twist the position of PRIMARY KEY (grp,id) to PRIMARY KEY (id, grp) and solved problem...