Hi, I've been thinking about converting our database from MyISAM to InnoDB and while I was in the process of testing this out I ran across a kind of "anomaly". In several of our tables, we have an auto_increment as the second field of the primary key. For example:
CREATE TABLE test_myisam ( id1 smallint(5) unsigned NOT NULL default '0', id2 smallint(5) unsigned NOT NULL auto_increment, PRIMARY KEY (id1,id2), ) TYPE=MyISAM;
When I try to create that same table as InnoDB:
CREATE TABLE test_innodb ( id1 smallint(5) unsigned NOT NULL default '0', id2 smallint(5) unsigned NOT NULL auto_increment, PRIMARY KEY (id1,id2), ) TYPE=InnoDB;
It fails with this error:
"Incorrect table definition; There can only be one auto column and
it must be defined as a key"So I tried it another way; create the table without the AUTO_INCREMENT property and add it afterwards in an alter table statement, and then insert a few test values:
CREATE TABLE test_innodb ( id1 smallint(5) unsigned NOT NULL default '0', id2 smallint(5) unsigned NOT NULL, PRIMARY KEY (id1,id2), ) TYPE=InnoDB; ALTER TABLE test_innodb CHANGE id2 id2 SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT; INSERT INTO test_innodb (id1) VALUES (1), (1), (1), (1), (2), (2), (2), (3), (3), (3), (2), (2);
And that worked perfectly. My question is: why did one way work and not the other?
As written in the doc:
"In MyISAM and BDB tables you can specify AUTO_INCREMENT secondary
column in a multiple-column key."Since it doesn't say anything about InnoDB tables, I guess I shouldn't expect that feature to work. But it DOES work. When the test values are inserted, the AUTO_INCREMENT on the second field works exactly like it does in MyISAM tables. The only problem seems to be with the CREATE TABLE syntax. Why is that? If 2nd-field AUTO_INCREMENT is supported, the CREATE TABLE shouldn't have failed. If it isn't supported, then why does it work? Or is that one of those "undocumented features"?
BTW, I'm running 4.1.0-alpha-log
Thanks, Daniel DeLorme
________________________________________________________________________ Daniel "42" DeLorme /| |Ż|/ŻŻŻ\ ICQ:11411269, AIM:DanFortyTwo / |_| | Ż| | encyclopedist @ http://www.animenewsnetwork.com |___ | / /_ webmaster @ http://dan42.com (Jin-Roh, Millennium Actress) |_||____| ŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻŻ
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
