Is there any way to tell MySQL not to throw when an auto_increment field overflows?

Question

From this question, MySQL throws when an auto_increment field overflows. However, in my case I want to auto-purge old data. Is there any way for me to tell MySQL not to throw when the field overflows? I have declared a PK on a auto_increment field.

Add an insert trigger that will truncate the table once it reach the maximum value-1 (to avoid the overflow) perhaps? — Martheen, Apr 10 '20 at 23:54
Bigint is quite big especially unsigned The unsigned range is 0 to 18446744073709551615 you will have to move your server quite a lot much faster than you reach the end. — nbk, Apr 11 '20 at 00:01
@nbk my design is to use a 4 byte int and auto-purge old records. — morpheus, Apr 11 '20 at 00:05
Change it to bigint. even when you implement a trigger you have to lock the table reassign new ids(from 0) and set the autpoincrement field, and i believe most experts will tell you it is quite a challenge and not recommended but doable. — nbk, Apr 11 '20 at 00:11

score 0 · Answer 1 · answered Apr 11 '20 at 00:58

Even if you purge old records, the auto-increment will never recycle old id values. It will only increase. Even if you try to set the auto-increment back to the start:

 ALTER TABLE mytable AUTO_INCREMENT=1;

You will see if you SHOW CREATE TABLE mytable that it ignores your request to set the value to 1, and it bumps it up to MAX(id)+1.

Unless you fully empty the table with TRUNCATE TABLE, you cannot benefit from purging old records.

Just use a BIGINT if you are concerned about running out of INT values.

Is there any way to tell MySQL not to throw when an auto_increment field overflows?

1 Answers1