DDC-392: OneToMany cascade delete and InheritanceType("JOINED") table #488

Closed
opened 2026-01-22 12:40:12 +01:00 by admin · 9 comments
Owner

Originally created by @doctrinebot on GitHub (Mar 6, 2010).

Jira issue originally created by user freeakk:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 't17'' in /var/www/shelly/library/Doctrine/ORM/Persisters/StandardEntityPersister.php:560 Stack trace: #0 /var/www/shelly/library/Doctrine/ORM/Persisters/StandardEntityPersister.php(560): PDOStatement->execute(Array) #1 /var/www/shelly/library/Doctrine/ORM/Mapping/OneToManyMapping.php(149): Doctrine\ORM\Persisters\StandardEntityPersister->loadOneToManyCollection(Object(Doctrine\ORM\Mapping\OneToManyMapping), Array, Object(Doctrine\ORM\PersistentCollection)) #2 /var/www/shelly/library/Doctrine/ORM/PersistentCollection.php(235): Doctrine\ORM\Mapping\OneToManyMapping->load(Object(shellyHost), Object(Doctrine\ORM\PersistentCollection), Object(Doctrine\ORM\EntityManager)) #3 /var/www/shelly/library/Doctrine/ORM/PersistentCollection.php(532): Doctrine\ORM\PersistentCollection->_initialize() #4 /var/www/shelly/library/Doctrine/ORM/UnitOfWork.php(1629): Doctrine\ORM\PersistentCollection->get in /var/www/shelly/library/Doctrine/ORM/Persisters/StandardEntityPersister.php on line 560

when delete shellyHost row

Originally created by @doctrinebot on GitHub (Mar 6, 2010). Jira issue originally created by user freeakk: ``` Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 't17'' in /var/www/shelly/library/Doctrine/ORM/Persisters/StandardEntityPersister.php:560 Stack trace: #0 /var/www/shelly/library/Doctrine/ORM/Persisters/StandardEntityPersister.php(560): PDOStatement->execute(Array) #1 /var/www/shelly/library/Doctrine/ORM/Mapping/OneToManyMapping.php(149): Doctrine\ORM\Persisters\StandardEntityPersister->loadOneToManyCollection(Object(Doctrine\ORM\Mapping\OneToManyMapping), Array, Object(Doctrine\ORM\PersistentCollection)) #2 /var/www/shelly/library/Doctrine/ORM/PersistentCollection.php(235): Doctrine\ORM\Mapping\OneToManyMapping->load(Object(shellyHost), Object(Doctrine\ORM\PersistentCollection), Object(Doctrine\ORM\EntityManager)) #3 /var/www/shelly/library/Doctrine/ORM/PersistentCollection.php(532): Doctrine\ORM\PersistentCollection->_initialize() #4 /var/www/shelly/library/Doctrine/ORM/UnitOfWork.php(1629): Doctrine\ORM\PersistentCollection->get in /var/www/shelly/library/Doctrine/ORM/Persisters/StandardEntityPersister.php on line 560 ``` when delete shellyHost row
admin added the Bug label 2026-01-22 12:40:12 +01:00
admin closed this issue 2026-01-22 12:40:13 +01:00
Author
Owner

@doctrinebot commented on GitHub (Mar 7, 2010):

Comment created by @beberlei:

Just as a note, using 21 subclsses with Joined Inheritence is NOT encouraged. You will get massive performance problems down the road and judging from the base class you don't need them anyways since you could solve the problem using strategy pattern.

Can you try just deleting an inventory basic instance? Does it give the same error?

@doctrinebot commented on GitHub (Mar 7, 2010): Comment created by @beberlei: Just as a note, using 21 subclsses with Joined Inheritence is NOT encouraged. You will get massive performance problems down the road and judging from the base class you don't need them anyways since you could solve the problem using strategy pattern. Can you try just deleting an inventory basic instance? Does it give the same error?
Author
Owner

@doctrinebot commented on GitHub (Mar 7, 2010):

Comment created by @beberlei:

Oh please show us the delete query sql, this could help

@doctrinebot commented on GitHub (Mar 7, 2010): Comment created by @beberlei: Oh please show us the delete query sql, this could help
Author
Owner

@doctrinebot commented on GitHub (Mar 7, 2010):

Comment created by freeakk:

Now I get other error when run $em->remove($shellyHost);

