MySQL Bugs: #45987: ERROR 1075 (42000): Incorrect table definition

Bug #45987	ERROR 1075 (42000): Incorrect table definition
Submitted:	7 Jul 2009 4:15	Modified:	7 Aug 2009 1:11
Reporter:	Ender Li	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.36-community	OS:	Windows (XP Pro)
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	1075, 42000, auto_increment, error, reference

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...