MySQL Auto-increment fields resets by itself

Question

We have a MySQL table that has an auto-incrementing field set as an INT (11). This table pretty much stores a list of jobs that are running in an application. At any given moment during the lifetime of the application, the table could well contain thousands of entries or be completely empty (i.e. everything has finished).

The field is not foreign-keyed to anything else.

The auto-increment seems to randomly reset itself to zero, although we have never actually been able to trap the reset.

The problem becomes evident because we see the auto-increment field get up to, say, 600,000 records or so and then a while later the auto-increment field seems to be running in the low 1000's.

It is almost as if the auto-increment resets itself if the table is empty.

Is this possible and if it is, how do I turn it off or change the manner in which it resets?

If it isn't, does anyone have an explanation of why it might be doing this?

Thanks!

Answer 1

Just a shot in the dark - if the application is using a TRUNCATE TABLE to empty out the table when it is done processing, that will reset the auto-increment field. Here is a brief discussion on the question. Though that link mentions that InnoDB doesn't reset auto_increments on a trunc, that was reported as a bug and fixed a few years ago.

Assuming my guess is right, you could change from truncating to deleting to fix the problem.

Answer 2

Only an explicit reset of that value, or a drop/recreate of that field, or other similar violent operation should ever reset an auto_increment counter. (The TRUNCATE was a really good theory.) It seems impossible you're suddenly wrapping a 32-bit INT when the last value you witness is only 600k. It definitely shouldn't reset just because the table empties. You either have a mysql bug or something in your PHP code. Or the guy in the cubicle next door is playing a trick on you.

You could debug by turning on the binary log, as it will contain statements like this throughout:

SET INSERT_ID=3747670/*!*/;

Then at least you can see every detail of what's happening to that table, including right before the counter resets.