At 18:45 +0200 2/13/03, Victoria Reznichenko wrote:
>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.
That's right. That's why the results he shows above indicate incorrect
behavior. The AUTO_INCREMENT column shouldn't be 1, 2, 3, 4, 5,
because he doesn't have 5 different prefixes.
Anyway, I get the correct behavior in 4.0.9, so the problem seems to have
fixed at least by then.
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);
select * from triple_key;
Yields:
+---------+---------------+----+
| news_id | news_revision | id |
+---------+---------------+----+
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 2 | 1 |
| 2 | 1 | 1 |
| 2 | 2 | 1 |
+---------+---------------+----+
