DDC-1098: Cascading delete is broken for all relationships other than inheritance relationships, at least on MySQL 5.0

[doctrinebot]

Jira issue originally created by user dalvarez:

The console tools do not generate the proper DDL statements for the constraints declared as part of the relationship annotations.

Take this entity field declaration, e.g. :

     /****
  * @OneToMany(targetEntity="\persistentData\model\core\invoiceCreator\AnalogOrderInvoiceLineItem", mappedBy="partialInvoice", cascade={"persist", "remove", "detach"})
  */

 protected $analogOrderInvoiceLineItems;

This declaration should generate some sort of ON DELETE CASCADE constraint, or at least actually cascade the delete, in whatever way. It does not. I reviewed the generated DDL statements with

doctrine orm:schema-tool:create --dump-sql

And it clearly showed that no "ON DELETE CASCADE" was generated. Neither did Doctrine 2 perform a cascading delete otherwise.

The delete simply does not cascade.

Subsequently, I end up getting errors like:

Exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails
(invoiceCreatorDB/AnalogOrderInvoiceLineItem, CONSTRAINT AnalogOrderInvoiceLineItem*ibfk_1 FOREIGN KEY (partialInvoice*dbID) REFERENCES PartialInvoice (dbID))'

The query that caused the error is:

DELETE FROM PartialInvoice where dbID = '2';

(PartialInvoice is the entity containing the above field declaration).

It seems that the cascading delete feature is fundamentally broken.

[doctrinebot]

Comment created by @beberlei:

This is expected, cascade remove is working with in memory instances of the to be deleted objects. This way lifecycle events are triggered.

If you want to perform this operation on the databse level you have to set onDelete="CASCADE" as an option of the join column:

http://www.doctrine-project.org/docs/orm/2.0/en/reference/annotations-reference.html#annref-joincolumn

This difference should be explained in the docs on Working with Associations, 8.6 Transitive persistence / Cascade Operations i guess.

[doctrinebot]

Comment created by dalvarez:

Good idea.

I believe that it is normal to expect that declared cascade behaviours refer to persistent state, and not only to in-memory object graphs.

That declared cascade behaviours are only applicable to objects in main memory is a fundamental limitation that

1. should be prominently documented, whereas, as of now, the information it is not visible at all

2. is IMO a highly questionable design in the first place, considering the principal focus of a ORM-solution as an interface to access persistent data

The current documentation states:

"Persisting, removing, detaching and merging individual entities can become pretty cumbersome, especially when a large object graph with collections is involved. Therefore Doctrine 2 provides a mechanism for transitive persistence through cascading of these operations. Each association to another entity or a collection of entities can be configured to automatically cascade certain operations. By default, no operations are cascaded.

The following cascade options exist:

persist : Cascades persist operations to the associated entities.
remove : Cascades remove operations to the associated entities.
merge : Cascades merge operations to the associated entities.
detach : Cascades detach operations to the associated entities.
all : Cascades persist, remove, merge and detach operations to associated entities."

How are users supposed to understand, that the "remove" option is limited to in-memory object graphs, if all the docs say is "Cascades remove operations to the associated entities.". From a user standpoint, the entities are associated, and even the more so being persistent, aren't they?

[doctrinebot]

Comment created by dalvarez:

Also, we obviously have a different understanding of the word "fixed".

[doctrinebot]

Comment created by @beberlei:

Persist remove works, it fetches the association and deletes the objects, pulling them into memory.

As to the ticket, i wanted to mark it as invalid and must have missclicked.

[doctrinebot]

Comment created by dalvarez:

If it works, why is there an exception?

The situation was pretty isolated. The entity to be deleted was merge()d, and then remove()d. "remove" was declared as a cascade option.

Doctrine simply issued a "DELETE FROM \x where dbID = '1'", but did not remove the associated entity, instead violating the integrity constraint.

[doctrinebot]

Comment created by dalvarez:

I think we are referring to the same end result: After all, persistent state needs to be modified one way or the other. Otherwise it would not make any sense.

Still, the cascading delete does not work for me. It does not modify persistent state in any way.

E. g. I have a use case where the following association is declared within an entity class, say A.

/****
* @OnetoOne(targetEntity="B", inversedBy="a", cascade={"persist", "remove", "detach", "merge"})
* @joincolumn(name="b_dbID", referencedColumnName="dbID")
*/

protected $b;

Here, the cascade option "remove" is set.

Now, if I remove a persistent instance of this class, like so:

$doctrineEntityManager->merge($a);

$doctrineEntityManager->remove($a);

it will delete that particular entity instance from the database, but none of the entity instances associated with it through the association.

