Bug #77743 Auto-increment sequence gets reset

Description:
If we insert a row into a table with an auto-increment primary key and then we empty the table using DELETE without conditions, and then we run statements for a number of tables noticeably larger than table-open-cache  then the auto-inc sequence is reset and next INSERT into the table will get an insert_id of 1

We tried SELECTs and EXPLAINs and either produce the issue; The attached script is using EXPLAIN because it seems to show the problem faster.

How to repeat:
Run the attached script like

reproduce.sh 100 2000 10 innodb;

Usage: resetai.sh <table_open_cache> <tables count> <sleep interval> <engine>;

Suggested fix:
Don't allow the sequence to be reset unless table is truncated or altered.

Bash script to reproduce the issue

Attachment: reproduce.sh (application/x-shellscript, text), 1.76 KiB.

Fixed typo in synopsis

Hello Marcos,

Thank you for the report and test case.
Observed this with 5.6.27 build with innodb engine.

Thanks,
Umesh

// 5.6.27

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.27: ./reproduce.sh 100 2000 10 innodb;
initial auto-inc value: 3... Sleeping 10
final auto-inc value: 1
FAILED!!, with 2000 innodb tables and table_open_cache=100; sleep set to 10.

Above should be more than enough to confirm, but just tried to see again:

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.27: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4019
Server version: 5.6.27-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, 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> use TESTAI
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DROP TABLE IF EXISTS TESTAI.every5minutes;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE TESTAI.every5minutes (
    -> Id int(11) NOT NULL AUTO_INCREMENT,
    -> ActionId int(11) NOT NULL DEFAULT '0',
    -> ItemId int(11) NOT NULL DEFAULT '0',
    -> PRIMARY KEY (Id),
    -> KEY ActionId (ActionId),
    -> KEY ItemId (ItemId)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table TESTAI.every5minutes\G
*************************** 1. row ***************************
       Table: every5minutes
Create Table: CREATE TABLE `every5minutes` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `ActionId` int(11) NOT NULL DEFAULT '0',
  `ItemId` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Id`),
  KEY `ActionId` (`ActionId`),
  KEY `ItemId` (`ItemId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1
1 row in set (0.00 sec)

mysql> INSERT INTO TESTAI.every5minutes SET id=2;
Query OK, 1 row affected (0.00 sec)

mysql> show create table TESTAI.every5minutes\G
*************************** 1. row ***************************
       Table: every5minutes
Create Table: CREATE TABLE `every5minutes` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `ActionId` int(11) NOT NULL DEFAULT '0',
  `ItemId` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Id`),
  KEY `ActionId` (`ActionId`),
  KEY `ItemId` (`ItemId`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 CHECKSUM=1
1 row in set (0.00 sec)

mysql> DELETE FROM TESTAI.every5minutes;
Query OK, 1 row affected (0.00 sec)

mysql> show create table TESTAI.every5minutes\G
*************************** 1. row ***************************
       Table: every5minutes
Create Table: CREATE TABLE `every5minutes` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `ActionId` int(11) NOT NULL DEFAULT '0',
  `ItemId` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Id`),
  KEY `ActionId` (`ActionId`),
  KEY `ItemId` (`ItemId`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 CHECKSUM=1
1 row in set (0.00 sec)

mysql> source /export/umesh/server/binaries/mysql-advanced-5.6.27/5_6_27.explains.new;
Database changed
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | ALL   | NULL          | NULL    | NULL    | NULL |    1 | NULL                                               |
|  1 | SIMPLE      | t     | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | t2    | ALL   | NULL          | NULL    | NULL    | NULL |    1 | NULL                                               |
|  1 | SIMPLE      | t     | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

.
.
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | t1999 | ALL   | NULL          | NULL    | NULL    | NULL |    1 | NULL                                               |
|  1 | SIMPLE      | t     | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | t2000 | ALL   | NULL          | NULL    | NULL    | NULL |    1 | NULL                                               |
|  1 | SIMPLE      | t     | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show create table TESTAI.every5minutes\G
*************************** 1. row ***************************
       Table: every5minutes
Create Table: CREATE TABLE `every5minutes` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `ActionId` int(11) NOT NULL DEFAULT '0',
  `ItemId` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Id`),
  KEY `ActionId` (`ActionId`),
  KEY `ItemId` (`ItemId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1
1 row in set (0.00 sec)

## with myisam

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.27: ./reproduce.sh 100 2000 10 myisam;
initial auto-inc value: 3... Sleeping 10
final auto-inc value: 3
Passed, with 2000 myisam tables and table_open_cache=100; sleep set to 10.

// 5.7.9 build seems to be not affected

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: ./reproduce.sh 100 2000 10 innodb;
initial auto-inc value: 3... Sleeping 10
final auto-inc value: 3
Passed, with 2000 innodb tables and table_open_cache=100; sleep set to 10.

// 5.5.46 build not affected as well
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.46: ./reproduce.sh 100 2000 10 innodb;
initial auto-inc value: 3... Sleeping 10
final auto-inc value: 3
Passed, with 2000 innodb tables and table_open_cache=100; sleep set to 10.

Umesh, can you check if this is related to bug #76037?

Posted by developer:
 
autoinc is reset to 0 when table is loaded after table is evicted without any rows. The solution is store autoinc value in a map when table is evicted, and restore autoinc when table is loaded.

Pushed the fixed to 5.6 & 5.7. In trunk, WL#6204 - InnoDB persistent max value for autoinc columns will fix the bug(rb#9138).

Thank you for the quick fix,  Shaohua!

Fixed as of the upcoming 5.6.27, 5.7.9, 5.8.0 releases, and here's the changelog entry:

Reloading a table that was evicted while empty caused an AUTO_INCREMENT
value to be reset. 

Thank you for the bug report.

Shaohua,

did you test cases when table includes few rows (not 100% empty), but auto-increment field still reset? We saw such a case in the environment where bug occurred first time.

Sveta, if reset means auto_increment starts from 1, I don't see such case
with a few rows. When a table is loaded, we usually search for max
auto_increment value in the table, that's why auto_increment gets reset with
empty table. after all, the patch pushed fixed all such reset issues.

Tese case for situation when table is not empty

Attachment: reproduce_non_empty.sh (application/x-shellscript, text), 1.82 KiB.

Bug #78491 marked as duplicate of this