What happens when an auto-incrementing column runs out ?

Question

Consider a simple table with an auto-increment column like this:

CREATE TABLE foo 
(
  `fooid` bigint unsigned NOT NULL auto_increment,
   ....snipped.... other columns
  PRIMARY KEY (`fooid`)
) 
ENGINE=InnoDB AUTO_INCREMENT=10

How does one redesign this so that we don't hit the max of the bigint datatype ? The unsigned range is 0 to 18446744073709551615. I don't know how long it will take to reach 18446744073709551615, but like the Y2K problem, I want to be ready for it.

Answer 1

Suppose you insert one row every millisecond.

18446744073709551615 millseconds = 18446744073709552 seconds = 307445734561826 minutes = 5124095576030 hours = 213503982335 days = 584942417 years

So it's not really like the Y2K problem

You could insert a million rows per millisecond and still be okay for over 500 years.

In other words: don't worry about it.

Answer 2

Depending on which SQL mode you are using, MySQL does one of two things when the value of an AUTO_INCREMENT numeric column grows out of range. You get an error in either case, but for different reasons.

In strict mode MySQL rejects the out of range value, throws an invalid value error, and the INSERT fails. In the default non-strict mode MySQL reduces the value to the highest allowed for the data type, and performs the INSERT. But the INSERT fails because the AUTO_INCREMENT attribute has caused all of the possible values to be used already, and you get this error (unsigned SMALLINT example):

MySQL said:

#1062 - Duplicate entry '65535' for key 1

For the BIGINT example here, replace the "65535" with 18 quintillion, although it's not likely that this error has ever occurred on a production database.

But with TINYINTs and SMALLINTs it can happen very easily if you underestimate the possible key values (number of INSERTs) over the lifetime of an application. Imagine you are making changes to your code and testing that your data is being inserted correctly. Suddenly your application quits working with the error above. You roll back the changes to known good code, but the error won't go away...very frustrating.

Answer 3

Don't know about MySQL, but in case of Postgresql, you can specify whether the sequence is a CYCLE/NO CYCLE. If created with a CYCLE option, it will come back again to 1 (or the minvalue) and would throw the error for duplicate key.