MySQL Bugs: #47653: Error 1075: incorrect table definition

Bug #47653	Error 1075: incorrect table definition
Submitted:	25 Sep 2009 19:44	Modified:	26 Sep 2009 7:39
Reporter:	Ezequiel P. Suarez	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.0.4	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	Error 1075 - Incorrect table definitions

Description:
When create this table I get the following error
Error 1075: incorrect table definition; there can be only one auto column and i must be defined as a key.

CREATE TABLE `routeGPS` (
  `usuario` varchar(45) NOT NULL,
  `latitud` decimal(10,6) NOT NULL,
  `longitud` decimal(10,6) NOT NULL,
  `elevacion` float NOT NULL,
  `time` datetime NOT NULL,
  `comentario` varchar(45) default NULL,
  `ip` varchar(45) default NULL,
  `fecha_ingreso` datetime NOT NULL,
  `tag_name` varchar(100) NOT NULL,
  `archivo_gpx` varchar(45) NOT NULL,
  `idrouteGPS` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`usuario`,`tag_name`,`archivo_gpx`,`idrouteGPS`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED COMMENT='InnoDB free: 13312 kB; InnoDB free: 20480 kB';

But when I create this table I get no error and both are equal in structure and syntax

CREATE TABLE `waypoints` (
  `idway` int(10) unsigned NOT NULL auto_increment,
  `altitud` float NOT NULL,
  `presion` float default NULL,
  `nomwpt` varchar(100) NOT NULL,
  `obs` varchar(255) NOT NULL,
  `fecha` datetime NOT NULL,
  `poslat` decimal(10,6) NOT NULL,
  `poslon` decimal(10,6) NOT NULL,
  `usuario` varchar(45) NOT NULL,
  `archivo_gpx` varchar(100) NOT NULL,
  `fecha_ingreso` datetime NOT NULL,
  PRIMARY KEY  (`idway`,`archivo_gpx`,`fecha_ingreso`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

what is happening?

How to repeat:
CREATE TABLE `routeGPS` (
  `usuario` varchar(45) NOT NULL,
  `latitud` decimal(10,6) NOT NULL,
  `longitud` decimal(10,6) NOT NULL,
  `elevacion` float NOT NULL,
  `time` datetime NOT NULL,
  `comentario` varchar(45) default NULL,
  `ip` varchar(45) default NULL,
  `fecha_ingreso` datetime NOT NULL,
  `tag_name` varchar(100) NOT NULL,
  `archivo_gpx` varchar(45) NOT NULL,
  `idrouteGPS` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`usuario`,`tag_name`,`archivo_gpx`,`idrouteGPS`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED COMMENT='InnoDB free: 13312 kB; InnoDB free: 20480 kB';

CREATE TABLE `waypoints` (
  `idway` int(10) unsigned NOT NULL auto_increment,
  `altitud` float NOT NULL,
  `presion` float default NULL,
  `nomwpt` varchar(100) NOT NULL,
  `obs` varchar(255) NOT NULL,
  `fecha` datetime NOT NULL,
  `poslat` decimal(10,6) NOT NULL,
  `poslon` decimal(10,6) NOT NULL,
  `usuario` varchar(45) NOT NULL,
  `archivo_gpx` varchar(100) NOT NULL,
  `fecha_ingreso` datetime NOT NULL,
  PRIMARY KEY  (`idway`,`archivo_gpx`,`fecha_ingreso`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `routeGPS` (
  `usuario` VARCHAR(45) NOT NULL,
  `latitud` DECIMAL(10,6) NOT NULL,
  `longitud` DECIMAL(10,6) NOT NULL,
  `elevacion` FLOAT NOT NULL,
  `time` DATETIME NOT NULL,
  `comentario` VARCHAR(45) DEFAULT NULL,
  `ip` VARCHAR(45) DEFAULT NULL,
  `fecha_ingreso` DATETIME NOT NULL,
  `tag_name` VARCHAR(100) NOT NULL,
  `archivo_gpx` VARCHAR(45) NOT NULL,
  `idrouteGPS` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY  (`usuario`,`tag_name`,`archivo_gpx`,`idrouteGPS`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;
-- Error Code : 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

-- 1st solution: when defining a multi-column key list the auto_increment column first
CREATE TABLE `routeGPS` (
  `usuario` VARCHAR(45) NOT NULL,
  `latitud` DECIMAL(10,6) NOT NULL,
  `longitud` DECIMAL(10,6) NOT NULL,
  `elevacion` FLOAT NOT NULL,
  `time` DATETIME NOT NULL,
  `comentario` VARCHAR(45) DEFAULT NULL,
  `ip` VARCHAR(45) DEFAULT NULL,
  `fecha_ingreso` DATETIME NOT NULL,
  `tag_name` VARCHAR(100) NOT NULL,
  `archivo_gpx` VARCHAR(45) NOT NULL,
  `idrouteGPS` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY  (`idrouteGPS`,`usuario`,`tag_name`,`archivo_gpx`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;
-- success 

-- 2nd solution: define a plain key on the auto_increment column alone
CREATE TABLE `routegps` (
   `usuario` VARCHAR(45) NOT NULL,
   `latitud` DECIMAL(10,6) NOT NULL,
   `longitud` DECIMAL(10,6) NOT NULL,
   `elevacion` FLOAT NOT NULL,
   `time` DATETIME NOT NULL,
   `comentario` VARCHAR(45) DEFAULT NULL,
   `ip` VARCHAR(45) DEFAULT NULL,
   `fecha_ingreso` DATETIME NOT NULL,
   `tag_name` VARCHAR(100) NOT NULL,
   `archivo_gpx` VARCHAR(45) NOT NULL,
   `idrouteGPS` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`usuario`,`tag_name`,`archivo_gpx`,`idrouteGPS`),
   KEY `NewIndex` (`idrouteGPS`)
 ) ENGINE=INNODB DEFAULT CHARSET=latin1;
-- success

Peter
(not a MySQL person).
.. but the MySQL team will probably direct you to proper page in documentation!

However I think that error message does not very well make it clear that why 1st example fails and 2nd example works.

btw: you hopefully do not mean seriously that you are using MySQL 5.0.4? If so the server should definitely be upgraded!

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

"For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index...". .. {But it will behave a special way then}.

So with no ENGINE you can have it as 3rd and higher column in a multiple-column index and with other Engines than MyISAM and BDB (like InnoDB) it will have to be 1st column in an index.

However this passage does not make it clear why 
..
  `idrouteGPS` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`usuario`,`tag_name`,`archivo_gpx`,`idrouteGPS`),
  KEY `NewIndex` (`idrouteGPS`)
 ) ENGINE=INNODB DEFAULT CHARSET=latin1;

.. actually works, as due to `NewIndex` on (`idrouteGPS`) alone `idrouteGPS` may be specified in any position in another index. However there may be some explanation in some InnoDB specific docs or some docs on how MySQL treats indexes in general.

Sorry, but our manual (http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html) clearly says:

"An AUTO_INCREMENT column must appear as the first column in an index on an InnoDB  table."

So, the behavior you described is expected and documented.