auto_increment not resettable

--/04w6evG8XlLl3ft
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

in MySQL 4.0.16 (linux x86 rpms), auto_increment doesn't seem to be
resettable, neither using "alter table auto_increment" nor by inserting
an earlier key.

create table x (id int auto_increment, primary key (id));
insert into x values ();
insert into x values ();
insert into x values ();
delete from x where id in (1,2);
alter table x auto_increment = 1; #this doesn't work
insert into x (id) values (1); #and this doesn't work either
insert into x values();
select * from x;

this yields
1
3
4
instead of
1
2
3

thanks!

--/04w6evG8XlLl3ft
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename=script

create table x (id int auto_increment, primary key (id));
insert into x values ();
insert into x values ();
insert into x values ();
delete from x where id in (1,2);
alter table x auto_increment = 1; #this doesn't work
insert into x (id) values (1); #and this doesn't work either
insert into x values();
select * from x;


--/04w6evG8XlLl3ft
Content-Type: text/plain; charset=us-ascii

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org
--/04w6evG8XlLl3ft--

Re: auto_increment not resettable

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello,

josh@yucs.org wrote:
> in MySQL 4.0.16 (linux x86 rpms), auto_increment doesn't seem to be
> resettable, neither using "alter table auto_increment" nor by inserting
> an earlier key.
>
> create table x (id int auto_increment, primary key (id));
> insert into x values ();
> insert into x values ();
> insert into x values ();
> delete from x where id in (1,2);
> alter table x auto_increment = 1; #this doesn't work
> insert into x (id) values (1); #and this doesn't work either
> insert into x values();
> select * from x;
>
> this yields
> 1
> 3
> 4
> instead of
> 1
> 2
> 3
>
> thanks!

This is intended behavior!

It is impossible to reset auto_increment to values lower than existing max value
in column.

With your test case:

create table x (id int auto_increment, primary key (id));
insert into x values ();
insert into x values ();
insert into x values ();
delete from x where id in (1,2);

select * from x;
+----+
| id |
+----+
| 3 |
+----+

At this point there is existing value 3 thus allowed minimum for
auto_increment is 4 !

The reason of auto_increment is to provide mechanism which ensures that newly
inserted value will be always bigger than maximum values which *existed* in
table before.

This is in order to ensure auto_increment values are never reused as they are
usually used as foreign key values referencing to other tables. Using such
values breaks dependencies.

At the other hand inserting non-existing auto_increment values is possible if
specified explicitly as you did in your example:


insert into x (id) values (1);

select * from x;
+----+
| id |
+----+
| 1 |
| 3 |
+----+

As long as you don't break PRIMARY KEY constraints it is Ok to INSERT or UPDATE
auto_increment column.

This way consistent behaviour is provided.

Best regards

- --
Want to swim with the dolphins? (April 14-16, 2004)
http://www.mysql.com/uc2004/

For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE//uQNeehWBZ4HcYkRAqVGAJ4yjSOg8M6vbd2MRv0ZQUMJKXX8HgCe LmEI
YURzqYdXBph7Yd+C2e9OnbM=
=k5El
-----END PGP SIGNATURE-----


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: auto_increment not resettable

didn't it work differently in previous versions?

it's a sensible feature to allow you to "fill in the gaps" in the
sequence. our real case involves some keys significantly greater than
all of the other keys (those few keys start at 10000000).

we used to be able to easily import records from a different installation of
our db application. By simply configuring the other installation to start
auto_incrementing in a different range, we effectively got a whole new
key space, without using the LAST_INSERT_ID(expr) or multi-column
primary key hacks.

On Fri, Jan 09, 2004 at 07:25:34PM +0200, Alexander Keremidarski wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello,
>
> josh@yucs.org wrote:
> > in MySQL 4.0.16 (linux x86 rpms), auto_increment doesn't seem to be
> > resettable, neither using "alter table auto_increment" nor by inserting
> > an earlier key.
> >
> > create table x (id int auto_increment, primary key (id));
> > insert into x values ();
> > insert into x values ();
> > insert into x values ();
> > delete from x where id in (1,2);
> > alter table x auto_increment = 1; #this doesn't work
> > insert into x (id) values (1); #and this doesn't work either
> > insert into x values();
> > select * from x;
> >
> > this yields
> > 1
> > 3
> > 4
> > instead of
> > 1
> > 2
> > 3
> >
> > thanks!
>
> This is intended behavior!
>
> It is impossible to reset auto_increment to values lower than existing max value
> in column.
>
> With your test case:
>
> create table x (id int auto_increment, primary key (id));
> insert into x values ();
> insert into x values ();
> insert into x values ();
> delete from x where id in (1,2);
>
> select * from x;
> +----+
> | id |
> +----+
> | 3 |
> +----+
>
> At this point there is existing value 3 thus allowed minimum for
> auto_increment is 4 !
>
> The reason of auto_increment is to provide mechanism which ensures that newly
> inserted value will be always bigger than maximum values which *existed* in
> table before.
>
> This is in order to ensure auto_increment values are never reused as they are
> usually used as foreign key values referencing to other tables. Using such
> values breaks dependencies.
>
> At the other hand inserting non-existing auto_increment values is possible if
> specified explicitly as you did in your example:
>
>
> insert into x (id) values (1);
>
> select * from x;
> +----+
> | id |
> +----+
> | 1 |
> | 3 |
> +----+
>
> As long as you don't break PRIMARY KEY constraints it is Ok to INSERT or UPDATE
> auto_increment column.
>
> This way consistent behaviour is provided.
>
> Best regards
>
> - --
> Want to swim with the dolphins? (April 14-16, 2004)
> http://www.mysql.com/uc2004/
>
> For technical support contracts, visit https://order.mysql.com/?ref=msal
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
> <___/ www.mysql.com
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.1 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iD8DBQE//uQNeehWBZ4HcYkRAqVGAJ4yjSOg8M6vbd2MRv0ZQUMJKXX8HgCe LmEI
> YURzqYdXBph7Yd+C2e9OnbM=
> =k5El
> -----END PGP SIGNATURE-----
>

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: auto_increment not resettable

