Symfony 2 + Doctrine: How to supress SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry

Question

I've looked into a couple of posts, but couldn't find a working solution.

My question is beyond simple:

I have an entity with say id, url and title. The URL should be unique (in MySQL PDO). I've managed to create both the entity and schema with no problems. Now when I walk some records I call persist() on each, and finaly a flush(). The problem is that when I try to insert duplicate entries for the URL it gives me an exception. How to supress it?

When a duplicate entry is being inserted it should just skip it and insert the rest. No need for events, ON UPDATE statements, triggers and all that fancy stuff.

I've tried catching any exceptions thrown by persist or flush(), but can't really seem to do it correctly.

Any ideas are welcome, thank you!

EDIT: Found my solution in here: Symfony2 Controller won't catch exception

Solution 1

try {
    $em->flush()
} catch (\PDOException $e) {
    // ... Error on database call
}

A better approach would be to specify a validation contstraint to avoid having to deal with this exception. In yaml (taken from the symfony docs)

Acme\SomeBundle\Entity\Item:
    constraints:
        - Symfony\Bridge\Doctrine\Validator\Constraints\UniqueEntity: url

Solution 2

In Symfony 2.1+, catch it using \Doctrine\DBAL\DBALException instead of \PDOException.

try {
    ...
} catch (\Doctrine\DBAL\DBALException $e) {
    // ... Error on database call
}

Solution 3

Be aware that there is a PDOException thrown in Symfony 2.1 if you are e.g. deleting an entry with parent-relations. But in order to catch it you will use the statement suggested by ihsan

    try {
        $em = $this->getDoctrine()->getEntityManager();
        $em->remove($entity);
        $em->flush();
    } catch(\Doctrine\DBAL\DBALException $e)
    {
        // ...
    }

Comments

[Tony Bogdanov]

I am not really sure how and where to write that (I'm new to Symfony), but from the Docs I got the annotation @ORM\Column(.., unique=true), which still gives the SQL error. The interesting part is that the error comes from MySQL, not Doctrine. Doctrine has no way of knowing if the value exists without doing an individual check in the database, which is (as far as performance is concerned) unacceptable.

[Pete Mitchell]

The error comes from MySQL because the unique annotation specifies a unique index on that column. You should be validating that your data is correct before attempting to insert it into the database. Catching (multiple) exceptions is a costly operation. It makes a lot more sense and is a good practice to validate your data before insert.

[Tony Bogdanov]

I understand, but how can I validate that a field in an entity is unique in the database without making a costly db check?

[Pete Mitchell]

You can't...How would it be possible to check if something is unique in your database without looking at the database first? One strategy you could employ - is to cache your url slugs in redis/memcache/apc and validate them against that store. Although it is obviously not a reliable representation of the data that may be in your database...

[Pete Mitchell]

Also why are you worried about the performance of inserting new items. Are they going to be updated regularly? Is the read performance of url slugs not far more important? Is this premature optimisation?

[Tony Bogdanov]

Well, it is going to be used for inserting RSS news feeds, where the source URL is the unique part. This is going to be run with cron every 20 minutes so checks are quite costly. Slugs on the other hand are not unique and are generated based on the title just for SEO purposes, the actual content is going to be fetched by an integer Id. I am already caching RSS entries that where once processed, so I guess I could afford the db checks, but do I really need them in my case?

[Yann Sagon]

don't forget to put a back slash before Doctrine or you will catch nothing!