Re: AUTO_INCREMENT bug after inserting negative value

[Manuel Lemos]

Precedence: bulk

Hello Michael,

On 10-May-00 09:33:06, you wrote:

>MySQL allows one to start a sequence at any positive number.

Ok. Other DBMS let sequences start with negative number,
so I assumed that would be granted for auto-incremented figures for all
MySQL versions as in the past versions.


>Another thought that I didn't think of:  The mysql client interface
>returns the last_insert_id() as a positive 64 bit number.  This makes
>it very hard to change MySQL to allow also negative numbers.

If you say it is hard to change, you know it better.  Personally I am not
affected by this problem, because my sequences always start at 1.  0 has a
special meaning. I'll leave a note on the Metabase manual to bring this
problem to the attention of the users just in case someone wants to start
in 0 or less.



>Note that you can VERY easily emulate sequences in MySQL 3.23;  Just
>take a note at the LAST_INSERT_ID() section in the MySQL manual.

I wish that was possible in previous versions of MySQL. The current sequence
implementation takes a insert and a delete stamement to not let the
sequence emulation table grow.  I'm afraid would lead to incompatibilities
to switch between the current sequence implementation and one using
LAST_INSERT_ID().  I think I make it an option for users willing to create
new sequences using MySQL 3.23.


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

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

>MySQL has never allowed one to start a sequence at 0; If you try to
>insert 0, MySQL has always changed this to the next possible number.

But if you inserted -1 the next value in 3.21 would be 0 which stands
correct.


>To allow negative values in 3.22 was a bug in 3.22;  As the number was
>reported to the client as a positive number, using negative numbers
>even in 3.22 may cause problems and is not recommended!

I don't know about 3.22, but in 3.21 I was getting correct values.


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

>I can understand that;  The questions is if there is any problem with
>only allowing positive auto_increment numbers (at least with MySQL).

As I said, not on my Metabase applications. Can't speak for others.

On a completely different subject, keep up the good work on making MySQL
fully SQL 92 compliant, namely bringing in proper transaction support and
sub-queries.


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