What happens when auto_increment on integer column reaches the max_value in databases?

Question

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.

Answer 1

You will know when it's going to overflow by looking at the largest ID. You should change it well before any exception even comes close to being thrown.

In fact, you should design with a large enough datatype to begin with. Your database performance is not going to suffer even if you use a 64 bit ID from the beginning.

Answer 2

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.