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

database auto-increment integer-overflow lastinsertid
Jonas picture Jonas · Apr 11, 2010 · Viewed 45.5k times · Source

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

outis picture outis · Apr 11, 2010

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.

Related questions

How to retrieve the last autoincremented ID from a SQLite table?

I have a table Messages with columns ID (primary key, autoincrement) and Content (text). I have a table Users with columns username (primary key, text) and Hash. A message is sent by one Sender (user) to many recipients (user) and …

Read More
auto increment ID in H2 database

Is there a way to have an auto_incrementing BIGINT ID for a table. It can be defined like so id bigint auto_increment but that has no effect (it does not increment automatically). I would like to insert all …

Read More
mysql Failed to read auto-increment value from storage engine

I am having mysql table with one id field as auto-increment . When I insert values to the table am getting error as 1467 - Failed to read auto-increment value from storage engine Also the show table status shows me that the …

Read More