Bug #17751 create table with AUTO_INCREMENT produces error

• Submitted: 27 Feb 2006 19:22
• Modified: 2 Mar 2006 4:01
• Reporter: thomas rodrian
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.18-nt
• OS: Windows (XP)
• CPU Architecture: Any

[27 Feb 2006 19:22] thomas rodrian

Description:
The following create table state found in documentation section 3.6.9 produces an error:
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) at line 7: Incorrect table definition: there can be pnly one auto column and it must be defined as a key

How to repeat:
Create database menagerie;

use menagerie

select version();

drop table if exists animals;

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;

Suggested fix:
Since the following syntax works, I think the error is being generated because of two columns specified in the primary key above.
CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
 );

Multi-column primary keys are legal, but mySQL appears to not like having one of the columns being an auto_increment column.

[27 Feb 2006 19:29] Jorge del Conde

Hi!

I was unable to reproduce this bug under XP/AMD and XP64/AMD64 with 5.0.18:

mysql> use menagerie
Database changed
mysql>
mysql> select version();
+------------+
| version()  |
+------------+
| 5.0.18-log |
+------------+
1 row in set (0.00 sec)

mysql>
mysql> drop table if exists animals;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
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)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> INSERT INTO animals (grp,name) VALUES
    ->     ('mammal','dog'),('mammal','cat'),
    ->     ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ->     ('bird','ostrich');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM animals ORDER BY grp,id;
+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+
6 rows in set (0.00 sec)

mysql>

[2 Mar 2006 4:01] thomas rodrian

Hi Jorge.

You tested with the -log version, i found the bug with the -nt version. Please recheck with the -nt version.

Thanks,
Thomas