Bug #71232 Wrong behaviour for auto_increment unsigned bigint column approaching max value

• Submitted: 26 Dec 2013 11:34
• Modified: 4 Jul 2014 9:51
• Reporter: Valeriy Kravchuk
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.6.15
• OS: Any
• CPU Architecture: Any

[26 Dec 2013 11:34] Valeriy Kravchuk

Description:
While checking bug #71188 (that is declared "Not a bug") I've noted that behavior highlighted by simple test in this comment, "[20 Dec 23:24] Przemyslaw Malkowski", there is still buggy probably. So, I created a very simple and short test case to point this out:

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -uroot -proot -P3314 test
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.6.15-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table ai (id bigint unsigned auto_increment primary key, c1 int) e
ngine=InnoDB;
Query OK, 0 rows affected (3.25 sec)

mysql> insert into ai values(18446744073709551613, 1) on duplicate key update c1
=c1+1;
Query OK, 1 row affected (0.36 sec)

mysql> select * from ai;
+----------------------+------+
| id                   | c1   |
+----------------------+------+
| 18446744073709551613 |    1 |
+----------------------+------+
1 row in set (0.13 sec)

mysql> insert into ai values(NULL, 1) on duplicate key update c1=c1+1;
Query OK, 1 row affected (0.11 sec)

mysql> select * from ai;
+----------------------+------+
| id                   | c1   |
+----------------------+------+
| 18446744073709551613 |    1 |
| 18446744073709551614 |    1 |
+----------------------+------+
2 rows in set (0.01 sec)

mysql> insert into ai values(NULL, 1) on duplicate key update c1=c1+1;
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

So, InnoDB probably tried to allocate a "batch" of auto-inc values and hit the limit, but as the above was just a simple single row insert, and because actually next value can be inserted:

mysql> insert into ai values(18446744073709551615, 1) on duplicate key update c1
=c1+1;
Query OK, 1 row affected (0.06 sec)

mysql> select * from ai;
+----------------------+------+
| id                   | c1   |
+----------------------+------+
| 18446744073709551613 |    1 |
| 18446744073709551614 |    1 |
| 18446744073709551615 |    1 |
+----------------------+------+
3 rows in set (0.00 sec)

I'd say that InnoDB should be smarter while generated that auto_increment value approaching maximum possible unsigned bigint. I'd call this a bug in the storage engine and/or (while the bug is not fixed) a documentation problem (as manual does NOT explain this case, that is different from other integer types).

For your information:

mysql> show variables like 'innodb_autoinc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set (0.13 sec)

How to repeat:
create table ai (id bigint unsigned auto_increment primary key, c1 int) engine=InnoDB;
insert into ai values(18446744073709551613, 1) on duplicate key update c1
=c1+1;
select * from ai;

insert into ai values(NULL, 1) on duplicate key update c1=c1+1;
select * from ai;

insert into ai values(NULL, 1) on duplicate key update c1=c1+1;
insert into ai values(18446744073709551615, 1) on duplicate key update c1
=c1+1;
select * from ai;

Suggested fix:
Fix generation of auto_increment value in InnoDB for this case. Explain current limitation in the manual clearly in the meantime.

[4 Jul 2014 9:50] MySQL Verification Team

Hello Valeriy,

Thank you for the report.
Verified as described.

//
mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.20-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> create table ai (id bigint unsigned auto_increment primary key, c1 int) engine=InnoDB;
Query OK, 0 rows affected (0.34 sec)

mysql> insert into ai values(18446744073709551613, 1) on duplicate key update c1
    -> =c1+1;
Query OK, 1 row affected (0.03 sec)

mysql> select * from ai;
+----------------------+------+
| id                   | c1   |
+----------------------+------+
| 18446744073709551613 |    1 |
+----------------------+------+
1 row in set (0.00 sec)

mysql> insert into ai values(NULL, 1) on duplicate key update c1=c1+1;
Query OK, 1 row affected (0.04 sec)

mysql> select * from ai;
+----------------------+------+
| id                   | c1   |
+----------------------+------+
| 18446744073709551613 |    1 |
| 18446744073709551614 |    1 |
+----------------------+------+
2 rows in set (0.00 sec)

mysql>
mysql> insert into ai values(NULL, 1) on duplicate key update c1=c1+1;
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine
mysql> insert into ai values(18446744073709551615, 1) on duplicate key update c1
    -> =c1+1;
Query OK, 1 row affected (0.06 sec)

mysql> select * from ai;
+----------------------+------+
| id                   | c1   |
+----------------------+------+
| 18446744073709551613 |    1 |
| 18446744073709551614 |    1 |
| 18446744073709551615 |    1 |
+----------------------+------+
3 rows in set (0.00 sec)