Laravel 5.3: Jump of auto_increment (id) (Gaps)

Question

Laravel 5.3: Jump of auto_increment (id) (Gaps)

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

I did a basic CRUD using Eloquent.

My problem is when it's time to 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();
}

Pretty simple. I can enter data normally. However, when an error occurs (for example the error Duplicate entry ) and I'm going to insert the next record, it skips a number (id) in the database. See pictures below

1) I enter a record

2)Ireproducedanerror

3)Ienterthenextrecordnormally(10002)

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

Why does this happen?

Can it be avoided?

Does this happen with all systems or only with Eloquent?

php laravel-eloquent laravel-5

asked by anonymous 30.11.2016 / 19:16

2 answers

1

As you mentioned Wallace Maxters , I gave one more research on the subject and 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 contention issues with the counter feature, improving efficiency when there are concurrent updates to the affected table. Sometimes it is due to transactions that were explicitly rolled back (or implicitly reversed due to an error).

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

Can it be avoided?

If you need the column values to be free from crashes, you need to manage the values alone, either in another layer of logic business or in the DB by means of a trigger (beware of potential performance issues with triggers). David Spillett

Does it happen to other systems?

So you understand this is something that happens in Mysql. It may or may not depend on other systems, depending on whether they allow auto_increment attribute management.

References also in the Mysql Doc: AI Handling

30.11.2016 / 19:53

Get selected object id attribute in JComboBox Change ListView color by selecting [duplicate]

score 1 · Accepted Answer

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

Responding to the other two questions:

Does this happen with all systems or only with Eloquent?

As I said, this is how Mysql works. Regardless of whether you do transaction control or not, this will keep happening. I believe Mysql does this to avoid internal issues regarding setting a key to be inserted if there are insertions occurring from multiple places at the same time.

Can it be avoided?

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

What's more, you do not have to be so perfectionist to the point that you want the IDs to be inserted right, 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 "straight" sequence, do it differently, since the AUTO_INCREMENT mechanism is the same and there is not much to do.

Maybe enumerating from a loop might be the solution if the problem is just visual.

Bonus - And when I delete all records, but does AUTO_INCREMENT continue?

If you want to "reset" the AUTO_INCREMENT of your table since you have "zeroed" 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 1 , since, when deleting all the records, the AUTO_INCREMENT count continues following the last inserted element.