Affects Version/s: None
Fix Version/s: None
**Reported in Launchpad by markus_albe last update 30-10-2015 09:22:36
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>;
Don't allow the sequence to be reset unless table is truncated or altered.