Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have an active and important InnoDB table with about 8M rows. We just noticed that for several months, the primary key value (a BIGINT) has been jumping by hundreds at a time.

I turned on query logging while watching for a jump and found no deletions from the table during the time. (We don't often delete historical data, anyway.)

MySQL version: mysql Ver 14.14 Distrib 5.1.66, for redhat-linux-gnu (i386) using readline 5.1

OS: Centos 6.3

Any ideas how or why this could happen?
Any other information you might need to assess the situation?

Thanks!

share|improve this question

1 Answer 1

up vote 2 down vote accepted

This would be expected behavior in a few scenarios that come to mind:

  • If you you ROLLBACK transactions where you have inserted records into the table, the auto_increment values transiently assigned to those rows you ultimately did not COMMIT the insertion of will not be reused.

  • If you are using INSERT IGNORE and you have another unique key (in addition to the primary key). For every row you attempt to INSERT without explicitly specifying the primary key value in the VALUES, the auto_increment gets incremented, and if that row happens to be a duplicate of an existing unique key value, and therefore discarded because of INSERT IGNORE, the auto_increment counter will still have been incremented and those values will never be used.

  • Similarly, if you are using INSERT ... ON DUPLICATE KEY UPDATE and you have another unique key, the same behavior is observed. For those rows where a duplicate key value is found and the query is performed as an update, the auto_increment value still increments if the primary key column is not part of the original INSERT and the duplicate key detection results from a conflict against a unique key on another column.

share|improve this answer
    
Thank you! While we don't do any of those 3, exactly, we are trying to add things to the table that may be excluded by a unique key based on 4 fields and defined in a table index. So, the 2nd point stirred up the realization of what was going on. –  marklark Feb 28 '13 at 23:24
    
+1 for thorough breakdown !!! –  RolandoMySQLDBA Mar 1 '13 at 2:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.