Auto-increment sequence gets reset

Bug #1475117 reported by markus_albe on 2015-07-16
6
This bug affects 1 person
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-cache=100 and 400 tables opened, but only once. With 500 I could reproduce easily.

Original issue happened with 165K+ explains and table_open_cache=80000; But with 162K it did not happened.

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.

markus_albe (markus-albe) wrote :
tags: added: upstream

It's easy to confirm with the script provided

Sveta Smirnova (svetasmirnova) wrote :

MySQL bug is fixed.

When you will merge the fix, please, check if the fix is also applied to cases where tables still have rows in them, but the highest remaining autoinc field value is lower than the autoinc table value when the table is evicted (i.e. most recent rows were deleted but table is not empty).

Thank you!

Sveta Smirnova (svetasmirnova) wrote :

When you apply patch from upstream, check what this slightly modified test case pass too.

Sveta Smirnova (svetasmirnova) wrote :

Upstream patch for 5.6.27 fixes case with non-empty table too

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-914

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Related blueprints

Remote bug watches

Bug watches keep track of this bug in other bug trackers.