[ale] Auto increment in MySQL

[James P. Kinney III]

On Wed, 2005-09-14 at 22:34 -0400, Christopher Fowler wrote:
> On Wed, 2005-09-14 at 21:34 -0400, George Carless wrote:
> > ALTER TABLE theTableInQuestion AUTO_INCREMENT=1234
> > 
> 
> Here is a question.  Lets say that I have a bunch of holes because I
> deleted various rows.  can I set the value to 1 and MySQL will be smart
> enough to fill those holes?

No. The next available use will be 2, then 3 etc. You will wind up with
duplicates.

If the auto-increment column is the primary index, you will see at the
least errors and at the worst a crash (most likely during a routine
reindex run after a series of inserts).

If you are flat out of numbers, you will need to either raise the
MAX_SEQUENCE (I don't think that's the correct variable name and I'm not
sure it's settable) or script a renumbering of the tables after a vacuum
if the largest sequence number has already been hit. From Mysql pages:


> When an AUTO_INCREMENT column runs out of values, InnoDB wraps a BIGINT
>  to -9223372036854775808 and BIGINT UNSIGNED to 1. However, BIGINT
>  values have 64 bits, so do note that if you were to insert one million
>  rows per second, it would still take nearly three hundred thousand
>  years before BIGINT reached its upper bound. With all other integer
>  type columns, a duplicate-key error results. This is similar to how
>  MyISAM works, because it is mostly general MySQL behavior and not
>  about any storage engine in particular.
> 

So the AUTO_INCREMENT process uses a 64bit Integer so running out is not
really a big issue.
> _______________________________________________
> Ale mailing list
> Ale at ale.org
> http://www.ale.org/mailman/listinfo/ale
-- 
James P. Kinney III          \Changing the mobile computing world/
CEO & Director of Engineering \          one Linux user         /
Local Net Solutions,LLC        \           at a time.          /
770-493-8244                    \.___________________________./
http://www.localnetsolutions.com

GPG ID: 829C6CA7 James P. Kinney III (M.S. Physics)
<jkinney at localnetsolutions.com>
Fingerprint = 3C9E 6366 54FC A3FE BA4D 0659 6190 ADC3 829C 6CA7
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part