Re: Changing ids conflicting with serial values?

On Fri, 2005-11-04 at 15:49, Marc Boucher wrote:
> At 11:49 04/11/2005 -0500, Alex Turner wrote:
> > I think he meant
> >
> > create sequence test_seq;
> > select setval('test_seq',(select max(primary_key_id) from my_table));
> >
> > not max value of a serial type.
>
> What I understand, and from what I know by using mysql, is that mysql
> auto-adjust the max value of a serial.
> Something like :
> - a table with 5000 elements with ids from 1-5000.
> - if you update the id in one of the rows and set it to 65000, mysql
> updates the serial current value.
> - even if you delete, or change the id back to its previous value, the
> current value will still be 65000.
> - a new inserted row will have the id 65001.
>
> Now assuming the id's maximum value is 65535, and you set one of the rows to
> this value, mysql will be unable to find a "nextval" greater than 65535. New
> inserts will fail.
> I don't know if it is still the case with recent versions of mysql, but that's
> what I discovered while testing a web application.

Actually, the behaviour is, I believe, dependent on which storage engine
you are using for that table. Reading the page on innodb, it seems that
on db startup a brand new starting point is determined by looking at the
current max in the autoinc field. I found this statement interesting:

QUOTE:

The behavior of the auto-increment mechanism is not defined if a user
assigns a negative value to the column or if the value becomes bigger
than the maximum integer that can be stored in the specified integer
type.

UNQUOTE:

While I can forgive the undefined behaviour for a negative number, the
undefined behaviour at rollover is a bit more bothersome. I'd prefer
it be defined as "we stop inserts until you rectify the situation" than
"undefined".