From: Michael Widenius
Date: April 18 1999 12:09pm
Subject: Re: auto_increment problem
List-Archive: http://lists.mysql.com/mysql/2021
Message-Id: <14105.51964.583808.841619@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

>>>>> "Sasha" == Sasha Pachev <sasha@stripped> writes:

Sasha> Michael Widenius wrote:
>> 
>> >>>>> "Lew" == Lew Barnesson <lewb@stripped> writes:
>> 
Lew> Hi,
Lew> I began using auto_increment in an extensive system (46 'C' CGI programs on
Lew> Win NT 4.0 server ) back in the 3.21.x days; back when I didn't know about
Lew> the problem of a delete freeing up a previously used auto_increment-assigned
Lew> number/id. My system assumes uniqueness accross not only the database, but
Lew> archived deleted rows.
>> 
Lew> I understand this problem will be resolved in 3.23.0. Is this correct, and
Lew> when can I expect to see this release? My system is going production very
Lew> shortly, so I need to decide to wait for resolution, or to create my own
Lew> unique id assignments.
>> 
Lew> My complements to Monty and crew for this very fine mysql.
>> 
Lew> Regards to all.
>> 
Lew> -Lew Barnesson
>> 
>> Yes, MySQL 3.23 will solve this problem.  I will also very shortly release an
>> alpha version of this.  The problem is of course when the 3.23 version
>> will be ready for production use. (We will of course test 3.23 very
>> throughly, but its very hard to test the new ISAM as good as the old
>> ISAM has been tested the past few years)
>> 
>> You can also in the MySQL manual find an example how to solve this
>> problem by using the LAST_INSERT_ID() function.
>> 
>> Regards,
>> Monty
>> 

Sasha> What Monty suggests is a cleaner coding practice. All that a defensive
Sasha> programmer would expect from auto_increment is that the id is unique for
Sasha> the table.

Sasha> -- 
Sasha> Sasha Pachev
Sasha> http://www.sashanet.com

Hi!

I was acutally refering to the following manual section:

------

     If `expr' is given as an argument to `LAST_INSERT_ID()' in an
     `UPDATE' clause, then the value of the argument is returned as a
     `LAST_INSERT_ID()' value.  This can be used to simulate sequences:

     First create the table:

          mysql> create table sequence (id int not null);
          mysql> insert into sequence values (0);

     Then the table can be used to generate sequence numbers like this:

          mysql> update sequence set id=LAST_INSERT_ID(id+1);

     You can generate sequences without calling `LAST_INSERT_ID()', but
     the utility of using the function this way is that the ID value is
     maintained in the server as the last automatically-generated
     value.  You can retrieve the new ID as you would read any normal
     `AUTO_INCREMENT' value in *MySQL*.  For example,
     `LAST_INSERT_ID()' (without an argument) will return the new ID.
     The C API function `mysql_insert_id()' can also be used to get the
     value.
-------

Regards,
Monty