16

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!

Hooligancat
  • 263
  • 1
  • 2
  • 6

6 Answers6

35

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

Gremio
  • 514
  • 4
  • 5
  • Not bad for a first post, dude. +1. – ceejayoz Nov 15 '12 at 21:20
  • Sweet bit of knowledge there Gremio. Thanks!! I had completely put this off and archived the issue internally as something to review at a later date, but coming back around to it your solution works a charm! – Hooligancat Jan 21 '13 at 19:56
3

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)

Kenny Rasschaert
  • 8,925
  • 3
  • 41
  • 58
Rod
  • 31
  • 1
2

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.

dsolimano
  • 1,290
  • 2
  • 14
  • 26
  • I didn't think we were using TRUNCATE, but we had to check to make sure. Even went so far as to verify down to the PHP driver level to make sure. BUt we weren't. Appreciate the possible solution though. – Hooligancat Feb 03 '11 at 15:20
1

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.

IcarusNM
  • 111
  • 2
0

ALTER TABLE table_name ENGINE=MyISAM

Workes for me. Our table is always kept very small, so no need for InnoDB.

QuickFix
  • 1
0

InnoDB doesn't store auto increment value on disk thus forgets it when the MySQL server is shut down. When the MySQL is started again, the InnoDB engine restores the auto increment value this way: SELECT (MAX(id) + 1) AS auto_increment FROM table. This is a bug that is fixed in MySQL version 8.0.

Change the table engine to solve the problem:

ALTER TABLE table ENGINE = MyISAM

Or update the MySQL server to version 8.0 when it is released.

Finesse
  • 131
  • 3