Sorry for taking long to get back to you, I've had a nice
stream of higher priority bugs to fix :-)
On 11/12/2010 11:36 AM, Guilhem Bichot wrote:
>> 3179 Jon Olav Hauglid 2010-11-10
>> Bug #50619 assert in handler::update_auto_increment
>> This assert could happen during subsequent inserts if -1 had
>> already been inserted into an auto increment column.
> We spend time testing and fixing cases which the manual prohibits:
> "An AUTO_INCREMENT column works properly only if it contains only
> positive values. Inserting a negative number is regarded as inserting a
> very large positive number. This is done to avoid precision problems
> when numbers “wrap” over from positive to negative and also to ensure
> that you do not accidentally get an AUTO_INCREMENT column that contains 0."
I see your point.
Do you think we should handle this bug differently?
>> +CREATE TRIGGER tr1 BEFORE DELETE ON t1 FOR EACH ROW SET @aux = 1 ;
> This trigger is needed to provoke the crash, I checked. Do you know why?
> I cannot explain it to myself, it only sets a variable...?
The trigger was needed for the test case as REPLACE otherwise
is done as a row update. With the trigger, REPLACE is done as
delete + insert, which causes handler::update_auto_increment() to
be called the extra time needed to trigger the assert.
I've simplified the test case to just:
CREATE TABLE t1 (pk INT AUTO_INCREMENT, PRIMARY KEY (pk));
INSERT INTO t1 VALUES (NULL), (-1), (NULL);
The first NULL causes auto_inc_interval_for_cur_row to be
generated, with 1 as lower bound.
-1 causes the next_insert_id to become 0 due to overflow
And the last NULL (can be any value), causes
handler::update_auto_increment() to be called the final time
needed to trigger the assert.
> What if the auto_increment column is BIGINT UNSIGNED? Such column has a
> range of [0, 18446744073709551615] (the entire range of ulonglong).
Good point. I've made a new version of the patch to take this
into account. I now only update next_insert_id if the auto
increment column is unsigned or the explicit value is positive.
Thanks for the comments!
--- Jon Olav