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
• Status: Closed
• 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:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 6:00] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:29] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 6:56] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-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:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-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:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-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:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[15 Jul 2010 7:58] MySQL Verification Team

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] MySQL Verification Team

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:[e-mail]-20101217124435-9imm43geck5u55qw) (version source revid:[e-mail]-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:[e-mail]-20101217124733-p1ivu6higouawv8l) (version source revid:[e-mail]-20101213121116-vuavnwyno56rha3s) (merge vers: 5.5.8) (pib:24)

[17 Dec 2010 12:56] Bugs System

Pushed into mysql-trunk 5.6.1 (revid:[e-mail]-20101217125013-y8pb3az32rtbplc9) (version source revid:[e-mail]-20101213123216-cz5yrib13t7e0y8l) (merge vers: 5.6.1) (pib:24)

[4 Jan 2011 14:31] Bugs System

Pushed into mysql-trunk 5.6.2 (revid:[e-mail]-20110104143005-920jhiyi67x17kez) (version source revid:[e-mail]-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:[e-mail]-20110104142803-kxkbv0ud3x8g2ejd) (version source revid:[e-mail]-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:[e-mail]-20110104133637-33qff7ri9vz47r27) (version source revid:[e-mail]-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.