Jump to content

Archived

This topic is now archived and is closed to further replies.

Kris

When auto_increment field reaches its maximum int

Recommended Posts

What happens when an auto_incrementing field reaches its highest integer? Does it reset back to zero, providing a zero doesn't currently exist?

 

I ask this as on a few of my sites, the databases update themselves periodically with a cron job, so a load of records get deleted and a new bunch inserted, I want to know what happens when it reaches its maximum 99,999,999,999 records (I have them set at int(11)).

Share this post


Link to post
Share on other sites

Personally, I strongly doubt that you will ever reach the 99,999,999,999 limit. What could possibly have that many records needed?

Share this post


Link to post
Share on other sites
Personally, I strongly doubt that you will ever reach the 99,999,999,999 limit. What could possibly have that many records needed?

307099[/snapback]

I have a database of 15,000+ products that is updated every morning, they are updated by category, one category is emptied, and then replenished with the updated products. The recently added products are given a new id by MySQL. So with the auto_incrementing id increasing by 15,000+ a day, I think 99,999,999,999 is a possibility.

 

So, when it does reach 99,999,999,999 - Will it revert back to zero? As ids 0 - 99,999,984,999 will be free...

Share this post


Link to post
Share on other sites

I'm not even going to ask why you have 15000 products a day, but...

just by making your INT(11) into an INT(22) would increase the amount by a lot more. If your database can support that many products, then I think that it should be able to suport a longer INT. And if you don't have the ability to increase that number, it may be a good idea to switch over to Microsoft SQL Server, or some similar product, to have an industrial-strength databse.

Share this post


Link to post
Share on other sites

Hi SA,

With 15K new IDs a day, you would still need 6,666,666 days to reach 99,999,999,999... That only 18,252 years. Not sure you'll be around to see it happen. ;)

 

Share this post


Link to post
Share on other sites
Hi SA,

With 15K new IDs a day, you would still need 6,666,666 days to reach 99,999,999,999... That only 18,252 years. Not sure you'll be around to see it happen. ;)

307254[/snapback]

Thanks for the workings out there Cook, at least I know I will be safe for a little longer! But still the question stands, say I only had it set at an int(3), what happens when it reaches a very plausable 999?

I'm not even going to ask why you have 15000 products a day, but...

just by making your INT(11) into an INT(22) would increase the amount by a lot more. If your database can support that many products, then I think that it should be able to suport a longer INT. And if you don't have the ability to increase that number, it may be a good idea to switch over to Microsoft SQL Server, or some similar product, to have an industrial-strength databse.

You didn't read my post, I don't have 15,000+ new products a day. The number of available records is irrelivent though, the question still stands. Lets say I set the id at a rather silly int(3) - What happens when it reaches 999? Although it might be quicker and easier to just try that out with a for loop...

 

The value will remain at the highest for that size field.
Thanks for actually answering my question Rnell, so I guess when it does reach that point, it won't allow any more enteries and just return an error.

Share this post


Link to post
Share on other sites

I have a strange feeling that your not using Database normalisazion correctly if you have a site with 15 000 new entries daily with an auto num and don't know how it works. odds are you could be doing Updates or things of the like. look into it

Share this post


Link to post
Share on other sites

I could be running updates, but the feeds from other places I'm using dont have consistany primary keys, I tried coding my database update scripts so that it updates the records but it took over twice as long searching if an entery existed and if it did update it should there be any changes, it worked a lot quicker just empying and inserting everything. Its on a shared server so the less load, the smoother things run. But anyway, this is going WAY off topic, consider this thread closed.

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.