List:Bugs« Previous MessageNext Message »
From:Manuel Lemos Date:April 30 2000 6:09pm
Subject:Re: AUTO_INCREMENT bug after inserting negative value
View as plain text  
Hello Michael,

On 30-Apr-00 10:40:21, you wrote:

>Manuel> Please acknowledge this bug report and let me know when you have
>Manuel> analized/fixed it.  BTW, is there a bug tracking application for
>MySQL Manuel> development like PHP development has?

>We are just about to start using support wizard for our support customers.
>As soon as we are sure we have got all parameters optimized for our
>usage we will extend this to also handle all mails to bugs@stripped

That's nice. Email forms are not that pleasant! :-)


>>> How-To-Repeat:

>Manuel> CREATE TABLE test (sequence INT DEFAULT 0 NOT NULL AUTO_INCREMENT,
>PRIMARY KEY (sequence)); Manuel> INSERT INTO test (sequence) VALUES (-1);
>Manuel> INSERT INTO test (sequence) VALUES (NULL); Manuel> SELECT
>LAST_INSERT_ID(); Manuel> SELECT * FROM test;

>The problem is that MySQL only supports unsigned values for
>auto_increment-fields;  This is true even if you define the
>auto_increment field as a signed value.  (This is basicly sound as it
>will ensure that MySQL can use the full potential range for a number
>as autoincrement values).

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

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


>Inserting -1 is basicly the same as inserting the max value for the
>field. As MySQL can't give you a higher value for the next value it
>will insert the max possible value for the column in the table.

Yes, but what happens is that if you insert a negative value in the field
it remains negative.  Only newly inserted records will get the large
positive integer.


>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!

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


>By the way, I wonder why MetaBase inserts -1 if they want MySQL starts
>from 1.  Just not inserting anything would handle this case much better.

No, that was a typo in my bug report.  I meant 0 where I typed 1.  To get
the initial value set to 1, indeed I don't insert an initial row because
the next row will get 1 without further initialization.


>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'

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

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



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
--

Thread
AUTO_INCREMENT bug after inserting negative valueManuel Lemos30 Apr
  • Re: AUTO_INCREMENT bug after inserting negative valuesinisa30 Apr
  • AUTO_INCREMENT bug after inserting negative valueMichael Widenius30 Apr
    • RE: AUTO_INCREMENT bug after inserting negative valuejason king30 Apr
      • Re: AUTO_INCREMENT bug after inserting negative valueThimble Smith1 May
      • RE: AUTO_INCREMENT bug after inserting negative valueMichael Widenius1 May
    • Re: AUTO_INCREMENT bug after inserting negative valueManuel Lemos30 Apr
      • Re: AUTO_INCREMENT bug after inserting negative valueMichael Widenius1 May
        • Re: AUTO_INCREMENT bug after inserting negative valueManuel Lemos10 May
          • Re: AUTO_INCREMENT bug after inserting negative valueMichael Widenius10 May
            • Re: AUTO_INCREMENT bug after inserting negative valueManuel Lemos5 Jun