I made sure that also "merge" was activated for the association - just in case it matters to be able to pull the object graph into memory first.

Funnily enough, inheritance relationships declare a ON DELETE CASCADE at the database level. Just that all non-inheritance associations rely on that other in-memory mechanism, that, in the scenario above, does not work. I don't get it why the same mechanism cannot be used for both scenarios - after all its about deleting dependent database rows in both cases.

Am I doing it wrongly? How is this mechanism supposed to work? Could you please verify it does work? (For me, it does not, at least not as described above).

Thanks.

[doctrinebot]

Comment created by dalvarez:

Oh yes, I forgot to mention I am now using version 2.1.1 of Doctrine. The last post was tested using version 2.1.1.

[doctrinebot]

Comment created by dalvarez:

Updated the issue data to reflect the current environment.

[doctrinebot]

Comment created by @beberlei:

Can you give an example domain for this bug? since it seems to have to do either with merge or inheritance.

following questions:

1. does it work for you without inheritance
2. does it work by grabbing an entity using find, then remove.

[doctrinebot]

Comment created by dalvarez:

Thanks for your quick reply.

I have now tested quite a number of different scenarios, both scenarios that involve inheritance, and scenarios that do not.

Inheritance was just the matter in the original issue because the initial observation was that, at the database level, only inheritance relationships would declare an ON DELETE CASCADE constraint. Inheritance does not really have an effect on the basic problem that delete operations are not cascaded.

As the application code I use involves a lot of inheritance, and since you explicitly mentioned it, I included inheritance in the tests, too, just to see if it made a difference. I performed the tests based on two different data models - first one where the target entity class of the association would inherit from another entity class, and a second one where it does not.

In the end, inheritance did not matter. The results were the same whether the target entity inherits from a base class or not.

What did matter is the way in which the delete was performed.

I have accidentally given you wrong information about the way I actually deleted the entity instances in my last post. Since calling the remove method is the regular way I delete objects in my application, I blindly assumed it would be the same in this scenario. Unfortunately, I overlooked a special case here. In that case, the delete is performed using a DQL DELETE query.

It seems that, for deletions performed using DQL queries, the cascade options are not respected. This is only logical, since - assuming that DQL gets compiled down straight to SQL and has no side effects on the in-memory objects - there will not be any in-memory object graph, and therefore any technique based purely on in-memory objects cannot possibly work for DQL.

A solution would be to declare ON DELETE CASCADE constraints at the database level too (as already done in case of inheritance relationships) for associations that declare "remove" as a cascade option, so that the SQL resulting from the compilation of DQL DELETE queries will have the expected semantics, and maybe the onDelete annotation would be mandatory here.

Here are the details of the tests I performed:

First, here are the entity models used for the test:

Here's the first, simple, model:

<?php

namespace persistentData\model;

/****
 * @Entity
 */

class Something {

   /****
    * @Id
    * @Column(type="bigint")
    * @GeneratedValue
    */

   public $dbID;


   /****
    * @OneToOne(targetEntity="persistentData\model\SomethingElse", inversedBy="thing", cascade={"persist", "remove", "detach", "merge"})
    * @JoinColumn(name="otherThing_dbID", referencedColumnName="dbID")
    */

   public $otherThing;
}


/****
 * @Entity
 */

class SomethingElse {

   /****
    * @Id
    * @Column(type="bigint")
    * @GeneratedValue
    */

   public $dbID;

   /****
    * @OneToOne(targetEntity="persistentData\model\Something", mappedBy="otherThing")
    */

   public $thing;
}

?>

There is an entity class "Something" which refers to an entity class "SomethingElse" with a bidirectional one-to-one association
The association declares a typical set of cascade options I use, including "remove". That's it.

Here is the second model, where the target entity class of the association inherits from another entitiy class.
Except for the inheritance, this second model is functionally identical to the first:

<?php

namespace persistentData\model;

/****
 * @Entity
 */

class Something {

   /****
    * @Id
    * @Column(type="bigint")
    * @GeneratedValue
    */

   public $dbID;


   /****
    * @OneToOne(targetEntity="persistentData\model\SomethingElse", inversedBy="thing", cascade={"persist", "remove", "detach", "merge"})
    * @JoinColumn(name="otherThing_dbID", referencedColumnName="dbID")
    */

   public $otherThing;
}


/****
 * @Entity
 * @InheritanceType("JOINED")
 * @DiscriminatorColumn(name="doctrineTypeDiscriminator", type="string", length=64)
 * @DiscriminatorMap({"baseClassForSomethingElse"   = "persistentData\model\BaseClassForSomethingElse",
 *                    "somethingElse"               = "persistentData\model\SomethingElse"})
 */

