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.

The question pretty much says it all. We're talking MySQL here, version 5.1.

What I'm wondering is whether this would strictly means rows have been deleted, or if there are other possible scenarios that could cause this: weird locking or race conditions, etc. that force the table's ID counter to increment multiple times for a single insertion.

share|improve this question
2  
No, there are several other scenarios. Inserts that did not succeed (due to duplicate unique or primary key violations or other reasons), inserts where the id was explicitely defined (and id numbers were skipped) and possibly other reasons. –  ypercube Oct 10 '12 at 23:50
    
@ypercube: Makes sense... seems to me that should be an answer! Right? –  Dan Tao Oct 10 '12 at 23:57

1 Answer 1

up vote 9 down vote accepted

I assume you are asking about columns with the AUTO_INCREMENT property.

No, deleted rows is not the only reason, there are several others:

  • inserts that did not succeed (due to duplicate unique or primary key violations or other reasons)

  • inserts inside transactions that did not commit and were rolled back.

  • inserts where the id was explicitely defined (and id numbers were skipped or negative values added). In the second case, you can have MAX(id) less than COUNT(id) as a result.

  • updates of the auto-incrementing column

  • the id was set to not start from 1

  • the auto_increment step setting (system variable: auto_increment_increment) of the server is not 1 (or had been changed to something else than 1 for some period).

  • and (something that is usually neglected because the id is usually the primary key), the column was not defined as the PRIMARY or UNIQUE key. That allows duplicates and one more case where the MAX can be lower than the COUNT.

  • a variation of the above is (with MyISAM engine) an auto-incrementing column that is part of a composite primary key.

share|improve this answer
    
Great list—thanks! –  Dan Tao Oct 11 '12 at 0:11

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.