1

i have a table called ptb_users like this:

id (auto increment)  |  name   | email
1                       john     john@email.com
2                       kate     kate@gmail.com
29                      harry    harry@email.com
10016                   lisa     lisa@mail.com

id is an auto increment value and its so far gone upto id's 1-29, but now for some reason its skipped 30-10015 and its starting auto increment from 10016?

does anyone know why this is?

thanks

James Cart
  • 11
  • 2
  • You can change your autoincrement: http://stackoverflow.com/questions/1485668/how-to-set-initial-value-auto-increment-in-mysql – fedorqui Mar 14 '13 at 20:47

1 Answers1

1

Usually this is because of an aborted transaction that allocated ids for insertion but then aborted. In general auto increment is only guaranteed to produce unique ids and should not be relied upon for producing a predictable sequence of numbers.

Steve
  • 1,215
  • 6
  • 11
  • is there anyway i can reset the auto increment? – James Cart Mar 14 '13 at 20:09
  • @JamesCart: Why do you care? – eggyal Mar 14 '13 at 20:10
  • In MySQL as far as I'm aware you can delete the table and re-create it. The auto-increment counter is tied to the table itself, allowing people to manipulate it could lead to duplicate ids which is very bad. (or maybe you can according to another answer, it's still a very bad thing to do). – Steve Mar 14 '13 at 20:10
  • @Steve: That's not correct. Duplicate IDs are impossible under all circumstances (the `PRIMARY KEY` constraint ensures that). One can update the next `AUTO_INCREMENT` value with `ALTER TABLE`, but it's generally a bad idea. – eggyal Mar 14 '13 at 20:14
  • @eggyal I assume if the AUTO_INCREMENT would cause a conflict then inserts will fail? – Steve Mar 14 '13 at 20:34