abstract class BaseClassForSomethingElse {

   /****
    * @Id
    * @Column(type="bigint")
    * @GeneratedValue
    */

   public $dbID;
}



/****
 * @Entity
 */

class SomethingElse extends BaseClassForSomethingElse {

   /****
    * @OneToOne(targetEntity="persistentData\model\Something", mappedBy="otherThing")
    */

   public $thing;
}

?>

I used the Doctrine command-line tools to generate the proxy classes and update the database model. Consequently, the following tables were generated:

For the simple model:

mysql> describe Something;
<ins>-----------------</ins>------------<ins>------</ins>-----<ins>---------</ins>----------------<ins>
| Field           | Type       | Null | Key | Default | Extra          |
</ins>-----------------<ins>------------</ins>------<ins>-----</ins>---------<ins>----------------</ins>
| dbID            | bigint(20) | NO   | PRI | NULL    | auto_increment |
| otherThing_dbID | bigint(20) | YES  | UNI | NULL    |                |
<ins>-----------------</ins>------------<ins>------</ins>-----<ins>---------</ins>----------------<ins>


mysql> describe SomethingElse;
</ins>-------<ins>------------</ins>------<ins>-----</ins>---------<ins>----------------</ins>
| Field | Type       | Null | Key | Default | Extra          |
<ins>-------</ins>------------<ins>------</ins>-----<ins>---------</ins>----------------<ins>
| dbID  | bigint(20) | NO   | PRI | NULL    | auto_increment |
</ins>-------<ins>------------</ins>------<ins>-----</ins>---------<ins>----------------</ins>

And for the model involving inheritance:

mysql> describe Something;
<ins>-----------------</ins>------------<ins>------</ins>-----<ins>---------</ins>----------------<ins>
| Field           | Type       | Null | Key | Default | Extra          |
</ins>-----------------<ins>------------</ins>------<ins>-----</ins>---------<ins>----------------</ins>
| dbID            | bigint(20) | NO   | PRI | NULL    | auto_increment |
| otherThing_dbID | bigint(20) | YES  | UNI | NULL    |                |
<ins>-----------------</ins>------------<ins>------</ins>-----<ins>---------</ins>----------------<ins>


mysql> describe SomethingElse;
</ins>-------<ins>------------</ins>------<ins>-----</ins>---------<ins>-------</ins>
| Field | Type       | Null | Key | Default | Extra |
<ins>-------</ins>------------<ins>------</ins>-----<ins>---------</ins>-------<ins>
| dbID  | bigint(20) | NO   | PRI | NULL    |       |
</ins>-------<ins>------------</ins>------<ins>-----</ins>---------<ins>-------</ins>


mysql> describe BaseClassForSomethingElse;
<ins>---------------------------</ins>-------------<ins>------</ins>-----<ins>---------</ins>----------------<ins>
| Field                     | Type        | Null | Key | Default | Extra          |
</ins>---------------------------<ins>-------------</ins>------<ins>-----</ins>---------<ins>----------------</ins>
| dbID                      | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| doctrineTypeDiscriminator | varchar(64) | NO   |     | NULL    |                |
<ins>---------------------------</ins>-------------<ins>------</ins>-----<ins>---------</ins>----------------<ins>

Using doctrine, I then created some sample entity instances:

$something = new Something();


$somethingElse = new SomethingElse();

$doctrineEntityManager->persist($somethingElse);


$something->otherThing = $somethingElse;

$doctrineEntityManager->persist($something);

The database ends up with the following content:

For the simple model:

mysql> select * from Something;
</ins>------<ins>-----------------</ins>
| dbID | otherThing_dbID |
<ins>------</ins>-----------------<ins>
|    2 |               2 |
</ins>------<ins>-----------------</ins>
1 row in set (0.00 sec)


mysql> select * from SomethingElse;
<ins>------</ins>
| dbID |
<ins>------</ins>
|    2 |
<ins>------</ins>
1 row in set (0.00 sec)

And for the model involving inheritance:

mysql> select * from Something;
<ins>------</ins>-----------------<ins>
| dbID | otherThing_dbID |
</ins>------<ins>-----------------</ins>
|    1 |               1 |
<ins>------</ins>-----------------<ins>
1 row in set (0.00 sec)

mysql> select * from SomethingElse;
</ins>------<ins>
| dbID |
</ins>------<ins>
|    1 |
</ins>------<ins>
1 row in set (0.00 sec)

