Bug #39828 autoinc wraps around when offset and increment > 1

Submitted: 3 Oct 2008 4:54 Modified: 10 Jan 2011 3:45
Reporter: Sunny Bains Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.1.28, 5.1.48, 6.0.6 OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any
Tags: autoinc, regression

[3 Oct 2008 4:54] Sunny Bains 
Description:
When the offset and increment are > 1, in a multi-value insert the next value generated for an autoinc column wraps around. I was testing a fix for InnoDB and it started to fail on one of my tests. I then tested it with a MyISAM table and I got the same behavior.

How to repeat:
#
# Check for overflow handling when increment is > 1
SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
SET @@INSERT_ID=1;
SHOW VARIABLES LIKE "%auto_inc%";
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1));
INSERT INTO t1 VALUES(NULL);
INSERT INTO t1 VALUES (18446744073709551603);
SELECT * FROM t1;
SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10;
SHOW VARIABLES LIKE "%auto_inc%";
# This should fail because of overflow
INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
SELECT * FROM t1;
DROP TABLE t1;

Output from last select:
mysql> SELECT * FROM t1;
+----------------------+
| c1                   |
+----------------------+
|                    0 | <-- This is due to wrap-around
|                    1 |
| 18446744073709551603 |
| 18446744073709551604 |
| 18446744073709551606 |
| 18446744073709551608 |
| 18446744073709551610 |
| 18446744073709551612 |
| 18446744073709551614 |
+----------------------+

Suggested fix:
The last insert should fail and not wrap around.
[3 Oct 2008 5:42] Valeriy Kravchuk 
Verified with 5.1.28.

5.0.x demonstrates correct behaviour, so this is a regression:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.66a-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table t1;
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) engi
ne=MyISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t1 VALUES(NULL);
Query OK, 1 row affected (0.13 sec)

mysql> INSERT INTO t1 VALUES (18446744073709551603);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+----------------------+
| c1                   |
+----------------------+
|                    1 |
| 18446744073709551603 |
+----------------------+
2 rows in set (0.03 sec)

mysql> INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) engi
ne=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO t1 VALUES(NULL);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO t1 VALUES (18446744073709551603);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+----------------------+
| c1                   |
+----------------------+
|                    1 |
| 18446744073709551603 |
+----------------------+
2 rows in set (0.00 sec)

mysql> SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET
=10;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW VARIABLES LIKE "%auto_inc%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 2     |
| auto_increment_offset    | 10    |
+--------------------------+-------+
2 rows in set (0.03 sec)

mysql> INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine
[3 Oct 2008 5:43] Valeriy Kravchuk 
6.0.x is also affected:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3311 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 6.0.6-alpha-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table t1;
Query OK, 0 rows affected (0.36 sec)

mysql> CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) engi
ne=MyISAM;
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO t1 VALUES(NULL);
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO t1 VALUES (18446744073709551603);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+----------------------+
| c1                   |
+----------------------+
|                    1 |
| 18446744073709551603 |
+----------------------+
2 rows in set (0.05 sec)

mysql> SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET
=10;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW VARIABLES LIKE "%auto_inc%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 2     |
| auto_increment_offset    | 10    |
+--------------------------+-------+
2 rows in set (0.03 sec)

mysql> INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;
+----------------------+
| c1                   |
+----------------------+
|                    0 |
|                    1 |
| 18446744073709551603 |
| 18446744073709551604 |
| 18446744073709551606 |
| 18446744073709551608 |
| 18446744073709551610 |
| 18446744073709551612 |
| 18446744073709551614 |
+----------------------+
9 rows in set (0.00 sec)
[19 Dec 2008 0:32] Timothy Smith 
Parts of the innodb-autoinc.test are commented out because the handler.cc:handler::update_auto_increment() asserts when autoinc values overflow.  As part of fixing this bug, that test case should be updated.  Look for 39828 in the comments in that test case.
[5 May 2010 15:06] Bugs System 
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:00] Bugs System 
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:29] Bugs System 
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:56] Bugs System 
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:03] Bugs System 
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:45] Bugs System 
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:30] Bugs System 
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[15 Jul 2010 7:58] Shane Bester 
It should be noted that the testcase crashes a debug server like this:

