What does MySQL do when auto incrementing IDs overflow?

Question

I have a django app that uses MySQL as the database backend. It's been running for a few days now, and I'm up to ID 5000 in some tables already.

I'm concerned about what will happen when I overflow the datatype.

Is there anyway to tell the auto increment to start over at some point? My data is very volatile, so when I do overflow the ID, there is no possible way that ID 0, or anywhere near that will still be in use.

Answer 1

Depending on whether you're using an unsigned integer or not and which version of MySQL you're running, you run the rink of getting nasty negative values for the primary key or (worse) the row simply won't be inserted and will throw an error.

That said, you can easily change the size/type of the integer in MySQL using an ALTER command to preemptively stop this from happening. The "standard" size for an INT being used as a primary key is an INT(11), but the vast majority of DB applications don't need anything nearly that large. Try a MEDIUMINT.

MEDIUMINT - The signed range is –8388608 to 8388607. The unsigned range is 0 to 16777215

As compared to....

INT or INTEGER - The signed range is –2147483648 to 2147483647. The unsigned range is 0 to 4294967295

There's also the BIGINT, but to be honest you've probably got much larger scalability issues than your data types to worry about if you have a table with > 2 billion rows :)