Redundant UPDATE queries from the second flush() in a row #6504

New Issue

Closed

opened 2026-01-22 15:34:12 +01:00 by admin · 4 comments

admin commented 2026-01-22 15:34:12 +01:00

Owner

Copy Link

Originally created by @develancer on GitHub (Jul 22, 2020).

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 = 100

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

Originally created by @develancer on GitHub (Jul 22, 2020). ### 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 ```php $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 ```sql UPDATE test_item SET collection_nid = 5 WHERE collection_nid = 5 AND item_nid = 100 ``` #### Current 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 <?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 <?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: ```sql 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.

admin added the Bug label 2026-01-22 15:34:12 +01:00

admin closed this issue 2026-01-22 15:34:12 +01:00

admin commented 2026-01-22 15:34:12 +01:00

Author

Owner

Copy Link

@Feolius commented on GitHub (Oct 17, 2021):

Having the same issue. However, I figured out that that problem also exists for OneToOne relationship, when foreign key is used as a primary key. Two test entities here:

<?php

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="nodes")
 */
class Node
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue
     */
    private int $nid;

    /**
     * @ORM\OneToOne(targetEntity="NodeField", mappedBy="node")
     */
    private NodeField $field;

    /**
     * @param Collection $field
     * @return Node
     */
    public function setField(NodeField $field): Node
    {
        $this->field = $field;

        return $this;
    }
}

<?php

use Doctrine\ORM\Mapping as ORM;


/**
 * NodeField
 * @ORM\Table(name="node_fields")
 * @ORM\Entity
 */
class NodeField
{
    /**
     * @ORM\Id @ORM\OneToOne(targetEntity="Node", inversedBy="fields")
     * @ORM\JoinColumn(name="nid", referencedColumnName="nid")
     */
    private Node $node;

    /**
     * @param Node $node
     * @return NodeField
     */
    public function setNode(Node $node): NodeField
    {
        $this->node = $node;

        return $this;
    }
}

The following codeblock

<?php
$node = new Node();
$nodeField = new NodeField();
$nodeField->setNode($node);

$entityManager->persist($nodeField);
$entityManager->persist($node);
$entityManager->flush();
// Second flush here.
$entityManager->flush();
$logger = $entityManager->getConfiguration()->getSQLLogger();

foreach ($logger->queries as $query) {
    if (is_array($query['params'])) {
        echo sprintf(
            '<b>query:</b> %s, <b>params:</b> [%s]</br>',
            $query['sql'],
            implode(', ', $query['params'])
        );
    } else {
        echo sprintf(
            '<b>query:</b> %s</br>',
            $query['sql']
        );
    }

}

provides this output

query: "START TRANSACTION"
query: INSERT INTO nodes (nid) VALUES (null), params: []
query: INSERT INTO node_fields (nid) VALUES (?), params: [2]
query: "COMMIT"
query: "START TRANSACTION"
query: UPDATE node_fields SET nid = ? WHERE nid = ?, params: [2, 2]
query: "COMMIT"

So, we have redundant UPDATE query here, which is happening after the second flush() call.

@Feolius commented on GitHub (Oct 17, 2021): Having the same issue. However, I figured out that that problem also exists for OneToOne relationship, when foreign key is used as a primary key. Two test entities here: ```php <?php use Doctrine\ORM\Mapping as ORM; /** * @ORM\Entity * @ORM\Table(name="nodes") */ class Node { /** * @ORM\Id * @ORM\Column(type="integer") * @ORM\GeneratedValue */ private int $nid; /** * @ORM\OneToOne(targetEntity="NodeField", mappedBy="node") */ private NodeField $field; /** * @param Collection $field * @return Node */ public function setField(NodeField $field): Node { $this->field = $field; return $this; } } ``` ```php <?php use Doctrine\ORM\Mapping as ORM; /** * NodeField * @ORM\Table(name="node_fields") * @ORM\Entity */ class NodeField { /** * @ORM\Id @ORM\OneToOne(targetEntity="Node", inversedBy="fields") * @ORM\JoinColumn(name="nid", referencedColumnName="nid") */ private Node $node; /** * @param Node $node * @return NodeField */ public function setNode(Node $node): NodeField { $this->node = $node; return $this; } } ``` The following codeblock ```php <?php $node = new Node(); $nodeField = new NodeField(); $nodeField->setNode($node); $entityManager->persist($nodeField); $entityManager->persist($node); $entityManager->flush(); // Second flush here. $entityManager->flush(); $logger = $entityManager->getConfiguration()->getSQLLogger(); foreach ($logger->queries as $query) { if (is_array($query['params'])) { echo sprintf( '<b>query:</b> %s, <b>params:</b> [%s]</br>', $query['sql'], implode(', ', $query['params']) ); } else { echo sprintf( '<b>query:</b> %s</br>', $query['sql'] ); } } ``` provides this output ``` query: "START TRANSACTION" query: INSERT INTO nodes (nid) VALUES (null), params: [] query: INSERT INTO node_fields (nid) VALUES (?), params: [2] query: "COMMIT" query: "START TRANSACTION" query: UPDATE node_fields SET nid = ? WHERE nid = ?, params: [2, 2] query: "COMMIT" ``` So, we have redundant UPDATE query here, which is happening after the second flush() call.

