1

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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Francisco Zarabozo
  • 3,676
  • 2
  • 28
  • 54
  • Why do you want a PK of `(uid,cid)`, rather than just a pure synthetic auto_increment PK of `(cid)` alone? – eggyal Apr 30 '13 at 12:14
  • It is working nicely on MyISAM to auto_increment from 1 for each client. If I add a new `uid`, it automatically gets `cid` = 1, and it increments one by one for that `uid`. I have Users that register Companies. So, each record is `uid`-`cid` unique. – Francisco Zarabozo Apr 30 '13 at 12:26
  • Perhaps [this answer](http://stackoverflow.com/a/11321211) will help? – eggyal Apr 30 '13 at 12:38

1 Answers1

2

in InnoDB, auto_increment keys have to have to: either have their own index, or at least be the primary sub-index of a compound index. (a in KEY(a,b)).

Fix this by adding a KEY(cid) after your PRIMARY KEY, like so:

CREATE TABLE `companies2` (
  `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`),
  KEY(`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Tasos Bitsios
  • 2,699
  • 1
  • 16
  • 22
  • However, you will lose this (from your comment): "It is working nicely on MyISAM to auto_increment from 1 for each client. If I add a new uid, it automatically gets cid = 1, and it increments one by one for that uid. I have Users that register Companies. So, each record is uid-cid unique." – Tasos Bitsios Apr 30 '13 at 12:40
  • I was starting to get excited until I saw your comment. Thanks anyway for the answer, it's a good answer. :-) – Francisco Zarabozo Apr 30 '13 at 13:22
  • If you absolutely have to do it, you can still accomplish it with custom triggers. The answer linked-to in your question's comments by eggyal describes a very similar thing. But it doesn't sound like it is worth the hassle. Up to you :) – Tasos Bitsios Apr 30 '13 at 13:26