Auto-increment sequence gets reset
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
|||
Percona Server moved to https://jira.percona.com/projects/PS |
Fix Released
|
High
|
Unassigned | ||
5.6 |
Fix Released
|
High
|
Unassigned |
Bug 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.
As table_open_cache is closer to number of tables opened after the DELETE, the issue becomes harder to reproduce. For example I was able to reproduce with table-open-
Original issue happened with 165K+ explains and table_open_
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.
tags: | added: upstream |
Upstream: http:// bugs.mysql. com/bug. php?id= 77743