Version: '5.1.48-enterprise-gpl-advanced-debug'  socket: ''  port: 3306  MySQL Enterprise Server - Advanced Edition Debug (GPL)
Assertion failed: next_insert_id >= auto_inc_interval_for_cur_row.minimum(), file .\handler.cc, line 2330

mysqld-debug.exe!my_sigabrt_handler()[mysqld.cc:2049]
mysqld-debug.exe!raise()[winsig.c:590]
mysqld-debug.exe!abort()[abort.c:71]
mysqld-debug.exe!_wassert()[assert.c:212]
mysqld-debug.exe!handler::update_auto_increment()[handler.cc:2330]
mysqld-debug.exe!ha_myisam::write_row()[ha_myisam.cc:777]
mysqld-debug.exe!handler::ha_write_row()[handler.cc:4654]
mysqld-debug.exe!write_record()[sql_insert.cc:1606]
mysqld-debug.exe!mysql_insert()[sql_insert.cc:835]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:3206]
mysqld-debug.exe!mysql_parse()[sql_parse.cc:5994]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1241]
mysqld-debug.exe!do_command()[sql_parse.cc:882]
mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1134]
mysqld-debug.exe!pthread_start()[my_winthread.c:85]
mysqld-debug.exe!_callthreadstart()[thread.c:293]
mysqld-debug.exe!_threadstart()[thread.c:277]
kernel32.dll!FlsSetValue()
[15 Jul 2010 8:00] Shane Bester 
related: bug #55277
[25 Sep 2010 0:59] Omer Barnir 
triage: changing tag from CHECKED to SR51MRU
[29 Sep 2010 8:12] Nirbhay Choubey 
A simplified test for the above assertion failure :

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1));
SET @@SESSION.AUTO_INCREMENT_INCREMENT=1;
INSERT INTO t1 VALUES(NULL);
INSERT INTO t1 VALUES (18446744073709551613);
SET @@SESSION.AUTO_INCREMENT_INCREMENT=2;
SHOW VARIABLES LIKE "%auto_inc%";
INSERT INTO t1 VALUES (NULL),(NULL);
SELECT * FROM t1;
[2 Oct 2010 18:46] Bugs System 
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/119772

3520 Nirbhay Choubey	2010-10-03
      Bug#39828 : Autoinc wraps around when offset and increment > 1
      
      Auto increment value wraps when performing a bulk insert with
      auto_increment_increment and auto_increment_offset greater than
      one. Howerer, the initial investigation showed that same bug can
      be reproduced even if just auto_increment_increment is greater 
      than one. The bug report also reports of an assertion failure on
      a debug server.
      
      The wrapping of autoinc column happend in update_auto_increment
      function as it failed to check for overflow of next autoinc value
      to be inserted into the column in case of bulk insert. 
      
      Fixed by placing checks for overflow.
     @ mysql-test/suite/innodb/r/innodb-autoinc.result
        Bug #39828 : Autoinc wraps around when offset and increment > 1
     @ mysql-test/suite/innodb/t/innodb-autoinc.test
        Bug #39828 : Autoinc wraps around when offset and increment > 1
     @ mysql-test/suite/innodb_plugin/r/innodb-autoinc.result
        Bug #39828 : Autoinc wraps around when offset and increment > 1
     @ mysql-test/suite/innodb_plugin/t/innodb-autoinc.test
        Bug #39828 : Autoinc wraps around when offset and increment > 1
     @ sql/handler.cc
        Bug #39828 : Autoinc wraps around when offset and increment > 1
        
        Added a condition to check for overflow of 'nr' returned from
        compute_next_insert_id. Apart from that, added one more condition
        towards the beginning of handler::update_auto_increment to check
        if next_insert_id is lesser than insert_id_for_cur_row, which
        inturn will avoid overflowing of autoinc value in case of 
        bulk (multi-valued) insert.
[6 Dec 2010 10:42] Bugs System 
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/126116

