Re: AUTO_INCREMENT bug after inserting negative value

[Manuel Lemos]

Hello Michael,

On 01-May-00 07:23:10, you wrote:

>Manuel> I understand your point, but don't you agree that should be up to the
>Manuel> database programmer to decide which is the most appropriate range of
>values Manuel> for his own application?

>Manuel> Wouldn't it be more consistent if you let the developer choose if he
>wants Manuel> the auto incremented field to have negative values or not by
>honouring the Manuel> presence (or absence) of the UNSIGNED qualifier? That's
>my suggestion for a fix Manuel> to this problem.

>I am usually for more freedom; The problem is that now the
>auto_increment is done at the MyISAM level and this doesn't know
>anything about the sign of the integer in questions.  I wouldn't like
>to have the change the format of the MyISAM tables at this point. :(
>The current behavour is also much easier to explain and should give
>fewer problems (I didn't like the old behavour of what appends when
>the auto_increment 'wrapped' around.  Inserting -1 to get 1 is also
>real ugly!)

That was a typo in my report. It should read 0 where it was written 1.


>Anyway, the main reason to use auto_increment is to get unique numbers
>and one shouldn't normally depend on which number one gets.  We will
>at some point offer new database handlers and on these the
>auto_increment value may be a row_id type value that appears to be
>completely random...

Yes, for my applications I never start at 0 as it has a special meaning.
But I can't speak for others that want to use MySQL with Metabase. MySQL
does not support sequences as many other DBMS. Others let sequences start
at any integer value. If it was not for that bug, MySQL could allow that
too.


>>> In other words, we don't think this is really a bug in MySQL, but
>>> rather that Metabase uses the auto_increment option in a way it was
>>> not designed for.  It's also not a good idea to change MySQL to allow
>>> wrap-around for the last_insert id (from max-value or -1 to 0), as if
>>> we would allow this we would get other much more interesting problems
>>> in normal usage!

>Manuel> But it was working as expected at least MySQL 3.21.22 which is a
>version Manuel> that I have in a production site.  Jason King reported that
>in MySQL Manuel> 3.22.32 it was also working as expected.  I don't know
>exactly when, but in Manuel> a more recent version the expected behaviour was
>broken.

>Yes, it works accidently in 3.22 but doesn't work anymore in MySQL
>3.23 with the new MyISAM tables.  The above behaveour was not
>something we did do by design and wasn't a intended one. Anyway, the
>new AUTO_INCREMENT option in MySQL 3.23 is a much nicer way to get the
>beahavour you want!

How?  It seems to be impossible in 3.23 to make a sequences start at 0 or
less, while it was possible in previous versions.


>>> PS: Sorry for the change of behaveour, but we think that the current
>>> model is better and gives us more possibilities to ensure that
>>> things doesn't get 'out of hands' in normal usage'

>Manuel> Maybe I am not explaining correctly why I am saying that the expected
>Manuel> behaviour was broken, probably because if the 1 by 0 typo confusion
>in my Manuel> report. But to sum up what I recommend:

>Manuel> Honour the presence or absence of the UNSIGNED qualifier in the
>INTeger Manuel> field declaration and make sure that when negative values are
>used in Manuel> signed auto incremented fields, the next insertion value be
>the natural Manuel> signed integer and not some large positive value.  This
>way MySQL won't Manuel> break the behaviour it had in previous stable
>non-alpha versions.

>I shall at least at look at this, but I can't' promise to have this in
>the next MySQL release that we are working on releasing pretty soon.

The bug itself doesn't bother me, although it would be better if I could
just remove MySQL bug alert from Metabase documentation manual.


Regards,
Manuel Lemos

Web Programming Components using PHP Classes.
Look at: http://phpclasses.UpperDesign.com/?user=mlemos@ style="color:#666">stripped
--
E-mail: mlemos@stripped
URL: http://www.mlemos.e-na.net/
PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp
--