List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:December 11 1999 9:16pm
Subject:Re: Auto_Increment rollover
View as plain text  
At 9:38 AM -0800 1999-12-11, J C Lawrence wrote:
>On Sat, 11 Dec 1999 11:29:50 -0600
>Paul DuBois <paul@stripped> wrote:
>
>>  At 9:16 AM -0800 1999-12-11, J C Lawrence wrote:
>>>  Given an auto_increment column, can I trust it to do the right
>>>  thing and roll back to 0 when the max value of the typ is
>>>  exceeded?  What happens if 0 is occupied?  Does it then find the
>>>  next available free number?
>
>>  No.  You should use a type that is large enough to hold the
>>  largest value you expect to have.  If the sequence reaches the
>>  largest value, the next attempt to generate a sequence value
>>  results in an error.
>
>This can be expensive.
>
>I have a long lived DB which potentially has a high rate of insert
>and deletes with the total number of records probably numbering in
>the middle millions.  To validly use an auto-increment as a
>sustainable key, I'd have to regularly regen the auto_increment
>collumn for all umpty million collumns.  Ouch.

I suspect that's not as expensive as the performance hit you'd take
were the database engine to be doing what you seem to suggest:
scan the table for the first hole in the sequence so that it can
reuse an unused record.  You'd have to do that for *every* insert,
and it potentially would need to scan several million records.

Regenerating the sequence under the circumstances you describe need
be done only once every several quadrillion inserts.

>
>>>   Consider the case of an auto_increment unsigned bigint.  What
>>>  happens if the last value assigned was 0xFFFFFFFFFFFFFFFF and you
>>>  add a new row?  What value does it get?  What if there is a row
>>>  with value 0, and value N (say 5) is unassigned?
>
>>  You cannot have a row with value 0 in an auto-increment column.
>>  Assigning 0 is like assigning NULL - MySQL generates a sequence
>>  number and assigns that instead.
>
>Umm, my mistake.  Make that a 1.

If the highest number assigned is the final value in the sequence
allowed by the underlying datatype, the next insert of NULL or 0
into the column results in an error.

-- 
Paul DuBois, paul@stripped
Thread
Auto_Increment rolloverJ C Lawrence11 Dec
  • Re: Auto_Increment rolloverPaul DuBois11 Dec
  • Re: Auto_Increment rolloverJoshua Chamas12 Dec
Re: Auto_Increment rolloverJ C Lawrence11 Dec
  • Re: Auto_Increment rolloverPaul DuBois12 Dec
Re: Auto_Increment rolloverJ C Lawrence12 Dec