CODEWITHSUNDEEP
pythonmysqlflasksqlalchemyflask-sqlalchemy
Fasil K
Fasil K

Reputation: 493

SQLAlchemy autoincrement not inserting correctly

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?

Upvotes: 2

Views: 586

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562230

There are at least five ways this could happen.

  1. 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.

  2. 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.

  3. Some rows were INSERTed with those values between 6 and 150, then subsequently deleted.

  4. InnoDB spontaneously "skipped" some values. This can happen. Auto-increment must generate unique values, but it is not guaranteed to generate consecutive values.

  5. 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.

Upvotes: 2

Related Questions