Jump to content


Photo

When auto_increment field reaches its maximum int


  • This topic is locked This topic is locked
8 replies to this topic

#1 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 16 October 2005 - 03:55 PM

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)).

#2 moberemk

moberemk
  • Members
  • PipPipPip
  • Advanced Member
  • 695 posts

Posted 16 October 2005 - 04:01 PM

Personally, I strongly doubt that you will ever reach the 99,999,999,999 limit. What could possibly have that many records needed?
Disclaimer: No matter how harsh I may be, I'm always IMO only.

#3 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 16 October 2005 - 08:49 PM

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

View Post

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...

#4 moberemk

moberemk
  • Members
  • PipPipPip
  • Advanced Member
  • 695 posts

Posted 16 October 2005 - 10:22 PM

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.
Disclaimer: No matter how harsh I may be, I'm always IMO only.

#5 rsnell

rsnell
  • Members
  • Pip
  • Newbie
  • 3 posts

Posted 16 October 2005 - 10:48 PM

The value will remain at the highest for that size field.

#6 Cook

Cook
  • Members
  • PipPipPip
  • Advanced Member
  • 64 posts
  • LocationSingapore

Posted 17 October 2005 - 04:38 AM

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. ;)

Cook

#7 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 17 October 2005 - 08:18 AM

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. ;)

View Post

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.

#8 daiwa

daiwa
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 17 October 2005 - 11:15 AM

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

#9 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 17 October 2005 - 04:55 PM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users