DDC-2524: Wrong commit order with cascade remove and double association

[doctrinebot]

Jira issue originally created by user mnapoli:

We have stumbled upon a bug in a situation where a class A has the following associations to a class B:

• A has one B (oneToOne unidirectional)
• A has many B (oneToMany bidirectional)

Associations are with cascade remove.

We will submit a PR soon with a failing test case.

The failure is a MySQL foreign key violation exception when removing A (removals for B are executed after removals for A).

[doctrinebot]

Comment created by valentin:

Here a link to the pull request #707

[doctrinebot]

Comment created by mnapoli:

The tests on Travis are failing as expected.

https://travis-ci.org/doctrine/doctrine2/builds/8382962

Here is the list of the queries executed for MySQL:

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`doctrine*tests`.`CascadeRemoveOrderEntityG`, CONSTRAINT `FK_23681E8F99938CE5` FOREIGN KEY (`ownerO*id`) REFERENCES `CascadeRemoveOrderEntityO` (`id`))

With queries:

13. SQL: 'DELETE FROM CascadeRemoveOrderEntityO WHERE id = ?' Params: '1'
12. SQL: '"START TRANSACTION"' Params: 
11. SQL: 'SELECT t0.id AS id1, t0.ownerO*id AS ownerO_id2 FROM CascadeRemoveOrderEntityG t0 WHERE t0.ownerO*id = ?' Params: '1'
10. SQL: 'SELECT t0.id AS id1, t0.oneToOneG*id AS oneToOneG*id2 FROM CascadeRemoveOrderEntityO t0 WHERE t0.id = ?' Params: '1'
9. SQL: '"COMMIT"' Params: 
8. SQL: 'INSERT INTO CascadeRemoveOrderEntityG (ownerO_id) VALUES (?)' Params: '1'
7. SQL: 'INSERT INTO CascadeRemoveOrderEntityO (oneToOneG_id) VALUES (?)' Params: ''
6. SQL: '"START TRANSACTION"' Params: 

As you can see, the latest query causes a foreign key constraint violation (wrong order with the next, non-executed query).

[doctrinebot]

Comment created by mnapoli:

On the sqlite tests we can see better the order the queries are executed:

14. SQL: 'DELETE FROM CascadeRemoveOrderEntityG WHERE id = ?' Params: '1'
13. SQL: 'DELETE FROM CascadeRemoveOrderEntityO WHERE id = ?' Params: '1'
12. SQL: '"START TRANSACTION"' Params: 
11. SQL: 'SELECT t0.id AS id1, t0.ownerO*id AS ownerO_id2 FROM CascadeRemoveOrderEntityG t0 WHERE t0.ownerO*id = ?' Params: '1'
10. SQL: 'SELECT t0.id AS id1, t0.oneToOneG*id AS oneToOneG*id2 FROM CascadeRemoveOrderEntityO t0 WHERE t0.id = ?' Params: '1'
9. SQL: '"COMMIT"' Params: 
8. SQL: 'INSERT INTO CascadeRemoveOrderEntityG (ownerO_id) VALUES (?)' Params: '1'
7. SQL: 'INSERT INTO CascadeRemoveOrderEntityO (oneToOneG_id) VALUES (?)' Params: ''
6. SQL: '"START TRANSACTION"' Params: 

13 and 14 are in the wrong order.

[doctrinebot]

Comment created by @guilhermeblanco:

This situation is not supported and cannot be resolved within current Doctrine code.
You created a circular dependency between the entities A and B. It happened because A contains one B (oneToOne) and because B contains a pointer to A as part of oneToMany association.
That way, you'll always have a foreign key constraint issue from RDBMS, no matter which entity you try to remove first.

Because of that, I'mm marking this ticket as "can't fix".

[doctrinebot]

Comment created by mnapoli:

[~guilhermeblanco] I see what you mean, but the case we submitted is with a nullable foreign key. So the operation is permitted by the RDBMS.

A has one B (nullable oneToOne), and B has a pointer to A (manyToOne, not nullable).

As I said for the query log, B should be removed first, which is not the case (see above, line 13 and 14 should be inversed).

So this is fixable on the Doctrine side if I'm not mistaken.

[doctrinebot]

Comment created by @beberlei:

[~matthieu] is this fixed with your DDC-2775 PR?

[doctrinebot]

Comment created by mnapoli:

Just for clarity (I answered this question in the pull request): no this is not fixed (see #707 (comment)).

[doctrinebot]

Comment created by benjamin:

Just encountered what I believe to be the same bug, without any kind of circular dependency:

class A {
    /****
     * @ORM\OneToMany(targetEntity="B", mappedBy="a", indexBy="x", cascade={"all"}, orphanRemoval=true)
     */
    private $b;

    ...
}

class B {
    /*
     * @ORM\ManyToOne(targetEntity="A", inversedBy="b")
     * @ORM\JoinColumn(name="aid", referencedColumnName="id", nullable=false, onDelete="CASCADE")
     */
    private $a;

    ...
}

The following operations in A:

$this->b->clear();
$this->b->add(new B());

Result in the following SQL commands:

INSERT INTO B ...
DELETE FROM B ...

This is always the wrong commit order, and is doomed to fail when you have unique constraints in the B table.

[doctrinebot]

Comment created by makm:

Another situation without circular dependency:

class Entity
{
    /****
     * @ORM\OneToMany(targetEntity="Picture", mappedBy="entity", fetch="EXTRA_LAZY", cascade={"all"}, orphanRemoval=true)
     * @ORM\OrderBy({"position" = "ASC"})
     */
    protected $pictures = array();
} 

class Pictures {
    /****
     * @ORM\Column(type="string", length=1024, unique = true)
     */
    protected $file;
}

$hotel->setPictures($picturesArray);
$em->flush();
$hotel->getPictures()->clear();
$hotel->setPictures($somePictureUpdatedArray);
$em->flush();
Unique violation: 7 ERROR:  duplicate key value violates unique constraint "uniq_8f7c2fc08c9f3610"

Why we can't invoke entity deletions method (executeDeletions, UnitOfWork#commit) before executeInserts?
https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/UnitOfWork.php#L385-L388
There is another way?

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-707] was labeled:
#707

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-707] was closed:
#707

[doctrinebot]

Issue was closed with resolution "Fixed"