How to fix auto increment gaps in MYSQL? With our MySQL Support Services, Bobcares offers solutions to your MySQL queries.

How to fix auto increment gaps in MYSQL?

While deleting a row in a table, the numbering for the auto-increment value will continue with the next value. For example, consider the below table:

CREATETABLE test_Identity (     id int(11)NOTNULLAUTO_INCREMENT,PRIMARYKEY(id));INSERTINTO test_Identity VALUES(),(),();SELECT * FROM test_Identity;

If we remove the final row, the numbering will proceed with 4 and not 3. In other words, the counter’s most recent value is retained and used for the next time rows are added:

DELETEFROM test_Identity WHERE id=3;INSERTINTO test_Identity VALUES();SELECT * FROM test_Identity;

So in order to fix this issue and avoid the gaps, we’ve to set the counter value manually. So in the case of the above query, we need to change it as:

DELETEFROM test_Identity WHERE id=4;ALTERTABLE test_Identity AUTO_INCREMENT = 3;INSERTINTO test_Identity VALUES(),(),();SELECT * FROM test_Identity;

Will keys run out?

Keys running out: The possibility of running out of primary key numbers is still another concern. We may access more than 2 billion values with an INT (2,147,483,647). Of course, we would possibly run out of keys faster if we were developing something like an HTTP request logger. However, the issue here is not the key; rather, it is that we are employing the incorrect technology for the task. Both the auto-increment value and the request log are hardly ever cleared. This implies that eventually, we will run out of values. Usually, only entries older than a year are removed.

Solution: Using BIGINT would be a fix; at the very least, it would postpone the problem for years. The internet will almost certainly fail, but we will still have more than nine quintillions until we run out.

Missing auto-increment values may simply indicate that the data was previously present in the table, which would not necessarily mean that a query failed to insert it. Although it technically still constitutes a failure, this failure was anticipated. It has been attempted to be avoided by using the duplicate key update id=id, but using InnoDB, will not stop the auto-increment value from increasing.

A select query to check if the row already exists is one technique to stop it from occurring. However, that would provide a short window of opportunity between the select and insert statements for someone to insert the data, thus it would not totally solve the issue and occasionally might even result in even worse outcomes.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, our Support team went over the details on how to fix auto increment gaps in MYSQL.