List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:February 13 2003 5:33pm
Subject:re: 3.23.41: auto_increment bug with TRIPLE primary key? (really
strange)
View as plain text  
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 |
+---------+---------------+----+
Thread
3.23.41: auto_increment bug with TRIPLE primary key? (reallystrange)The Mindflayer11 Feb
  • re: 3.23.41: auto_increment bug with TRIPLE primary key? (really strange)Victoria Reznichenko13 Feb
    • re: 3.23.41: auto_increment bug with TRIPLE primary key? (reallystrange)Paul DuBois13 Feb