CODEWITHSUNDEEP
databasemysql
madeye
madeye

Reputation: 1406

MySQL skipped a row when inserting new record

The problem was that mysql was running high on CPU on our server and that lasted for about 15 minutes. In that time some of the data that were coming to server were not being inserted into db but transaction committed.

After restarting mysql problem was resolved.

So this means that even though transaction committed there were no new rows in the table. The evidence for this is auto-incremented primary key that should looked like this:

100
101
102

But currently is like this:

100
102

So the record 101 was missing and its skipped.

Now i need to find some kind of log for that record if that exists.

I tried in mysqldump log but there are no records there. Record 101 is still missing.

I need to find a way to recreate that transaction and do it again.

Has anyone had any similar problems?

Code looks basically like this:

$db->autocommit(FALSE);

$sql = "INSERT INTO t (amount, datetime) VALUES ($amount, '$datetime')";
$db->query($sql);

$id = $db->insert_id;
$sql = "INSERT INTO tr (id, log) VALUES ($id, '$log')";
$db->query($sql);

$db->autocommit(TRUE);

Upvotes: 0

Views: 565

Answers (1)

Norbert
Norbert

Reputation: 6084

The transaction was most likely aborted after the sequence had incremented already. This is not a problem from the database perspective: An auto incremented column is not required to have all ids sequentially in there. From a code/user perspective this should also not be a problem. If your code requires continuous ids (lets say for auditing purposes), you will have to implement a system for that yourself. If you do not require that, then just run the transaction again. That will then create id 103 (in your case).

Upvotes: 1

Related Questions