Redundant UPDATE queries from the second flush() in a row #8217
Description
Bug Report
Simple bi-directional OneToMany relation with auto-increment primary key makes UnitOfWork detect non-existing changes.
When performing two flush() calls in a row, subsequent flush results in redundant UPDATE queries.
| Q | A |
|---|---|
| BC Break | not sure |
| Version | 2.10.2 |
Summary
Let's say we have a OneToMany relation between TestCollection and TestItem entities. Additionally, TestCollection has a single-column auto-increment primary key (IDENTITY GeneratedValue). We also have cascade persist and remove operations for items.
In a simplest scenario of creating a new collection with a single item
$collection = new TestCollection();
$item = new TestItem($collection, 100);
$item->value = 'TEST';
$collection->items->set(100, $item);
$entity_manager->persist($collection);
$entity_manager->flush();all seems fine, but if we add an additional flush() just after the first one, it results in the additional UPDATE like
UPDATE test_item SET collection_nid = 5 WHERE collection_nid = 5 AND item_nid = 100Current behavior
Subsequent flush() operation results in a redundant UPDATE query. Internally, the UnitOfWork seems to detect a difference between $originalData['collection'] which consist of an int(5) (!) and $actualData['collection'] which is a proper entity.
How to reproduce
TestItem:
<?php
use Doctrine\ORM\Mapping as ORM;
/**
* @ORM\Entity
* @ORM\Table(name="test_item")
*/
class TestItem
{
/**
* @ORM\Id
* @ORM\ManyToOne(targetEntity="TestCollection", inversedBy="items")
* @ORM\JoinColumn(name="collection_nid", referencedColumnName="collection_nid")
* @var TestCollection
*/
public $collection;
/**
* @ORM\Id
* @ORM\Column(type="integer", options={"unsigned": true})
* @var int
*/
public $item_nid;
/**
* @ORM\Column(type="string", nullable=false)
* @var string
*/
public $value = '';
public function __construct(TestCollection $collection, int $item_nid)
{
$this->collection = $collection;
$this->item_nid = $item_nid;
}
}TestCollection:
<?php
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;
/**
* @ORM\Table(name="test_collection")
* @ORM\Entity
*/
class TestCollection
{
/**
* @var int|null
*
* @ORM\Column(name="collection_nid", type="integer", nullable=false, options={"unsigned": true})
* @ORM\Id
* @ORM\GeneratedValue(strategy="IDENTITY")
*/
public $collection_nid;
/**
* @ORM\OneToMany(targetEntity="TestItem", indexBy="item_nid",
* mappedBy="collection", cascade={"persist", "remove"}, orphanRemoval=true)
* @var Collection
*/
public $items;
public function __construct()
{
$this->items = new ArrayCollection();
}
}Database structure with an additional trigger to detect redundant UPDATE:
CREATE TABLE test_collection (
collection_nid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE test_item (
collection_nid INT UNSIGNED NOT NULL,
item_nid INT UNSIGNED NOT NULL,
value TEXT NOT NULL,
PRIMARY KEY (collection_nid, item_nid)
);
DELIMITER :
CREATE TRIGGER test_item_before_update BEFORE UPDATE ON test_item FOR EACH ROW BEGIN
IF NEW.value = OLD.value THEN
signal sqlstate '45000' set message_text = 'redundant update';
END IF;
END:
DELIMITER ;Expected behavior
Subsequent flush() operation should perform no UPDATE queries.