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

[doctrinebot]

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

[doctrinebot]

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]

Comment created by @beberlei:

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

[doctrinebot]

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]

Comment created by freeakk:

Create schema log

[doctrinebot]

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]

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]

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)

[doctrinebot]

Issue was closed with resolution "Incomplete"

[doctrinebot]

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

• 10431_privateInventoryBasic.php
• 10432_shellyHost.php
• 10433_create_tables.log