At 12:53 -0500 1/9/04, josh@yucs.org wrote:
>didn't it work differently in previous versions?

No. It has never been possible to reset the sequence counter lower
than the maximum value currently present in the table. It is possible
to reuse values in gaps that occur below the maximum, but you can do
so only by inserting those values explicitly, not by generating auto-increment
values automatically.


>
>it's a sensible feature to allow you to "fill in the gaps" in the
>sequence. our real case involves some keys significantly greater than
>all of the other keys (those few keys start at 10000000).
>
>we used to be able to easily import records from a different installation of
>our db application. By simply configuring the other installation to start
>auto_incrementing in a different range, we effectively got a whole new
>key space, without using the LAST_INSERT_ID(expr) or multi-column
>primary key hacks.
>
>On Fri, Jan 09, 2004 at 07:25:34PM +0200, Alexander Keremidarski wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> Hello,
>>
>> josh@yucs.org wrote:
>> > in MySQL 4.0.16 (linux x86 rpms), auto_increment doesn't seem to be
>> > resettable, neither using "alter table auto_increment" nor by inserting
>> > an earlier key.
>> >
>> > create table x (id int auto_increment, primary key (id));
>> > insert into x values ();
>> > insert into x values ();
>> > insert into x values ();
>> > delete from x where id in (1,2);
>> > alter table x auto_increment = 1; #this doesn't work
>> > insert into x (id) values (1); #and this doesn't work either
>> > insert into x values();
>> > select * from x;
>> >
>> > this yields
>> > 1
>> > 3
>> > 4
>> > instead of
>> > 1
>> > 2
>> > 3
>> >
>> > thanks!
>>
>> This is intended behavior!
>>
>> It is impossible to reset auto_increment to values lower than
>>existing max value
>> in column.
>>
>> With your test case:
>>
>> create table x (id int auto_increment, primary key (id));
>> insert into x values ();
>> insert into x values ();
>> insert into x values ();
>> delete from x where id in (1,2);
>>
>> select * from x;
>> +----+
>> | id |
>> +----+
>> | 3 |
>> +----+
>>
>> At this point there is existing value 3 thus allowed minimum for
>> auto_increment is 4 !
>>
>> The reason of auto_increment is to provide mechanism which ensures
>>that newly
>> inserted value will be always bigger than maximum values which *existed* in
>> table before.
>>
>> This is in order to ensure auto_increment values are never reused
>>as they are
>> usually used as foreign key values referencing to other tables. Using such
>> values breaks dependencies.
>>
>> At the other hand inserting non-existing auto_increment values is
>>possible if
>> specified explicitly as you did in your example:
>>
>>
>> insert into x (id) values (1);
>>
>> select * from x;
>> +----+
>> | id |
>> +----+
>> | 1 |
>> | 3 |
>> +----+
>>
>> As long as you don't break PRIMARY KEY constraints it is Ok to
>>INSERT or UPDATE
>> auto_increment column.
>>
>> This way consistent behaviour is provided.
>>
>> Best regards
>>
>> - --
>> Want to swim with the dolphins? (April 14-16, 2004)
>> http://www.mysql.com/uc2004/
>>
>> For technical support contracts, visit https://order.mysql.com/?ref=msal
>> __ ___ ___ ____ __
>> / |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
>> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
>> /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
>> <___/ www.mysql.com
>>
>>
>> -----BEGIN PGP SIGNATURE-----
>> Version: GnuPG v1.2.1 (GNU/Linux)
>> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>>
>> iD8DBQE//uQNeehWBZ4HcYkRAqVGAJ4yjSOg8M6vbd2MRv0ZQUMJKXX8HgCe LmEI
>> YURzqYdXBph7Yd+C2e9OnbM=
>> =k5El
>> -----END PGP SIGNATURE-----
>>
>
>--
>MySQL Bugs Mailing List
>For list archives: http://lists.mysql.com/bugs
>To unsubscribe: http://lists.mysql.com/bugs?unsub=paul@mysql.com


--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org