Auto-increment sequence gets reset

[markus_albe]

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)]

Upstream: http://bugs.mysql.com/bug.php?id=77743

[Valerii Kravchuk (valerii-kravchuk)]

It's easy to confirm with the script provided

[Sveta Smirnova (svetasmirnova)]

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)]

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

[Sveta Smirnova (svetasmirnova)]

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

[Shahriyar Rzayev (rzayev-sehriyar)]

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