Question 21718 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: 8.0.31 – Large gaps (thousands) in ID Field after few months of usage

We see large gaps in primary key column with auto_increment in mysql 8.0.31 release version in multiple tables (with default – innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)). Also I understand some smaller gaps can happen in this mode.

We have ‘order’ table (id bigint) and ‘order_line_items’ table (id bigint).

TABLE_NAME          | CURRENT AUTO_INCREMENT    |  TABLE ROW COUNT
order               |   27504                   |        1367
order_line_items    |  430930                   |       34970

We have a total of 1367 order rows with current auto increment value of ‘27504’, and 34970 (order line item rows) with current auto increment value of ‘430930’. We save, order and order_line_items all within the same transaction in a API call (there is possibility for error which can lead to transaction rollback).

Gaps:

Order table gaps continued to happen for 1-2 day once we noticed and went back to incrementing by ‘1’ again as of today. Jumps are happening together in clusters continuously (check below) and went back to incrementing by ‘1’.

Order table gap details:

id    |  difference from previous 'id'
-------------------------------------
27489   1
27488   1
27487   1
27486   232
27254   232
27022   693
26329   240
26089   401
25688   175
25513   348
25165   864
24301   7656
16645   1709
14936   184
14752   13
14739   541
14198   82
14116   215
13901   68
13833   117
13716   140
....
1468    1
1467    10
1457    1
1456    1
1455    1
1454    9
1445    1
1444    1
1443    1
1442    1
1441    1

Observations:

  1. We use (OpenJPA) use the configuration (@Id, @GeneratedValue(strategy = GenerationType.IDENTITY) for the ID. Id’s are not set in the code.

  2. We see the same pattern in other tables as well. We tested the same API with parallel creation of the same with 20 threads but we didn’t similar large gaps in the create id’s for both tables.

  3. Till id: 1445 (we have count of 1225 rows) in order table, we don’t see any issue with bigger jumps. After id: 1445, we started seeing multiple jumps in order table till 27487 for 1-2 days continuously.

  4. Also I understand the rollback of transaction’s in API call can increase the auto-increment value, but the multiple bigger jumps compared to total records seems not understandable.

Questions:

  1. Does anyone has any idea what can be the cause for this ?. Why id’s are jumping in multiple thousands (e.g., 7656, 1709 in above example) when the total records are below 1367 in order tables ?.

  2. I see this happens in multiple cluster of islands of the ids jumping and starts back to create one by one (like the above example). Has this relate to any other internal handling of mysql data relate processes. ?. Is this a possible bug in mysql ?

  3. What can cause ‘mysql’ to jump higher like this (for e.g., bulk inserts, can shared locks cause this) ?. How can I simulate this issue locally ?.

Any ideas would be really helpful.

Update:
Multi- API calls were done which were failed at the API level due to unexpected retry from our client side which caused the large gaps in the ids. Thanks everyone.

Tags:

2

Answers


  1. 0

    Well, for starters, I wouldn’t start checking the inserts. They are hardly at fault here. How is the insert done? I would expect that if the API call fails, you’ll rollback, else commit, correct? But keep in mind that if a transaction fails, the sequence is not rolled back.

    I would search the API call logs for errors. Chances are, if you have max id 1099, then the next insert is 1104, I’d say you’ll surely find 4 errors in the log, plus a correct and valid insert.

    Keep in mind, sequences are not meant to "look nice". Yes, if all control codes pass, then of course, each record is n + 1 and it is "properly" (from an aesthetic point-of-view) inserted. But sequences are not about that. An auto_increment simply makes sure that you will not break a PK or UK constraint. That is all. So for any usage, regardless of a COMMIT or ROLLBACK, a sequence is "used". This is by design and guarantees data integrity.

    As for simulating this, you can try a custom API call (or local insert, but if you want to be as close to the prod version as possible, you can use an API call) that always rolls back after inserting. Then checking the current sequence value of the auto_increment.

    Login or Signup to reply.
  2. 0

    Id "gaps" can occur for several reasons:

    • If an INSERT fails, for example by conflicting with another constraint like UNIQUE or FOREIGN KEY, the generation of an id will not be undone. The row will not be inserted, but the id is still incremented, so a value is "lost."

      I helped a client with such an issue once. They had a UNIQUE constraint in the username in the users table, and some people kept trying to sign up as new users on the website using existing usernames. This happened every day, and kept increasing, so eventually there was about 1500 id’s "lost" per day.

    • If the INSERT succeeds, but the transaction is rolled back by your application for some reason.

    • If the auto_increment_increment is set to something other than 1. This is an option that can be set temporarily by any session, so you may not see it in your MySQL Server config file. But it would probably be in the application code.

    • The INSERT specifies a value higher than the next greater id value, overriding the auto-increment. Auto-increment will not generate a value lower than the greatest value in the table, so if an INSERT specifies a higher value, the next INSERT after that will be greater. It won’t fill in the gap it left.

    • Someone ran ALTER TABLE ... AUTO_INCREMENT=... and changed the table’s current next id value.

    • InnoDB bugs. These are rare, but they have been reported that sometimes a table "skips" auto-increment values instead of incrementing strictly by 1. The auto-increment guarantees unique values, but it does not guarantee consecutive values.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search