MySQL Bugs: #43524: Issues with bigint unsigned auto-increment column

Bug #43524	Issues with bigint unsigned auto-increment column
Submitted:	10 Mar 2009 3:33	Modified:	22 Oct 2009 10:34
Reporter:	Nidhi Shrotriya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0.11	OS:	Linux
Assigned to:	Lars-Erik BjÃ¸rk	CPU Architecture:	Any
Tags:	F_ENCODING

Description:
See How to repeat section.

How to repeat:
CREATE TABLE t5(c1 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, c2 BIGINT SIGNED NULL, c3 BIGINT SIGNED NOT NULL, c4 TINYINT, c5 SMALLINT, c6 MEDIUMINT, c7 INT, c8 INTEGER, PRIMARY KEY(c1,c2), UNIQUE INDEX(c3))engine=falcon;

--insert some rows
I guess can be reproduced with just inserting below rows.
INSERT INTO t5 VALUES(0,-9223372036854775808,1,2,3,4,5,5),(255,-2147483648,6,7,8,9,10,10),(65535,-8388608,11,12,13,14,15,15),(16777215,-32768,16,17,18,19,20,20),(4294967295,-128,21,22,23,24,25,25),(18446744073709551615,9223372036854775807,26,27,28,29,30,30);

INSERT INTO t5(c2,c3) VALUES(33,34) /* tries to increment out of range */;

Other engines (MyISAM,Innodb,Maria):
ERROR HY000: Failed to read auto-increment value from storage engine

SELECT * FROM t5;

c1      c2      c3      c4      c5      c6      c7      c8

101     -102    -103    -104    105     106     107     108

102     0       0       37      38      39      40      41

108     -109    -110    111     112     113     114     115

109     -32     -32     33      34      35      36      37

110     0       -104    2       NULL    NULL    NULL    NULL

111     0       -17     18      19      20      21      22

112     -1      -1      8       9       10      11      12

113     -101    -102    104     105     106     107     108

114     -108    -109    111     112     113     114     115

115     -115    -116    118     119     120     121     122

116     -122    -123    125     126     127     128     128

117     -9223372036854775808    1       2       3       4       5       5

16777215        -32768  16      17      18      19      20      20

18446744073709551615    9223372036854775807     26      27      28      29      30      30

255     -2147483648     6       7       8       9       10      10

4294967295      -128    21      22      23      24      25      25

65535   -8388608        11      12      13      14      15      15

Falcon:
---------------
doesn't give 
ERROR HY000: Failed to read auto-increment value from storage engine
SELECT * FROM t5;

c1      c2      c3      c4      c5      c6      c7      c8

101     -102    -103    -104    105     106     107     108

102     0       0       37      38      39      40      41

108     -109    -110    111     112     113     114     115

109     -32     -32     33      34      35      36      37

110     0       -104    2       NULL    NULL    NULL    NULL

111     0       -17     18      19      20      21      22

112     -1      -1      8       9       10      11      12

113     -101    -102    104     105     106     107     108

114     -108    -109    111     112     113     114     115

115     -115    -116    118     119     120     121     122

116     -122    -123    125     126     127     128     128

118     -9223372036854775808    1       2       3       4       5       5

16777215        -32768  16      17      18      19      20      20

18446744073709551615    9223372036854775807     26      27      28      29      30      30

255     -2147483648     6       7       8       9       10      10

4294967295      -128    21      22      23      24      25      25

4294967296      33      34      NULL    NULL    NULL    NULL    NULL

65535   -8388608        11      12      13      14      15      15

increments 4294967295.

Thank you for the report.

Verified as described.

test case

Attachment: bug43524.test (application/octet-stream, text), 571 bytes.

Falcon may be interpreting 18446744073709551615 (0xffffffffffffffff) as a negative number, leaving 4294967295 (0xffffffff) as the highest number entered so far.  That may be why the next auto-inc number assigned by Falcon is 4294967296 (0x100000000)

That would be my bet as well

I dug a little deeper, and this seems to be what happens:

Falcon uses Sequences to generate the auto_increment numbers. A Falcon
Sequence is a signed 64 bit integer. When a row is written in Falcon,
some different scenarios can occur.

The user does not specify the values for the auto_commit column
----------------------------------------------------
StorageTableShare::getSequenceValue is called from
StorageInterface::write_row, via handler::update_auto_increment and
StorageInterface::get_auto_increment. The value of the sequence is
incremented by one. When the value reaches the end of the range for
the signed long long, it wraps over to negative values. When this is
returned to the handler, it is however treated as an unsigned value,
and it appears correctly in the table. The Falcon sequence, however,
is now negative, which works, but is quite suspect.

The user specifies the value for the auto_commit column
---------------------------------------------------
This time, the codepath is not the same, but
StorageTableShare::setSequenceValue is called from
StorageInterface::write_row. When setting the maximuim value for an
unsigned bigint, 0xFFFFFFFFFFFFFFFF, this is received as -1. We fetch the
current value of the Falcon Sequence and check if the new value is
greater than the current, and in that case update the value of the
Sequence accordingly.

if (value > current)
   sequence->update(value - current, NULL);

When the latter scenario happens, the comparison when inserting a
value with the sign bit set, will be wrong if the current value of the
Sequence, does not have the sign bit set. Then we will not update the
value of the Sequence. Therefore, when inserting the next row without
specifying a value for the auto_increment column, we will continue
with our old (wrong) Sequence values.

Even though our sequences are signed 64-bit integers, when they are
assigned to an unsigned field, we need to follow the comparison rules
of the field, not the underlying sequence.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/72847

2696 lars-erik.bjork@sun.com	2009-04-27
      This is a possible patch for bug#43524 - Issues with bigint unsigned auto-increment column
      
      StorageTableShare now treats the sequence values as uint64s.
      This way, the sequence is updated and incremented correctly
      when used for auto_increment. When the sequence value has
      reached it's maximum value, 0xFFFFFFFFFFFFFFFF, it won't wrap
      around the next time we try to increment it, but will return
      its current value.
      added:
        mysql-test/suite/falcon/r/falcon_bug_43524.result
        mysql-test/suite/falcon/t/falcon_bug_43524.test
      modified:
        storage/falcon/Dbb.cpp
        storage/falcon/StorageTableShare.cpp
        storage/falcon/StorageTableShare.h

Lars-Erik Bjørk wrote:
> Hi Kevin!
>
> I noticed that you approved the patch for this bug.
> Did you notice that if the current sequence  value is 0xffffffffffffffff, I return the current value if we try to increment? This will make auto_increment work as expected. Do you know if there are any places where this can be a problem? I just want to be sure before pushing it.

Kevin wrote;
Yes, I noticed that.  Then it will fail with a duplicate value error.  There is no value larger than that.  The only reason that a sequence would ever get that high is if the number was explicitly entered, which would be a mistake.  Then that value would have to be fixed and the sequence reset.  I don't think a sequence should automatically wrap around.

A Falcon bug, updating the status to reflect (no documentation entry is needed).