MySQL Weirdism...?

[whit3fir3]

I have a problem with a MySQL database that started about 12 hours ago that I can not figure out.  The short version of the problem is that in a table I have an AUTO_INCREMENT column and for some reason the values in this column went from something in the 12,000 range to something in the 120,000 range.

 

I know everyone's first thought is going to be that the AUTO_INCREMENT was manually set for the table.  I can assure that this is not the case as I am the only DBA that has access to this DB and I have gone through my ~/.mysql_history file just to ensure that I did not fat finger something.  Now when I do a select on the table and ORDER BY the AUTO_INCREMENT column DESC I see no records between the ones in the 120,000 range and the ones in the 12,000 range.  However I do notice that not every record before the 12,000 one is sequential.  I had noticed this before and thought it was due to me inserting records and deleting them as part of testing, but that does not seem to be the case any longer.

 

I guess my question is in MySQL what else besides an INSERT or a manual setting of the AUTO_INCREMENT value would case the AUTO_INCREMENT value to increase?  Does a record UPDATE cause the  AUTO_INCREMENT value to increase even if a new record is not added to the table??

 

Thanks,

 

whit3fir3

[mikosiko]

check the values for your system variables

auto_increment_increment and auto_increment_offset  maybe is something there

 

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

 

 

[fenway]

You may have manually created a UID with an extra zero -- auto-increment won't fill spaces.

[Mchl]

Also in InnoDB tables, INSERTS in transaction that are rolled back do increase AUTO_INCREMENT value.