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 |
+---------+---------------+----+
---------------------------------------------------------------------
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
