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.