MySQL Auto-increment fields resets by itself

autoincrementMySQL

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!

Best Answer

The auto-increment counter is stored only in main memory, not on disk.

http://dev.mysql.com/doc/refman/4.1/en/innodb-auto-increment-handling.html

Because of this when the service (or server) restarts the following will happen:

After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement: SELECT MAX(ai_col) FROM t FOR UPDATE;

InnoDB increments by one the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. If the table is empty, InnoDB uses the value 1.

So in plain English, after the MySQL service starts it has no idea what the auto-increment value for your table should be. So when you first insert a row, it finds the max value of the field that uses auto-increment, adds 1 to this value, and uses the resulting value. If there are no rows, it will start at 1.

This was a problem for us, as we were using the table and mysql's auto-increment feature to neatly manage IDs in a multi-threaded environment where users were getting re-directed to a third-party payment site. So we had to make sure the ID the third party got and sent back to us was unique and would stay that way (and of course there's the possibility the user would cancel the transaction after they had been redirected).

So we were creating a row, obtaining the generated auto-increment value, deleting the row to keep the table clean, and forwarding the value to the payment site. What we ended up doing to to fix the issue of the way InnoDB handles AI values was the following:

$query = "INSERT INTO transactions_counter () VALUES ();";
mysql_query($query);
$transactionId = mysql_insert_id();
$previousId = $transactionId - 1;
$query = "DELETE FROM transactions_counter WHERE transactionId='$previousId';";
mysql_query($query);

This always keeps the latest transactionId generated as a row in the table, without unnecessarily blowing up the table.

Hope that helps anyone else that might run into this.

Edit (2018-04-18):

As Finesse mentioned below it appears the behavior of this has been modified in MySQL 8.0+.

https://dev.mysql.com/worklog/task/?id=6204

The wording in that worklog is faulty at best, however it appears InnoDB in those newer versions now support persistent autoinc values across reboots.

-Gremio

Related Topic