3512 Sergey Glukhov	2010-12-06
      Bug#39828 : Autoinc wraps around when offset and increment > 1
      Auto increment value wraps when performing a bulk insert with
      auto_increment_increment and auto_increment_offset greater than
      one.
      The fix:
      If overflow happened then return MAX_ULONGLONG value as an
      indication of overflow and check this before storing the
      value into the field in update_auto_increment().
     @ mysql-test/r/auto_increment.result
        test case
     @ mysql-test/suite/innodb/r/innodb-autoinc.result
        test case fix
     @ mysql-test/suite/innodb/t/innodb-autoinc.test
        test case fix
     @ mysql-test/suite/innodb_plugin/r/innodb-autoinc.result
        test case fix
     @ mysql-test/suite/innodb_plugin/t/innodb-autoinc.test
        test case fix
     @ mysql-test/t/auto_increment.test
        test case
     @ sql/handler.cc
        If overflow happened then return MAX_ULONGLONG value as an
        indication of overflow and check this before storing the
        value into the field in update_auto_increment().
[13 Dec 2010 11:58] Bugs System 
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/126621

3518 Sergey Glukhov	2010-12-13
      Bug#39828 : Autoinc wraps around when offset and increment > 1
      Auto increment value wraps when performing a bulk insert with
      auto_increment_increment and auto_increment_offset greater than
      one.
      The fix:
      If overflow happened then return MAX_ULONGLONG value as an
      indication of overflow and check this before storing the
      value into the field in update_auto_increment().
     @ mysql-test/r/auto_increment.result
        test case
     @ mysql-test/suite/innodb/r/innodb-autoinc.result
        test case fix
     @ mysql-test/suite/innodb/t/innodb-autoinc.test
        test case fix
     @ mysql-test/suite/innodb_plugin/r/innodb-autoinc.result
        test case fix
     @ mysql-test/suite/innodb_plugin/t/innodb-autoinc.test
        test case fix
     @ mysql-test/t/auto_increment.test
        test case
     @ sql/handler.cc
        If overflow happened then return MAX_ULONGLONG value as an
        indication of overflow and check this before storing the
        value into the field in update_auto_increment().
[17 Dec 2010 12:49] Bugs System 
Pushed into mysql-5.1 5.1.55 (revid:georgi.kodinov@oracle.com-20101217124435-9imm43geck5u55qw) (version source revid:sergey.glukhov@oracle.com-20101213114812-kaq7sh0s623128lv) (merge vers: 5.1.55) (pib:24)
[17 Dec 2010 12:53] Bugs System 
Pushed into mysql-5.5 5.5.9 (revid:georgi.kodinov@oracle.com-20101217124733-p1ivu6higouawv8l) (version source revid:sergey.glukhov@oracle.com-20101213121116-vuavnwyno56rha3s) (merge vers: 5.5.8) (pib:24)
[17 Dec 2010 12:56] Bugs System 
Pushed into mysql-trunk 5.6.1 (revid:georgi.kodinov@oracle.com-20101217125013-y8pb3az32rtbplc9) (version source revid:sergey.glukhov@oracle.com-20101213123216-cz5yrib13t7e0y8l) (merge vers: 5.6.1) (pib:24)
[4 Jan 2011 14:31] Bugs System 
Pushed into mysql-trunk 5.6.2 (revid:jon.hauglid@oracle.com-20110104143005-920jhiyi67x17kez) (version source revid:jon.hauglid@oracle.com-20110104143005-920jhiyi67x17kez) (merge vers: 5.6.2) (pib:24)
[4 Jan 2011 14:32] Bugs System 
Pushed into mysql-5.5 5.5.9 (revid:jon.hauglid@oracle.com-20110104142803-kxkbv0ud3x8g2ejd) (version source revid:jon.hauglid@oracle.com-20110104142803-kxkbv0ud3x8g2ejd) (merge vers: 5.5.9) (pib:24)
[4 Jan 2011 14:32] Bugs System 
Pushed into mysql-5.1 5.1.55 (revid:jon.hauglid@oracle.com-20110104133637-33qff7ri9vz47r27) (version source revid:jon.hauglid@oracle.com-20110104133637-33qff7ri9vz47r27) (merge vers: 5.1.55) (pib:24)
[10 Jan 2011 3:45] Paul DuBois 
Noted in 5.1.55, 5.5.9, 5.6.2 changelogs.

When auto_increment_increment is greater than one, values generated
by a bulk insert that reaches the maximum column value could wrap
around rather producing an overflow error.

As a consequence of the fix, it is no longer possible for an
auto-generated value to be equal to the maximum BIGINT UNSIGNED
value. It is still possible to store that value manually, if the
column can accept it.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.061 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.