SQLAlchemy autoincrement not inserting correctly

Question

I created a customer table with autoincrement id, the problem is after id:5 it inserted 151, 152, 153... how this is happening? is there has any way to fix this?

It might be the same problem as this [Auto Increment skipping numbers?](https://stackoverflow.com/questions/17798835/auto-increment-skipping-numbers). I do not know if they changed anything about the behavior of the auto increment. (see [this](https://www.google.com/search?q=mysql+auto+increment+skipping+numbers+site:stackoverflow.com) for more result about this on SO) — Luuk, Nov 25 '21 at 14:42
It is reproduceable , see this [DBFIDDLE](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=970b779073372b6f6562f1d858f22a3b) — Luuk, Nov 25 '21 at 14:52

Bill Karwin · Answer 1 · 2021-11-25T14:59:42.210

2

There are at least five ways this could happen.

Someone deliberately inserted a row with id=150. This advances the next auto-increment for the table automatically. I.e. auto-increment will not generate a value less than the greatest id in the table.
There were 145 failed INSERTs. By default, InnoDB allocates the next auto-inc value, then tries the INSERT. But it doesn't "undo" the auto-inc if the INSERT fails. So if you have a lot of failed attempts to INSERT rows, for example if they violate other table constraints, then you "lose" auto-inc values. They are allocated, but not used in a row.
Some rows were INSERTed with those values between 6 and 150, then subsequently deleted.
InnoDB spontaneously "skipped" some values. This can happen. Auto-increment must generate unique values, but it is not guaranteed to generate consecutive values.
auto_increment_increment was set to 145 temporarily, then reset to 1, its default value. This doesn't happen by accident, it would have been deliberate.

edited Nov 25 '21 at 14:59

answered Nov 25 '21 at 14:55

Bill Karwin

499,602
82
638
795

Is there is any way to avoid this? – Fasil K Nov 25 '21 at 14:56
Why do you need to? – Bill Karwin Nov 25 '21 at 14:57
actually, I have to generate a unique id for each customer so I pick the last id then add it to a unique customer id like CS5, CS6 ... – Fasil K Nov 25 '21 at 14:59
And is there a roll call of customers, so someone would notice there are no customers CS6 through CS150? – Bill Karwin Nov 25 '21 at 15:00
Yeah, my client will notice. – Fasil K Nov 25 '21 at 15:03
2

Then don't use the built-in auto-increment feature to generate customer id's. Generate them consecutively using some other solution. – Bill Karwin Nov 25 '21 at 15:03

SQLAlchemy autoincrement not inserting correctly

1 Answers1