Bug #107128 | InnoDB table: gaps in auto increment doing LOAD DATA o INSERT INTO ... SELECT | ||
---|---|---|---|
Submitted: | 26 Apr 2022 10:13 | Modified: | 26 Apr 2022 13:54 |
Reporter: | Agostino di Salle | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 8.0.28 | OS: | Red Hat |
Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[26 Apr 2022 10:13]
Agostino di Salle
[26 Apr 2022 13:17]
MySQL Verification Team
Hi, > with auto increment we noted gaps in auto increment when inserting rows SQL standard does not promise you there will be no gaps nor that id's will be in order. This is not a bug, this is by design. Thanks for your report
[26 Apr 2022 13:24]
Agostino di Salle
Maybe I don't understand but if I'm inserting 512 or 513 or 600 rows why the server need always to allocate 1023 ids ? The issue is not about out of order but about the fact that the sw is always allocating a predictable range of id generally greater that needed. Why do not allocate exactly the same like the number of rows being inserted? Like it's done by INSERT INRTO tables VALUES (...), (...), (...) ? Regards, Agostino
[26 Apr 2022 13:39]
MySQL Verification Team
Hi Agostino, It is done because of the speed as multiple threads can insert rows in the same time; so every thread will allocate some number of ID's. If each thread would have to allocate id for every row it would cause high contention and would severely limit performance. As I already stated, SQL standard allows for this so holes in auto increment fields are normal and expected. You only do not have these holes in table lock storage engines like myisam. For some storage engines (e.g. ndbcluster) the amount of id's being allocated is tunable, in others it is hardcoded (and can be changed without warning through versions). kind regards Bogdan Kecman
[26 Apr 2022 13:54]
Agostino di Salle
Hi Bodgan, just a real example: - LOAD DATA of 511 rows... the server always allocate exactly 511 ids. - LOAD DATA of 512 rows... the server always allocate exactly 1023 ids. What I don't understand is why in the second example the server can't allocate just 512 ids instead of 1023. Agostino
[26 Apr 2022 14:01]
MySQL Verification Team
Hi, This is now turning into "how MySQL Server works" and for that there is no place in bugs system. As I mentioned, this is not a bug and is how SQL standard allows and most SQL servers implement the feature. For why/how please either contact our MySQL Support system or use free https://forums.mysql.com/ kind regards