Issues converting MyISAM table to InnoDB (auto column issue) I'm having issues trying to convert a table from MyISAM to InnoDB in MySQL 5.6.

The following is the table dump:

--
-- Table structure for table `companies`
--

DROP TABLE IF EXISTS `companies`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `companies` (
  `uid` int(20) NOT NULL,
  `cid` int(20) NOT NULL AUTO_INCREMENT,
  `cname` varchar(500) NOT NULL,
  `rfc` varchar(20) NOT NULL,
  `address` varchar(1000) NOT NULL,
  `dbUseExternal` tinyint(1) NOT NULL DEFAULT '0',
  `dbHost` varchar(50) NOT NULL,
  `dbPort` varchar(50) NOT NULL,
  `dbUser` varchar(50) NOT NULL,
  `dbPass` varchar(50) NOT NULL,
  `dbSSL` varchar(50) NOT NULL,
  `dbDriver` varchar(50) NOT NULL,
  `dbName` varchar(50) NOT NULL,
  `status` int(10) NOT NULL,
  PRIMARY KEY (`uid`,`cid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;


It works as MyISAM. But, if I try to convert it to InnoDB (or if I try editing this dump to insert it on the command line as a sql file), I get the following error:

Incorrect table definition; there can be only one auto column and it must be defined as a key

I understand that error - or at least I thought I did. I'm not really using more than one AUTO_INCREMENT column and it is defined as primary key.

Also, the information I've found regarding the error is always because of an obvious missing key or a duplicate AUTO_INCREMENT definition. One more thing I see generally commented is that the same is true for MyISAM and InnoDB.

So, why does it work for MyISAM and not for InnoDB?

Thanks in advance for any comments.

Francisco
At present, only MyISAM permits auto_increment values in a composite key. I wrote about this over a year ago : How can you have two auto-incremental columns in one table?

According to the Book



Section 5.7 Page 89 Paragraph 1 states the following:


  The MyISAM Storage Engine supports composite indexes that include an AUTO_INCREMENT column. This allows the creation of independent sequences.


which I know you are well aware of. I simply stated this for the benefit of other readers.

As I stated in my past post


  This is not possible with InnoDB based on auto_increment columns being tied directly to the gen_clust_index (aka Clustered Index) !!!


Allowing this would require redesigning the gen_clust_index to accommodate such a mechanism.