MySQL Auto-increment fields resets by itself

Solution 1:

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

Solution 2:

We've experienced this issue and have discovered that when optimize table was run on an empty table, the auto-increment value was also reset. See this MySQL bug report.

As a workaround, you can do:

ALTER TABLE a AUTO_INCREMENT=3 ENGINE=innoDB;

Instead of OPTIMIZE TABLE.

It seems this is what MySQL does internally (without setting the Auto increment value, obviously)

---

Solution 3:

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.