在PRIMARY上使用POST完整性约束违规

With Symfony 3.2 (running on Apache with PHP-FPM) we offer a REST API which allows to POST new entities via JSON payload in the request.

We don't have a high traffic on our API but in this specific case our frontend fires > 20 POST requests towards our API at the same time.

Today for the very first time (this app is running since 3 years) we received in production the following error message for one of these requests:

Integrity constraint violation: 1062 Duplicate entry '5372' for key 'PRIMARY' in /home/vcap/app/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php` (...)

Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing 'INSERT INTO our_entity (ourfield1, ourfield2, ourfield3,...) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?,...

unfortunately our logging in production stops here which is another issue

We don't do

  1. any updates here (not before and not after)
  2. we have no additional unique constraint validations
  3. we use the standard Auto-ID-Generator against a standard MariaDB database
  4. of course there was no entity existing with this ID in our database before

In the end it's a simple

// Step 1 - create and flush new Entity
$newEntity = new OurEntity();
$newEntity->setValues($valuesFromJsonPayload);
$em->persist($newEntity);
$em->flush();

// Step 2 - get ID and store it with another object

$em->refresh($newEntity);

$generatedId = $newEntity->getId();
$history = new History();
$history->setNewId($generatedId);
$em->persist($history);
$em->flush();

I don't even see why I would mention Step 2 here because the issue obviously arises with the Step 1 according to the error message.

After we asked our user to simple execute the same action again it worked like a charm.

Any ideas why that could happen?

This might be a bit off topic, but:

I would not recommend using the database auto increment for IDs.

You could, for instance use Uuid library. The ID would be a random hashed string. Your entity object would have ID and be valid immediately upon instantiation. And you would not have to save it to database just to obtain the ID.

Just yesterday I gave a similar answer. Have a look for the rest of the story and references.