Bobcares

For every $500 you spend, we will provide you with a $500 credit on your account*

BLACK FRIDAY SPECIAL

*The maximum is $4000 in credits, Offer valid till November 30th, 2024, New Customers Only, Credit will be applied after purchase and expires after six (6) months

For every $500 you spend, we will provide you with a $500 credit on your account*

BLACK FRIDAY SPECIAL

*The maximum is $4000 in credits, Offer valid till November 30th, 2024, New Customers Only, Credit will be applied after purchase and expires after six (6) months

Fix Auto Increment Gaps In MYSQL | How To?

by | Feb 3, 2023

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;
 
1 2 3

 

 

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;
 
1 2 4

 

 

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;
 
1 2 3 4 5
 

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.

 

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Never again lose customers to poor
server speed! Let us help you.

Privacy Preference Center

Necessary

Necessary cookies help make a website usable by enabling basic functions like page navigation and access to secure areas of the website. The website cannot function properly without these cookies.

PHPSESSID - Preserves user session state across page requests.

gdpr[consent_types] - Used to store user consents.

gdpr[allowed_cookies] - Used to store user allowed cookies.

PHPSESSID, gdpr[consent_types], gdpr[allowed_cookies]
PHPSESSID
WHMCSpKDlPzh2chML

Statistics

Statistic cookies help website owners to understand how visitors interact with websites by collecting and reporting information anonymously.

_ga - Preserves user session state across page requests.

_gat - Used by Google Analytics to throttle request rate

_gid - Registers a unique ID that is used to generate statistical data on how you use the website.

smartlookCookie - Used to collect user device and location information of the site visitors to improve the websites User Experience.

_ga, _gat, _gid
_ga, _gat, _gid
smartlookCookie
_clck, _clsk, CLID, ANONCHK, MR, MUID, SM

Marketing

Marketing cookies are used to track visitors across websites. The intention is to display ads that are relevant and engaging for the individual user and thereby more valuable for publishers and third party advertisers.

IDE - Used by Google DoubleClick to register and report the website user's actions after viewing or clicking one of the advertiser's ads with the purpose of measuring the efficacy of an ad and to present targeted ads to the user.

test_cookie - Used to check if the user's browser supports cookies.

1P_JAR - Google cookie. These cookies are used to collect website statistics and track conversion rates.

NID - Registers a unique ID that identifies a returning user's device. The ID is used for serving ads that are most relevant to the user.

DV - Google ad personalisation

_reb2bgeo - The visitor's geographical location

_reb2bloaded - Whether or not the script loaded for the visitor

_reb2bref - The referring URL for the visit

_reb2bsessionID - The visitor's RB2B session ID

_reb2buid - The visitor's RB2B user ID

IDE, test_cookie, 1P_JAR, NID, DV, NID
IDE, test_cookie
1P_JAR, NID, DV
NID
hblid
_reb2bgeo, _reb2bloaded, _reb2bref, _reb2bsessionID, _reb2buid

Security

These are essential site cookies, used by the google reCAPTCHA. These cookies use an unique identifier to verify if a visitor is human or a bot.

SID, APISID, HSID, NID, PREF
SID, APISID, HSID, NID, PREF