mysql auto-increment skipping values

Hi all.I am using a bash script to populate a db table with some data in text file.(Using Load data infile command) Everything is imported nicely from the text file to the db table.However i have an auto increment column in the table(with auto_increment=1) and it skips some values every time the script runs.Something like this happens

# script runs for the first time
ID
1
2
3
4
5
# Second run of script
8
9
10
11
12


it is skipping 6 and 7. Multiple records are being inserted into table with each execution of the script.Is it some kind of bug in mysql which is causing it?? Any solutions for the problem.I am using mysql-server-5.5.24 and os is Ubuntu-12.04

 

Are you sure nothing is deleting records? Autoincrement won't fill in for deleted records, their numbers are still "used".

 

Hey.Thanks for the reply.My script is deleting no records.However i have found a workaround for this problem.It is something like this

# Script runs the first time

ID
1
2
3
4
5

# Five records are inserted

# i reset the auto_increment value to 1 using alter table command(through the same script). However it sets the auto_increment value to (max_id+1 which is 6). So when the next insert takes place it is like
.
.
.
6
7
8
9
10

It fixes my problem.However i have to run an extra query in my script.If you insert a large number of records using LOAD DATA INFILE,i think some auto_increment values are skipped.May be its a bug in mysql.

 

You might also want to read http://forums.mysql.com/read.php?10,437197 for a couple of other "fixes".