mysql> select * from BaseClassForSomethingElse;
</ins>------<ins>---------------------------</ins>
| dbID | doctrineTypeDiscriminator |
<ins>------</ins>---------------------------<ins>
|    1 | somethingElse             |
</ins>------<ins>---------------------------</ins>
1 row in set (0.00 sec)

We now have an entity instance of class "Something", which refers to an entity instance of class "SomethingElse". The database with the model involving inheritance is identical, except for the implementation detail that the entity instance of class "SomethingElse" inherits from an entity instance of class "BaseClassForSomethingElse".

This is the setup. Now to the program code:

This code deletes both entity instances correctly, as expected, including the base class row in case of the second data model:

$query = $doctrineEntityManager->createQuery('SELECT something
                                                FROM \persistentData\model\Something something
                                               WHERE something.dbID = 2');

$something = $query->getSingleResult();

$doctrineEntityManager->remove($something);

This code will also work correctly, having the exact same effect as the code above:

$something = $doctrineEntityManager->find('persistentData\model\Something', 2);

$doctrineEntityManager->remove($something);

However, this code will not work:

$query = $doctrineEntityManager->createQuery('DELETE
                                                FROM \persistentData\model\Something something
                                               WHERE something.dbID = 2');

$something = $query->execute();

The code deletes the entity instance of class "Something", but will not delete the dependent data object of class "SomethingElse".

It seems like the cascade option for the cascading delete is completely ignored by any delete operations performed through DQL queries (as opposed to e. g. calling the remove method on the entity manager).

In the trivial example above, the query could simply be replaced by a call to the remove method, of course. Fortunately, in the application I am developing, this is possible, so I can resort to that.
But for queries involving more complex WHERE clauses, it will not be easily possible, except maybe by first performing a SELECT query to have the WHERE clause evaluated, and then iterating over the result object-by-object to delete them.

Is this behaviour (cascade options being bypassed by DQL queries) intended? At least it is logical according to your explanation of the in-memory object-graph approach.
It would be a pain to understand though, with no clear mention of this fundamental restriction being available in the docs.

However, I found a mention of an "onDelete" option in the "transitive persistence" section of the reference documentation

"[...] To rely on the database level cascade operations for the delete operation instead, you can configure each join column with the onDelete option. See the respective mapping driver chapters for more information."

Do I have to rely on that, even if cascade="remove" is declared?

The annotation reference explains the onDelete annotation as "onDelete: Cascade Action (Database-level)". However, this is misleading, because a cascade option is understood to be one of "persist"/"remove"/"detach"/"merge"/"all" throughout the annotation reference. Only the Doctrine 1.2 docs contain a mention of "database-level cascades" (http://www.doctrine-project.org/documentation/manual/1_1/ru/defining-models:transitive-persistence:database-level-cascades). Reading the source, I assume the syntax would be onDelete="cascade", but from the docs this is not evident.

Personally, I would opt for generally using ON DELETE CASCADE constraints at the database level - either exclusively, or in addition to anything that might happen in the object domain. This is IMO the only efficient way to make the declared cascade={"remove"} constraint be generally effective.

Please clarify if the behaviour is intended, and if declaring onDelete="cascade" is mandatory to have DQL queries cascade delete operations.

Thanks.

[doctrinebot]

Comment created by dalvarez:

Wrapped the code in code blocks and the MySQL tables into noformat-blocks.

[doctrinebot]

Comment created by @beberlei:

Ah ok, using DELETE makes all the difference. This is actually the exepcted behavior. Cascade Operations are purely in-memory operations and dont trigger on DELETE statements. That is waht the join column on-delete definitions are for.

This is documented in the DQL chapter, but should probably be documented in the cascade section aswell: http://www.doctrine-project.org/docs/orm/2.0/en/reference/dql-doctrine-query-language.html#delete-queries

[doctrinebot]

Comment created by dalvarez:

Thanks for the clarification. Doctrine 2 really is one complex beast to tame.

Maybe a note in the architecture section could point out that anything involving DQL belongs to the database domain as opposed to the object domain. As DQL is toutet as an object-query-language it is quite contrary to the principle of least astonishment that cascade constraints defined at the object-domain-level are ignored. Bypassing object-domain constraints basically makes DQL a mere SQL abstraction rather than a fully-blown object-query-language aware of the object domain constraints. This is a very fundamental architectural tenet.

I will close this issue as invalid, because it does work as intended by the creators, even if the behaviour IMO constitutes a questionable design choice, being highly counter-intuitive without compensating for it by giving adequate other benefits to the user that would necessarily depend on that design choice.

[doctrinebot]

Comment created by dalvarez:

Works as intended, though maybe not as to be expected.

[doctrinebot]

Issue was closed with resolution "Invalid"