Menu
I am implementing a database application and I will use both JavaDB and MySQL as database. I have an ID column in my tables that has integer as type and I use the databases auto_increment-function for the value.
But what happens when I get more than 2 (or 4) billion posts and integer is not enough? Is the integer overflowed and continues or is an exception thrown that I can handle?
Yes, I could change to long as datatype, but how do I check when that is needed? And I think there is problem with getting the last_inserted_id()-functions if I use long as datatype for the ID-column.
956
When the AUTO_INCREMENT column reaches the upper limit of data type then the subsequent effort to generate the sequence number fails. That is why it is advised to use a large enough integer data type for the AUTO_INCREMENT column to hold the maximum sequence value required by us.
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
It could have multiple causes: Check if the auto_increment value on the table itself, has the next highest value. Mind that if you have transactions where you INSERT a row and rollback the transaction, that auto_increment value will be gone/skipped.
Auto increment attribute when specified on a column with a numeric data types, generates numbers sequentially whenever a new row is added into the database. The Auto increment is commonly used to generate primary keys. The defined data type on the Auto increment should be large enough to accommodate many records.
Jim Martin's comment from §3.6.9. "Using AUTO_INCREMENT" of the MySQL documentation:
Just in case there's any question, the AUTO_INCREMENT field /DOES NOT WRAP/. Once you hit the limit for the field size, INSERTs generate an error. (As per Jeremy Cole)
A quick test with MySQL 5.1.45 results in an error of:
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine
You could test for that error on insert and take appropriate action.
170
Just to calm the nerves, consider this:
Suppose you have a database that inserts a new value for every time a user executes some sort of transaction on your website.
With a 64 bit integer as an ID then this is the condition for overflow: With a world population of 6 billion then if every human on earth executes a transaction once per second every day and every year (without rest) it would take more than 80 years for your id to wrap around.
Ie, only google needs to vaguely consider this problem occasionally during a coffee break.
31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
