Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Short story: InnoDB does not allow an auto-incrementing column to be part of a composite (unique or) primary key. –  ypercube Apr 30 '13 at 12:11
    
Actually it does (if you add another index, for (cid) alone) but the auto-increment won't work as it does in MyISAM. –  ypercube Apr 30 '13 at 12:14
    
Same problem: MySQL - convert MyISAM into InnoDB getting error 1075 –  ypercube Apr 30 '13 at 12:37
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
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. –  ypercube Apr 30 '13 at 18:24
    
@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. –  RolandoMySQLDBA Apr 30 '13 at 21:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.