Bug Report

Summary

In some cases, Doctrine will insert entities into the database in the wrong order, causing constraint violations for non-nullable foreign keys.

Current behavior

As a simple test case with two entities, consider an application where users can upload files: it would have a User and an UploadedFile entity.

Other than its ID, an UploadedFile has two fields:

• owner (OneToOne User, not nullable): so we can keep track of which user uploaded the file. This is not nullable because all files need to belong to a valid user.
• lastDownloadedBy (OneToOne User, nullable): because for some reason we want to keep track of who last downloaded a file. This may be null in case nobody downloaded the file yet.

A User only has one field:

• lastUploadedFile (OneToOne UploadedFile, nullable): stores the last file the user uploaded, or null if the user didn't upload a file yet

From the above mapping it can be seen that the User must be inserted before the UploadedFile can be inserted: no UploadedFile can exist without a User.

In my test case, a User and an UploadedFile are created, and all fields are populated. Then, $em->persist() is called on the UploadedFile and then the User. During the $em->flush(), Doctrine will try to insert the UploadedFile first, which will fail (because the owner field is set to NULL since the User is not inserted yet,) causing an exception.

How to reproduce

The test case mentioned above can be found in this repository. To reproduce it, clone the repository, run composer install and then run php test.php. It will create a SQLite database in the current directory, create the schema and then try to insert a User and an UploadedFile object into the database.

The SQL logs show that Doctrine is trying to INSERT the UploadedFile object first, which fails. It will then roll back the transaction and throw an exception.

Expected behavior

I would expect the following behavior:

• Insert the User object first (with the lastUploadedFile field set to NULL)
• Insert the UploadedFile object (the owner field can now be set to the ID of the User we just inserted)
• Update the User object (set lastUploadedFile to the ID of the UploadedFile we just inserted)

Additional information

It should be noted that I found many small things that, when changed, caused Doctrine to exhibit the expected behavior:

• Changing the order in which the owner and lastDownloadedBy fields in the UploadedFile entity are defined
• Changing the order of the two $em->persist()
• Changing the lastDownloadedBy field to not nullable

I believe this has something to do with the way the CommitOrderCalculator traverses the entity graph - changing the order of the fields or of the persist calls might cause it to walk the edges in a different order, resulting in a different commit order. Changing the nullability of the fields affects the weight that is assigned to the edges, which also has an effect on the commit order. However, I don't quite understand this part, so I was not able to investigate this further.

The code in the test case repository uses SQLite, but I could also reproduce this on MySQL (MariaDB 10.3.18).