mysql -V: mysql Ver 11.15 Distrib 3.23.41, for redhat-linux-gnu (i386) (Upgrading may not be an option.)
Not subscribed to the list, so please CC me on any responses. I'd submit this to bugs@, but my MySQL version is way out of date and I'm not sure if the current version has been fixed. A quick search on the bugs@ archives didn't net me anything. I'm hoping this general discussion list can shed some light. Please read the whole message first, because some strange things happened when I wrote test code. Example SQL queries are provided at the bottom of this message. I'm using the nifty multi-column-primary-key auto_increment feature of MySQL as described at http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html In one table I have: |Field |Type |Null|Key|Default|Extra | |id |bigint(20)| |PRI|1 | | |revision|bigint(20)| |PRI|NULL |auto_increment | (and other columns, snipped) And it works fine. Eg: insert 1,NULL : 1 1 insert 1,NULL : 1 2 insert 2,NULL : 2 1 insert 2,NULL : 2 2 insert 5,NULL : 5 1 In another table I have: |Field |Type |Null|Key|Default|Extra | |news_id |bigint(20)| |PRI|0 | | |news_revision|bigint(20)| |PRI|0 | | |id |bigint(20)| |PRI|NULL |auto_increment| (and other columns, snipped) And the "id" field doesn't behave as expected: insert 1,1,NULL : 1 1 1 insert 1,1,NULL : 1 1 2 insert 1,2,NULL : 1 2 3 *BAD: should be 1 2 1 insert 2,1,NULL : 2 1 4 *BAD: should be 2 1 1 insert 2,2,NULL : 2 2 5 *BAD: should be 2 2 1 As you can see, the id field is behaving as an ordinary single-primary-key autoincrement. Now, when I went to write test code for you all, even stranger things happened. The part where I expected a failure didn't fail. I had to actually dump my existing table to get code that would fail. Here it is: #this works, as expected # create temporary table double_key (id bigint not null default 1, revision bigint not null auto_increment, primary key (id,revision)); insert into double_key values(1,NULL); insert into double_key values(1,NULL); insert into double_key values(2,NULL); insert into double_key values(2,NULL); insert into double_key values(5,NULL); #this ...WORKS!? It should fail! # create temporary table triple_key (news_id bigint not null default 0, news_revision bigint not null default 0, id bigint not null auto_increment, primary key (news_id,news_revision,id)); insert into triple_key values(1,1,NULL); insert into triple_key values(1,1,NULL); insert into triple_key values(1,2,NULL); insert into triple_key values(2,1,NULL); insert into triple_key values(2,2,NULL); #This is actually from my DB, more or less. #This fails, as expected. #I didn't want to cram all the columns and data down #your throats, but seem to be required for it to fail. #it's pretty much identical to the triple_key above, except #with extra columns. # CREATE temporary TABLE news_images ( news_id bigint not null default 0, news_revision bigint not null default 0, id bigint not null auto_increment, image_URL varchar(255) NOT NULL default '', caption varchar(255) default NULL, image_alt varchar(50) default NULL, creator bigint(20) default NULL, original varchar(255) default NULL, PRIMARY KEY (id,news_revision,news_id) ) TYPE=MyISAM; # INSERT INTO news_images VALUES (4,4,NULL,'','',NULL,NULL,NULL),(4,4,NULL,'',NULL,NULL,NULL,NULL),(5,1,NULL,'','','',1,''),(5,1,NULL,'','','',1,''),(5,1,NULL,'','','',1,''),(5,1,NULL,'','','',1,''),(13,1,NULL,'','','',1,''); Somehow the extra columns are confusing MySQL into misbehaving. Do you know if and when (which version) this bug was fixed? (Should I post this to the bugs mailing list?) Do you know any reasonable workarounds? Thanks in advance, everyone. -The Mindflayer -- _______________________________________________ http://www.operamail.com Now with OperaMail Premium for only US$29.99/yr Powered by Outblaze --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
