• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Junilu Lacar
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Ganesh Patekar

What happens when auto increment id generated future increases to generate a very large int value

 
Ranch Hand
Posts: 1251
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have an auto increment id generated from sql server table. It generates values such as 1,2,3,4 and so on. So when I insert new record in it it generates a new auto increment value. For example if the previous value is 4, it returns 5. It works well. I am just thinking what will happen if one day this generated auto increment number is too large e.g 1000000000000000000000000000. what will happen in that case to my application?

thanks
 
Marshal
Posts: 65755
250
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What datatype is your value? Is it a LONG INT or similar?
 
Marshal
Posts: 7149
491
Mac OS X VI Editor BSD Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Check here (it's for MySQL) -> https://dev.mysql.com/doc/refman/8.0/en/out-of-range-and-overflow.html
There is also a bullet point about the max values.
 
Monica Shiralkar
Ranch Hand
Posts: 1251
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks. The datatype is int. Is it better to change it to long?. Although the max limit of long is good enough and very high value,  what would happen to application in the hypothetical scenario that this application  is used so much that even that limit crosses?
 
Sheriff
Posts: 4672
307
IntelliJ IDE Clojure Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I suggest you measure your current usage and extrapolate it out to see how long it'll take to become a problem.

How many new records are created in a day on average? You can determine this from your application history
What's the maximum value for the index data type?
What index are you currently on?

From this you can calculate how many days from now you'll likely overflow you data type. If it's next week then it's probably worth doing something about it. If it's hundreds of years in the future then don't worry about it.
 
Monica Shiralkar
Ranch Hand
Posts: 1251
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks. I will check the rate. Suppose by that rate it goes of limit in 10 years time and by that long time I may not even be working in this project, will it not be a problem for whosoever is maintaining this application at that time?
 
lowercase baba
Posts: 12765
51
Chrome Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If i recall correctly, once an int reaches the maximum value and is incremented, it rolls over to the lower bound negative value, and starts counting back up to zero.  

Note that this is something you can test with a simple program.  Just set your variable to Integer.MAX_VALUE, print it, add one, print it again.

Now, there is no way for us to tell what impact this might have on your program.  you might have "take the sqrt of the number", which would cause a problem.  You might have "use this as an index on an array - which could cause a problem. You might only be able to display 5 digits..There are many, many ways it could cause a problem, any or all of which might be in your code.
 
Bartender
Posts: 2361
102
Google Web Toolkit Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Monica Shiralkar wrote:...e.g 1000000000000000000000000000. what will happen in that case to my application?...

That is a large number indeed. I am not sure what that Id represents in your system. Can you share the field name if possible ?
To put things into perspective, there are 31536000000 milliseconds in a year (non leap). Even after a 100 years, the number of milliseconds passed would not even come close to your number. So, even if you're system is generating ids a 1000 times a second, you're not going to reach that value ever.
 
Monica Shiralkar
Ranch Hand
Posts: 1251
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The ID being auto generated is used as a unique key and compared in the program at multiple places. It is being called as RequestId.
 
salvin francis
Bartender
Posts: 2361
102
Google Web Toolkit Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As long as comparision is the only thing done by the application, I dont think you need to worry about it. Next, is your system generating a huge number of requests as Tim asked above ? Even if you generate 1 request every second for 24 hours every day, I think it will take 65+ years for your system to reach it's max for a sql int value max (2147483647). You dont need to worry about it
 
Monica Shiralkar
Ranch Hand
Posts: 1251
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks.  In that case there is no reason to think about it for near future.
 
Live ordinary life in an extraordinary way. Details embedded in this tiny ad:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!