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.

Regelate

You end up with a 3 hour downtime while you run ALTER TABLE to change the data type :-) slashdot.org/articles/06/11/09/1534204.shtml – Grogram

Also see this question: stackoverflow.com/questions/253616 – Grogram

Answer 1

Depending on whether you're using an unsigned integer or not and which version of MySQL you're running, you run the risk 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 :)

Realpolitik

Is the row not being inserted with an erro really "worse"? I'd rather have the system fail fast than roll over and potentially (one day) overwrite ID values. – Unexpected

A fair point - it depends on how crucial that data is .. if it's truly volatile as the OP claims, the rollover probably is the lesser of two evils. Either way, the issue is pretty easily avoided. – Realpolitik

The OP asked about a volatile table (not especially large). Constant churn in a table could exhaust the ID column range. Just takes longer if a size like BIGINT is used. Is the right solution to use an unsigned column type and let the rollover happen? Does MySQL correctly check whether the next integer is actually available? – Batista

Answer 2

You can also use a UUID. Characters would give you a much such a large range you practically don't need to worry about collisions.

Supposedly the performance would degrade if you need to perform joins using UUIDs due to string comparisons.

Sunshinesunspot