<b>Fatal error</b>:  Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`private*inventory_basic`, CONSTRAINT `private_inventory_basic_ibfk_1` FOREIGN KEY (`host_id`) REFERENCES `shelly*host` (`id`))' in /var/www/shelly/library/Doctrine/DBAL/Connection.php:627
Stack trace:
#0 /var/www/shelly/library/Doctrine/DBAL/Connection.php(627): PDOStatement-&gt;execute(Array)
#1 /var/www/shelly/library/Doctrine/DBAL/Connection.php(388): Doctrine\DBAL\Connection-&gt;executeUpdate('DELETE FROM she...', Array)
#2 /var/www/shelly/library/Doctrine/ORM/Persisters/StandardEntityPersister.php(289): Doctrine\DBAL\Connection-&gt;delete('shelly_host', Array)
#3 /var/www/shelly/library/Doctrine/ORM/UnitOfWork.php(789): Doctrine\ORM\Persisters\StandardEntityPersister-&gt;delete(Object(shellyHost))
#4 /var/www/shelly/library/Doctrine/ORM/UnitOfWork.php(312): Doctrine\ORM\UnitOfWork-&gt;_executeDeletions(Object(Doctrine\ORM\Mapping\ClassMetadat in <b>/var/www/shelly/library/Doctrine/DBAL/Connection.php</b> on line <b>627</b><br />

SQL:

100307 16:34:38   107 Connect   shelly@localhost on test
          107 Query SET NAMES 'utf8'
          107 Query START TRANSACTION
          107 Query INSERT INTO shelly*host (name, ssh_login, ssh_password, ip_address, ssh*port) VALUES (NULL, NULL, NULL, NULL, NULL)
          107 Query COMMIT
          107 Query SELECT shelly*host.id, shelly_host.name, shelly_host.ssh_login, shelly_host.ssh_password, shelly_host.ip_address, shelly_host.ssh_port FROM shelly*host WHERE id = '1'
          107 Query START TRANSACTION
          107 Query INSERT INTO private*inventory_basic (product, vendor, version, businfo, capabilities, num, comment, physid, driver, driver_version, description, xml_id, class, handle, parent_handle, host*id, discr) VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '17')
          107 Query INSERT INTO inventory_system (id, name, serial, boot, chassis, smbios, dmi, bits, uuid) VALUES ('1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
          107 Query COMMIT
          107 Query START TRANSACTION
          107 Query DELETE FROM shelly_host WHERE id = '6'
          107 Query COMMIT
          107 Quit  
100307 16:35:44   108 Connect   shelly@localhost on test
          108 Query SET NAMES 'utf8'
          108 Query START TRANSACTION
          108 Query INSERT INTO shelly*host (name, ssh_login, ssh_password, ip_address, ssh*port) VALUES (NULL, NULL, NULL, NULL, NULL)
          108 Query COMMIT
          108 Query START TRANSACTION
          108 Query INSERT INTO private*inventory_basic (product, vendor, version, businfo, capabilities, num, comment, physid, driver, driver_version, description, xml_id, class, handle, parent_handle, host*id, discr) VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7', '17')
          108 Query INSERT INTO inventory_system (id, name, serial, boot, chassis, smbios, dmi, bits, uuid) VALUES ('2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
          108 Query COMMIT
          108 Query START TRANSACTION
          108 Query DELETE FROM shelly_host WHERE id = '7'
          108 Query ROLLBACK
100307 16:35:45   108 Quit  

My code:

        $shellyHost = new shellyHost();
        var_dump($shellyHost);
        $em->persist($shellyHost);
        $em->flush();


        $InventorySystem = new inventorySystem();
        $InventorySystem->setHostId($shellyHost);
        var_dump($InventorySystem);
        $em->persist($InventorySystem);
        $em->flush();

        $em->remove($shellyHost);
        $em->flush();
        die();

Without error: when delete InventorySystem object:

        $shellyHost = new shellyHost();
        var_dump($shellyHost);
        $em->persist($shellyHost);
        $em->flush();


        $InventorySystem = new inventorySystem();
        $InventorySystem->setHostId($shellyHost);
        var_dump($InventorySystem);
        $em->persist($InventorySystem);
        $em->flush();

        $em->remove($InventorySystem);
        $em->flush();
        die();

Also, no error when only create nodes

        $shellyHost = new shellyHost();
        var_dump($shellyHost);
        $em->persist($shellyHost);
        $em->flush();        

        $InventorySystem = new inventorySystem();
        $InventorySystem->setHostId($shellyHost);
        var_dump($InventorySystem);
        $em->persist($InventorySystem);
        $em->flush();

        // $em->remove($shellyHost);
        $em->flush();
        die();
@doctrinebot commented on GitHub (Mar 7, 2010): Comment created by freeakk: Now I get other error when run $em->remove($shellyHost); ``` <b>Fatal error</b>: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`private*inventory_basic`, CONSTRAINT `private_inventory_basic_ibfk_1` FOREIGN KEY (`host_id`) REFERENCES `shelly*host` (`id`))' in /var/www/shelly/library/Doctrine/DBAL/Connection.php:627 Stack trace: #0 /var/www/shelly/library/Doctrine/DBAL/Connection.php(627): PDOStatement-&gt;execute(Array) #1 /var/www/shelly/library/Doctrine/DBAL/Connection.php(388): Doctrine\DBAL\Connection-&gt;executeUpdate('DELETE FROM she...', Array) #2 /var/www/shelly/library/Doctrine/ORM/Persisters/StandardEntityPersister.php(289): Doctrine\DBAL\Connection-&gt;delete('shelly_host', Array) #3 /var/www/shelly/library/Doctrine/ORM/UnitOfWork.php(789): Doctrine\ORM\Persisters\StandardEntityPersister-&gt;delete(Object(shellyHost)) #4 /var/www/shelly/library/Doctrine/ORM/UnitOfWork.php(312): Doctrine\ORM\UnitOfWork-&gt;_executeDeletions(Object(Doctrine\ORM\Mapping\ClassMetadat in <b>/var/www/shelly/library/Doctrine/DBAL/Connection.php</b> on line <b>627</b><br /> ``` SQL: ``` 100307 16:34:38 107 Connect shelly@localhost on test 107 Query SET NAMES 'utf8' 107 Query START TRANSACTION 107 Query INSERT INTO shelly*host (name, ssh_login, ssh_password, ip_address, ssh*port) VALUES (NULL, NULL, NULL, NULL, NULL) 107 Query COMMIT 107 Query SELECT shelly*host.id, shelly_host.name, shelly_host.ssh_login, shelly_host.ssh_password, shelly_host.ip_address, shelly_host.ssh_port FROM shelly*host WHERE id = '1' 107 Query START TRANSACTION 107 Query INSERT INTO private*inventory_basic (product, vendor, version, businfo, capabilities, num, comment, physid, driver, driver_version, description, xml_id, class, handle, parent_handle, host*id, discr) VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '17') 107 Query INSERT INTO inventory_system (id, name, serial, boot, chassis, smbios, dmi, bits, uuid) VALUES ('1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL) 107 Query COMMIT 107 Query START TRANSACTION 107 Query DELETE FROM shelly_host WHERE id = '6' 107 Query COMMIT 107 Quit 100307 16:35:44 108 Connect shelly@localhost on test 108 Query SET NAMES 'utf8' 108 Query START TRANSACTION 108 Query INSERT INTO shelly*host (name, ssh_login, ssh_password, ip_address, ssh*port) VALUES (NULL, NULL, NULL, NULL, NULL) 108 Query COMMIT 108 Query START TRANSACTION 108 Query INSERT INTO private*inventory_basic (product, vendor, version, businfo, capabilities, num, comment, physid, driver, driver_version, description, xml_id, class, handle, parent_handle, host*id, discr) VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7', '17') 108 Query INSERT INTO inventory_system (id, name, serial, boot, chassis, smbios, dmi, bits, uuid) VALUES ('2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL) 108 Query COMMIT 108 Query START TRANSACTION 108 Query DELETE FROM shelly_host WHERE id = '7' 108 Query ROLLBACK 100307 16:35:45 108 Quit ``` My code: ``` $shellyHost = new shellyHost(); var_dump($shellyHost); $em->persist($shellyHost); $em->flush(); $InventorySystem = new inventorySystem(); $InventorySystem->setHostId($shellyHost); var_dump($InventorySystem); $em->persist($InventorySystem); $em->flush(); $em->remove($shellyHost); $em->flush(); die(); ``` Without error: when delete InventorySystem object: ``` $shellyHost = new shellyHost(); var_dump($shellyHost); $em->persist($shellyHost); $em->flush(); $InventorySystem = new inventorySystem(); $InventorySystem->setHostId($shellyHost); var_dump($InventorySystem); $em->persist($InventorySystem); $em->flush(); $em->remove($InventorySystem); $em->flush(); die(); ``` Also, no error when only create nodes ``` $shellyHost = new shellyHost(); var_dump($shellyHost); $em->persist($shellyHost); $em->flush(); $InventorySystem = new inventorySystem(); $InventorySystem->setHostId($shellyHost); var_dump($InventorySystem); $em->persist($InventorySystem); $em->flush(); // $em->remove($shellyHost); $em->flush(); die(); ```
Author
Owner

@doctrinebot commented on GitHub (Mar 7, 2010):

Comment created by freeakk:

Create schema log

@doctrinebot commented on GitHub (Mar 7, 2010): Comment created by freeakk: Create schema log
Author
Owner

@doctrinebot commented on GitHub (Mar 7, 2010):

Comment created by freeakk:

Also I get this error in phpMyAdmin. This error with db schema and keys.
DELETE FROM test.shelly*host WHERE shelly*host.id =1 LIMIT 1

Ответ MySQL: Документация
#1451 - Cannot delete or update a parent row: a foreign key constraint fails (test.private*inventory_basic, CONSTRAINT private_inventory_basic_ibfk_1 FOREIGN KEY (host_id) REFERENCES shelly*host (id))

@doctrinebot commented on GitHub (Mar 7, 2010): Comment created by freeakk: Also I get this error in phpMyAdmin. This error with db schema and keys. DELETE FROM `test`.`shelly*host` WHERE `shelly*host`.`id` =1 LIMIT 1 Ответ MySQL: Документация #1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test`.`private*inventory_basic`, CONSTRAINT `private_inventory_basic_ibfk_1` FOREIGN KEY (`host_id`) REFERENCES `shelly*host` (`id`))
Author
Owner

@doctrinebot commented on GitHub (Mar 7, 2010):

Comment created by freeakk:

I get error with this Foreign Key:
ALTER TABLE `private*inventory*basic`
  ADD CONSTRAINT `private*inventory_basic_ibfk_1` FOREIGN KEY (`host_id`) REFERENCES `shelly*host` (`id`);

yaml schema:
  oneToMany:
    components:
      targetEntity: privateInventoryBasic
      cascade:
        remove: true
        persist: true
        refresh: true
        merge: true
        detach: true
      mappedBy: hostId
      orphanRemoval: true

I need to add this key, but I don't know how.

ALTER TABLE `private*inventory*basic`
  ADD CONSTRAINT `private*inventory_basic_ibfk_1` FOREIGN KEY (`host_id`) REFERENCES `shelly*host` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

I think cascade definition don't work with mySQL.

@doctrinebot commented on GitHub (Mar 7, 2010): Comment created by freeakk: ``` I get error with this Foreign Key: ALTER TABLE `private*inventory*basic` ADD CONSTRAINT `private*inventory_basic_ibfk_1` FOREIGN KEY (`host_id`) REFERENCES `shelly*host` (`id`); yaml schema: oneToMany: components: targetEntity: privateInventoryBasic cascade: remove: true persist: true refresh: true merge: true detach: true mappedBy: hostId orphanRemoval: true ``` I need to add this key, but I don't know how. ``` ALTER TABLE `private*inventory*basic` ADD CONSTRAINT `private*inventory_basic_ibfk_1` FOREIGN KEY (`host_id`) REFERENCES `shelly*host` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; ``` I think cascade definition don't work with mySQL.
Author
Owner

@doctrinebot commented on GitHub (Mar 12, 2010):

Comment created by freeakk:

This is problem with mysql, which requires onDelete="SOME ACTION" and onUpdate="SOME ACTION".
These definitions can be found here
http://www.doctrine-project.org/documentation/manual/2_0/en/annotations-reference%3Areference%3A%40joincolumn

Also there are bug with these definitions
http://www.doctrine-project.org/jira/browse/DDC-409

@doctrinebot commented on GitHub (Mar 12, 2010): Comment created by freeakk: This is problem with mysql, which requires onDelete="SOME ACTION" and onUpdate="SOME ACTION". These definitions can be found here http://www.doctrine-project.org/documentation/manual/2_0/en/annotations-reference%3Areference%3A%40joincolumn Also there are bug with these definitions http://www.doctrine-project.org/jira/browse/[DDC-409](http://www.doctrine-project.org/jira/browse/DDC-409)
Author
Owner

@doctrinebot commented on GitHub (Mar 12, 2010):

Issue was closed with resolution "Incomplete"

@doctrinebot commented on GitHub (Mar 12, 2010): Issue was closed with resolution "Incomplete"
Author
Owner

@doctrinebot commented on GitHub (Dec 13, 2015):

Imported 3 attachments from Jira into https://gist.github.com/7ed0ea4aa1ce416ca5d7

@doctrinebot commented on GitHub (Dec 13, 2015): Imported 3 attachments from Jira into https://gist.github.com/7ed0ea4aa1ce416ca5d7 - [10431_privateInventoryBasic.php](https://gist.github.com/7ed0ea4aa1ce416ca5d7#file-10431_privateInventoryBasic-php) - [10432_shellyHost.php](https://gist.github.com/7ed0ea4aa1ce416ca5d7#file-10432_shellyHost-php) - [10433_create_tables.log](https://gist.github.com/7ed0ea4aa1ce416ca5d7#file-10433_create_tables-log)
Sign in to join this conversation.
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#488