Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2524

Wrong commit order with cascade remove and double association

    Details

    • Type: Bug Bug
    • Status: Reopened
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Security Level: All
    • Labels:
      None

      Description

      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).

        Activity

        Hide
        Valentin Claras added a comment -

        Here a link to the pull request https://github.com/doctrine/doctrine2/pull/707

        Show
        Valentin Claras added a comment - Here a link to the pull request https://github.com/doctrine/doctrine2/pull/707
        Hide
        Matthieu Napoli added a comment - - edited

        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).

        Show
        Matthieu Napoli added a comment - - edited 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).
        Hide
        Matthieu Napoli added a comment -

        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.

        Show
        Matthieu Napoli added a comment - 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.
        Hide
        Guilherme Blanco added a comment -

        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".

        Show
        Guilherme Blanco added a comment - 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".
        Hide
        Matthieu Napoli added a comment -

        Guilherme Blanco 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.

        Show
        Matthieu Napoli added a comment - Guilherme Blanco 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.
        Hide
        Benjamin Eberlei added a comment -

        Matthieu Napoli is this fixed with your DDC-2775 PR?

        Show
        Benjamin Eberlei added a comment - Matthieu Napoli is this fixed with your DDC-2775 PR?
        Hide
        Matthieu Napoli added a comment -

        Just for clarity (I answered this question in the pull request): no this is not fixed (see https://github.com/doctrine/doctrine2/pull/707#issuecomment-31564035).

        Show
        Matthieu Napoli added a comment - Just for clarity (I answered this question in the pull request): no this is not fixed (see https://github.com/doctrine/doctrine2/pull/707#issuecomment-31564035 ).

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Matthieu Napoli
          • Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated: