Issues converting MyISAM table to InnoDB (auto column issue)

Question

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

• ypercubeᵀᴹ: Short story: InnoDB does not allow an auto-incrementing column to be part of a composite (unique or) primary key.
• ypercubeᵀᴹ: Actually it does (if you add another index, for (cid) alone) but the auto-increment won't work as it does in MyISAM.
• ypercubeᵀᴹ: Same problem: MySQL - convert MyISAM into InnoDB getting error 1075

Answer 1

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

picture

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.

• ypercubeᵀᴹ: I agree that this is not possible in InnoDB but I seriously doubt it is related to the clustered index. It's probably related to isolation levels and their implementation.
• RolandoMySQLDBA: @ypercube I think you are saying it better than I expressed it. I was thinking of mechanisms, not so much the Clustered Index in itself.