MySQL AUTO_INCREMENT Counter giving some problem

i have some MySQL tables which uses primary key with auto_increment feature, it all seems while inserting the records for the first time, but when i delete any records in between it does not reset the auto_increment counter but continues to use the last id.

for example i have inserted the records with following id's

1,2,3,4,5,6

when i delete 5,6 in between and next time while i insert another row it continues from 7. whereas i want it to continue from 5. it just does not reset the auto_increment counter, i tried modifying the table by querying this command ALTER TABLE amenities AUTO_INCREMENT = 1; still it does not seems to work.

Here is the sample DDL of one of the tables i got from phpMyAdmin Export.

CREATE TABLE `amenities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQ开发者_高级运维UE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

That's not how AUTO_INCREMENT works in MySQL or any other database. AUTO_INCREMENT always goes forward. Never re accommodates itself to fill the gaps.

If you need the behavior you are describing, you need to write your own code to handle that. In your client program, in the database or wherever you feel it's better. Just keep in mind that you will need to handle transactions very well in order to avoid concurrency problems when your database is on heavy load.