Laravel 5.3: Auto_increment jump (id) (Gaps)

Question

I did a basic CRUD using Eloquent.

My problem is time to create (Create). I use the code below:

public function store(Request $request)
{
    $subscription = new Subscription;
    // Basic Data
    $subscription->custom_id = '10001';
    $subscription->service_id = 1;
    $subscription->imported = 1;
    $subscription->review = 0;
    $subscription->inserted_by = \Auth::user()->id;
    $subscription->save();
}

Very simple. I can enter data normally. However, when an error occurs (for example the error Duplicate entry) and I’ll insert the next record, it skips a number (id) in the database. See images below

1) I enter a record

2) I reproduce an error

3) I enter the next record normally (10002)

Note that the next record is with id 3, and I believe it should be 2, because it did not enter the second record because I forced an error.

Why does this happen?

It is possible to avoid?

This happens to all systems or only Eloquent?

Answer 1

As you quoted the Wallace Maxters, I have done further research on the subject and the David Spillett gave a good explanation:

Why does this happen?

This is not uncommon and there are a couple of causes. Sometimes it is due to optimizations that the query Runner does to reduce containment problems with the counter feature, improving efficiency when there are simultaneous updates to the affected table. Sometimes it is due to transactions that have been explicitly reversed (or implicitly reversed due to encountering an error).

The only guarantees of an auto_increment column (or IDENTITY in MSSQL and the other names that the concept passes) is that each value will be unique and never less than a previous one: so you can trust the values to sort but can’t trust them to have no gaps. David Spillett

It is possible to avoid?

If you need column values to have no flaws, you will need to manage the values alone, whether in another logic layer business or DB through a trigger (beware of potential performance issues with triggers). David Spillett

Happens with other systems?

From what we understand this is something that happens in Mysql. May or May not depend on other systems, depending on whether they allow auto_increment attribute management.

References also in Mysql Doc: AI Handling

Answer 2

It has already been explained very well in a reply that this is an expected behavior.

Answering the other two questions:

This happens to all systems or only Eloquent?

As stated, this is how Mysql works. Whether you do transaction control or not, this will continue to happen. I believe Mysql does this to avoid internal problems regarding the definition of a key to be inserted if there are insertions occurring from multiple places at the same time.

It is possible to avoid?

I’ll answer with another question: Why avoid?. If Mysql is already doing so automatically, leave things to it. As already said, perhaps for more bureaucratic issues (such as internal reserve of increment numbers) Mysql decided that it would be better so.

Besides, you don’t have to be so perfectionist as to want the Ids to be inserted straight, cute, all in the right order.

In my opinion, what matters is that you will always have Mysql generating a unique key for you.

If you need the "right" sequence, do otherwise, because the mechanism of AUTO_INCREMENT That’s the one and there’s not much to do.

_{Maybe enumerating from a loop can be the solution, if the problem is only visual.}

Bonus - And when I delete all records, but the AUTO_INCREMENT continues?

If you want to "reset" the AUTO_INCREMENT of your table after you have "reset" all the records you had in it, you can run this command:

ALTER TABLE minha_tabela AUTO_INCREMENT=1

This causes the AUTO_INCREMENT counter of that table to start from the 1, since, when deleting all records, the AUTO_INCREMENT count continues following the last inserted element.