admin commented 2026-01-22 15:34:13 +01:00

Author

Owner

Copy Link

@develancer commented on GitHub (Nov 26, 2021):

This bug is still present in 2.10.2, so I updated the specs.

@develancer commented on GitHub (Nov 26, 2021): This bug is still present in 2.10.2, so I updated the specs.

admin commented 2026-01-22 15:34:13 +01:00

Author

Owner

Copy Link

@derrabus commented on GitHub (Dec 21, 2021):

@develancer Can you test #9244 please?

@derrabus commented on GitHub (Dec 21, 2021): @develancer Can you test #9244 please?

admin commented 2026-01-22 15:34:13 +01:00

Author

Owner

Copy Link

@develancer commented on GitHub (Dec 28, 2021):

@derrabus The patch does help. Thanks @Feolius

@develancer commented on GitHub (Dec 28, 2021): @derrabus The patch does help. Thanks @Feolius

Sign in to join this conversation.

No Branch/Tag Specified

Branches Tags

3.6.x

2.20.x

dependabot/github_actions/2.20.x/doctrine/dot-github/dot-github/workflows/composer-lint.yml-14.0.0

3.7.x

4.0.x

2.21.x

old-prototype-3.x

3.6.2

3.6.1

3.6.0

2.20.9

3.5.8

3.5.7

3.5.6

3.5.5

2.20.8

3.5.4

3.5.3

2.20.7

3.5.2

2.20.6

3.5.1

3.5.0

3.4.4

3.4.3

3.4.2

2.20.5

3.4.1

3.4.0

3.3.4

2.20.4

3.3.3

2.20.3

3.3.2

2.20.2

3.3.1

2.20.1

3.3.0

3.2.3

2.20.0

2.19.8

3.2.2

2.19.7

3.2.1

2.19.6

3.2.0

3.1.4

3.1.3

2.19.5

3.1.2

2.19.4

3.1.1

2.19.3

2.19.2

2.19.1

3.1.0

2.19.0

3.0.3

2.18.3

3.0.2

2.18.2

3.0.1

2.18.1

3.0.0

2.18.0

2.17.5

3.0.0-RC1

2.17.4

2.17.3

2.17.2

2.17.1

3.0.0-beta2

2.17.0

2.16.3

3.0.0-beta1

2.16.2

2.16.1

2.16.0

2.15.5

2.15.4

2.15.3

2.15.2

2.15.1

2.15.0

2.14.3

2.14.2

2.14.1

2.14.0

2.13.5

2.13.4

2.13.3

2.13.2

2.13.1

2.13.0

2.12.4

2.12.3

2.12.2

2.12.1

2.12.0

2.11.3

2.11.2

2.11.1

2.11.0

2.10.5

2.10.4

2.10.3

2.10.2

2.10.1

2.10.0

2.9.6

2.9.5

2.9.4

2.9.3

2.9.2

2.9.1

2.9.0

2.8.5

2.8.4

2.8.3

2.8.2

2.8.1

2.8.0

2.7.5

2.7.4

v2.7.3

v2.7.2

v2.7.1

v2.7.0

v2.6.6

v2.6.5

v2.6.4

v2.6.3

v2.6.2

v2.6.1

v2.6.0

v2.5.14

v2.5.13

v2.5.12

v2.5.11

v2.5.10

v2.5.9

v2.5.8

v2.5.7

v2.5.6

v2.5.5

v2.5.4

v2.5.3

v2.5.2

v2.4.8

v2.5.1

v2.5.0

v2.5.0-RC2

v2.5.0-RC1

v2.5.0-beta1

v2.5.0-alpha2

v2.5.0-alpha1

v2.4.7

v2.4.6

v2.4.5

v2.4.4

v2.4.3

v2.3.6

v2.4.2

2.3.5

v2.4.1

v2.4.0

2.4.0-RC2

2.4.0-RC1

2.4.0-BETA2

2.3.4

2.3.3

2.4.0-BETA1

2.3.2

2.3.1

2.3.0

2.3.0-RC4

2.3.0-RC3

2.3.0-RC2

2.2.3

2.3.0-RC1

2.3.0-BETA1

2.1.7

2.2.2

2.2.1

2.1.6

2.2.0

2.2.0-RC1

2.2.0-BETA2

2.2.0-BETA1

2.1.5

2.1.4

2.1.3

2.1.2

2.1.1

2.0.7

2.1.0

2.1.0RC3

2.1.0RC2

2.1.0RC1

2.0.6

2.1.0BETA1

2.0.5

2.0.4

2.0.3

2.0.2

2.0.1

2.0.0

2.0.0RC2

2.0.0RC1

2.0.0-BETA4

2.0.0-BETA3

2.0.0-BETA2

2.0.0-BETA1

Labels

Clear labels

BC Break

Bug

Can't Fix

Deprecation

Documentation

DQL

Duplicate

Failing Test

Hacktoberfest

Improvement

Improvement

Incomplete

Invalid

Missing Tests

New Feature

PR Created

pull-request

Mirrored from GitHub Pull Request

Question

Regression

Status: Waiting feedback

Waiting feedback

WIP

Won't Fix

No Label Bug

Milestone

No items

No Milestone

Projects

Clear projects

No project

Assignees

Clear assignees

admin

No Assignees

1 Participants

Notifications

Subscribe

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6504