Sometimes when using MySQL tables of the InnoDB engine variant, the auto increment property of the primary key does not lock properly and therefore creates holes in the system when INSERT instructions are run.
This is how I solved the problem in my projects. I run separate SELECT query that determines the last record number in the system, increments it by 1 and uses the number as the next key field value.
/**
* function that retrieves the last index in a table
* $tbl is the key
* $tblid is the key field
* $conn is the database connection resource
*/
function rec_get_next($tbl, $tblid, $conn)
{
$sql = get_read_sql($tbl)
// order the table by the key field
.' ORDER BY `'.$tblid.'`'
// in decending order
.' DESC'
// select only the item at the top: i.e. the last item
.' LIMIT 1'
;
$rs = mysql_query($sql, $conn) or die(mysql_error());
$row = mysql_fetch_assoc($rs);
$total_rows = mysql_num_rows($rs);
// increment the value and return
return intval($row[$tblid] + 1);
}
However a better way of handling this problem without running separate queries, can be found in the link below
https://www.percona.com/blog/2011/11/29/avoiding-auto-increment-holes-on-innodb-with-insert-ignore/