On Tuesday 11 February 2003 19:53, The Mindflayer wrote: > 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!? 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);
In this table id is the third column of the primary key, so autoincrement value is generated per given prefix. > #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; > # In the table news_images id is the first column of the primary key, so it works as usual auto_increment column :) > > 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? Change columns order in the primary key. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com --------------------------------------------------------------------- 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
