Sunday, May 24, 2009

How reset the auto_increment propertie in mysql table.

Recently encountered on a 'odd' behaviour of mySQL table with primary key that defined as auto-increment, in a first look the table had only 5 records, which was far away from limit(256), then I've remebered that the table was used by junit runs for inserting/deleting records and apparently the limit was reached, I think that most expected behavior would be to generate an id for primary key that most close to maximum value of primary key.
Anyway in order to reset the auto_increment, in a situation where some of the most recently added rows were deleted, use:

ALTER TABLE theStuckedTable AUTO_INCREMENT=1234

No comments: