25

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.

ashitaka
  • 3,928
  • 7
  • 38
  • 43

3 Answers3

63

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.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • 1
    Ok. I don't think my app will last that long => 500 years. – ashitaka Dec 12 '08 at 07:40
  • 2
    Similarly, if you only insert once a second, your app would run for 68 years with a 32-bit signed int. Save yourself 4-bytes per record and use a 32-bit int if you can. – jmucchiello Dec 12 '08 at 07:51
  • I second that. There are things that need more precaution when building a database. – Tomalak Dec 12 '08 at 08:29
  • 3
    I'd like to know what happens if you use a smaller type (e.g. INT) and it overflows. It's not very forward-thinking to assume a fixed-size integer will never overflow. – Joey Adams Oct 13 '09 at 01:46
  • 12
    @Joey: So you've downvoted me because I've answered the question that was actually being asked, instead of the one you would have asked yourself? Odd. Why not ask the question you're interested in instead? I think it's entirely reasonable to assume that a fixed-size integer will never overflow when the size is big enough. If you start assuming your app will need to run for over 500 years with a million hits per millisecond during that time, you're almost certainly going to overengineer it. – Jon Skeet Oct 13 '09 at 05:20
  • Actually the ids [don't need to start from bottom](http://stackoverflow.com/questions/1485668/how-to-set-initial-value-and-auto-increment-in-mysql), so.. see the answer below. – Pacerier Jan 16 '15 at 09:08
  • 5
    In the far future people will dig this up off the internet and blame @JonSkeet for all the failing apps because the irresponsible developers back in 2008 didn't worry about it :D – brettwhiteman Jul 29 '15 at 02:52
  • I agree with this one. For indexing with number, using very large data type will prevent Y2K-like problem or out of range error that will happen if you put smaller type. Also, 500 million years is too much to be worried about running out of data. – poring91 Aug 24 '15 at 02:07
19

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.

flamingLogos
  • 5,971
  • 4
  • 37
  • 44
2

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.

cnu
  • 36,135
  • 23
  • 65
  • 63