zq29 Posted October 16, 2005 Share Posted October 16, 2005 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)). Link to comment Share on other sites More sharing options...
moberemk Posted October 16, 2005 Share Posted October 16, 2005 Personally, I strongly doubt that you will ever reach the 99,999,999,999 limit. What could possibly have that many records needed? Link to comment Share on other sites More sharing options...
zq29 Posted October 16, 2005 Author Share Posted October 16, 2005 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... Link to comment Share on other sites More sharing options...
moberemk Posted October 16, 2005 Share Posted October 16, 2005 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. Link to comment Share on other sites More sharing options...
rsnell Posted October 16, 2005 Share Posted October 16, 2005 The value will remain at the highest for that size field. Link to comment Share on other sites More sharing options...
Cook Posted October 17, 2005 Share Posted October 17, 2005 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. Link to comment Share on other sites More sharing options...
zq29 Posted October 17, 2005 Author Share Posted October 17, 2005 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. Link to comment Share on other sites More sharing options...
daiwa Posted October 17, 2005 Share Posted October 17, 2005 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 Link to comment Share on other sites More sharing options...
zq29 Posted October 17, 2005 Author Share Posted October 17, 2005 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. Link to comment Share on other sites More sharing options...
Recommended Posts