Hi!
<cut>
>> 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...
Manuel> Yes, for my applications I never start at 0 as it has a special meaning.
Manuel> But I can't speak for others that want to use MySQL with Metabase. MySQL
Manuel> does not support sequences as many other DBMS. Others let sequences start
Manuel> at any integer value. If it was not for that bug, MySQL could allow that
Manuel> too.
MySQL allows one to start a sequence at any positive number.
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.
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.
>>>> 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!
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.
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!
>>>> 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.
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).
Regards,
Monty
