Description:
MySQL accepts CREATE TABLE with PRIMARY KEY definition for MyISAM tables that is not considered as a valid PRIMARY KEY anymore.
Issue applies to MyISAM table with auto_increment column specified as a second column for primary key.
CREATE TABLE test (
col1 VARCHAr(20), col2 INT(5) AUTO_INCREMENT,
PRIMARY KEY (col1,col2)
) ENGINE=MyISAM;
mysql> ALTER TABLE test ENGINE=InnoDB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
DROPping primary key is also not possible.
mysql> ALTER TABLE test DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
The only way to fix issue is to drop primary key and redefine it at the same step:
mysql> ALTER TABLE test DROP PRIMARY KEY, ADD PRIMARY KEY (col2, col1);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
Creating table with that type of incorrect PK definition is not possible for InnoDB and HEAP storage engines.
How to repeat:
mysql> CREATE TABLE test (
col1 VARCHAr(20), col2 INT(5) AUTO_INCREMENT,
PRIMARY KEY (col1,col2)
) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)
mysql> ALTER TABLE test ENGINE=InnoDB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> ALTER TABLE test DROP PRIMARY KEY, ADD PRIMARY KEY (col2,col1);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE test ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
Suggested fix:
better checking table definition as it is done for InnoDB and HEAP storage engines.
