Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-914

LP #1475117: Auto-increment sequence gets reset


    • Type: Bug
    • Status: Done
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/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>;

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

        Smart Checklist




              • Assignee:
                lpjirasync lpjirasync (Inactive)
              • Votes:
                0 Vote for this issue
                1 Start watching this issue


                • Created: