0 Replies Latest reply on Jan 6, 2004 2:28 PM by Andreas Buschka

    cascade-delete: SQL DELETE Statement missing!

    Andreas Buschka Newbie

      I have the following problem:

      An "Acl" object contains 0 or more "AclEintrag" objects. I can get a set of AclEintrag objects with the aclEintraege relation. If I delete an entry in this set, the corresponding AclEintrag object should be deleted.

      The ejb-jar.xml extract looks like this:

      <ejb-relation>
      <ejb-relation-name>aclEintrag-acl</ejb-relation-name>
      <ejb-relationship-role>
      aclEintrag
      <ejb-relationship-role-name>AclEintragRelationshipRole</ejb-relationship-role-name>
      Many
      <cascade-delete />
      <relationship-role-source>
      aclEintrag
      <ejb-name>AclEintrag</ejb-name>
      </relationship-role-source>
      <cmr-field>
      acl
      <cmr-field-name>acl</cmr-field-name>
      </cmr-field>
      </ejb-relationship-role>
      <ejb-relationship-role>
      acl
      <ejb-relationship-role-name>AclRelationshipRole</ejb-relationship-role-name>
      One
      <relationship-role-source>
      acl
      <ejb-name>Acl</ejb-name>
      </relationship-role-source>
      <cmr-field>
      aclEintrag
      <cmr-field-name>aclEintraege</cmr-field-name>
      <cmr-field-type>java.util.Set</cmr-field-type>
      </cmr-field>
      </ejb-relationship-role>
      </ejb-relation>

      And the two database tables are defined like this (Oracle 9i server):
      CREATE TABLE acleintraege (
      ACLEINTRAG_ID INTEGER NOT NULL INITIALLY DEFERRED DEFERRABLE,
      ADRESSAT_ID INTEGER NOT NULL INITIALLY DEFERRED DEFERRABLE,
      ACL_ID INTEGER NOT NULL INITIALLY DEFERRED DEFERRABLE
      )
      ;



      CREATE TABLE acls (
      ACL_ID INTEGER NOT NULL INITIALLY DEFERRED DEFERRABLE,
      NAME VARCHAR2(50) NOT NULL INITIALLY DEFERRED DEFERRABLE,
      TYP INTEGER NOT NULL INITIALLY DEFERRED DEFERRABLE,
      EXTERNE_ACL_ID INTEGER
      )
      ;

      ALTER TABLE acleintraege
      ADD CONSTRAINT PK_ACLEINTRAEGE
      PRIMARY KEY (ACLEINTRAG_ID)
      ;

      ALTER TABLE acls
      ADD CONSTRAINT PK_ACLS
      PRIMARY KEY (ACL_ID)
      ;

      ALTER TABLE acls
      ADD CONSTRAINT FK_EXTERNE_ACLS FOREIGN KEY (EXTERNE_ACL_ID)
      REFERENCES externeacls (EXTERNE_ACL_ID) INITIALLY DEFERRED DEFERRABLE
      ;

      When I try to delete all AclEintraege objects for an Acl object in a session bean, like the following, the transaction is rolled back:

      public void setAclEintraegeForAcl(AclDto aclDto, AclEintragDto[] aclEintraege)
      throws RemoteException {
      try {
      Acl acl = aclHome.findByPrimaryKey(aclDto.getId());
      Set ejbAclEintraege = acl.getAclEintraege();

      // Alte ACL auslöschen
      Iterator i = ejbAclEintraege.iterator();
      while ( i.hasNext()) {
      AclEintrag aclEintrag = (AclEintrag)i.next();
      i.remove();
      }

      // @todo
      }
      catch (FinderException ex) {
      throw new RemoteException(ex.getMessage());
      }
      }

      The last lines from server.log are the following. It seems that all statements are issued, but not the DELETE statement that is required to remove the AclEintrag object. I think that is has nothing to do with my constraints, because

      update acleintraege set acl_id=null where acleintrag_id=2;
      delete from acleintraege where acleintrag_id=2;
      commit;

      works perfectly in SQL-Plus, because of the deferred constraint. What I do not understand is why no DELETE statement is issued by JBoss before committing the transaction:

      2004-01-06 20:36:02,923 INFO [org.jboss.deployment.MainDeployer] Deployed package: file:/C:/Java/jboss/server/default/conf/jboss-service.xml
      2004-01-06 20:36:02,933 INFO [org.jboss.system.server.Server] JBoss (MX MicroKernel) [3.2.3 (build: CVSTag=JBoss_3_2_3 date=200311301445)] Started in 3m:31s:965ms
      2004-01-06 20:36:28,009 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.Adressat#findAlleAdressaten] Executing SQL: SELECT t0_ad.ADRESSAT_ID FROM ADRESSATEN t0_ad
      2004-01-06 20:36:28,430 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Adressat] Executing SQL: SELECT ADRESSAT_ID, BENUTZER_NAME, IST_SYSTEMADMINISTRATOR FROM ADRESSATEN WHERE (ADRESSAT_ID=?) OR (ADRESSAT_ID=?) OR (ADRESSAT_ID=?) OR (ADRESSAT_ID=?)
      2004-01-06 20:36:28,921 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Acl#findByPrimaryKey] Executing SQL: SELECT ACL_ID FROM ACLS WHERE ACL_ID=?
      2004-01-06 20:36:29,011 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Acl] Executing SQL: SELECT NAME, TYP, EXTERNE_ACL_ID FROM ACLS WHERE (ACL_ID=?)
      2004-01-06 20:36:29,051 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.Acl] Executing SQL: SELECT ACLEINTRAG_ID FROM ACLEINTRAEGE WHERE (ACL_ID=?)
      2004-01-06 20:36:29,151 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.AclEintrag] Executing SQL: SELECT ADRESSAT_ID FROM ACLEINTRAEGE WHERE (ACLEINTRAG_ID=?)
      2004-01-06 20:36:29,171 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Adressat] Executing SQL: SELECT BENUTZER_NAME, IST_SYSTEMADMINISTRATOR FROM ADRESSATEN WHERE (ADRESSAT_ID=?)
      2004-01-06 20:36:34,779 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Acl#findByPrimaryKey] Executing SQL: SELECT ACL_ID FROM ACLS WHERE ACL_ID=?
      2004-01-06 20:36:34,879 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.Acl] Executing SQL: SELECT NAME, TYP, EXTERNE_ACL_ID FROM ACLS WHERE (ACL_ID=?)
      2004-01-06 20:36:34,929 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.Acl] Executing SQL: SELECT ACLEINTRAG_ID FROM ACLEINTRAEGE WHERE (ACL_ID=?)
      2004-01-06 20:36:35,079 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.AclEintrag] Executing SQL: SELECT ADRESSAT_ID FROM ACLEINTRAEGE WHERE (ACLEINTRAG_ID=?)
      2004-01-06 20:36:35,130 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreEntityCommand.AclEintrag] Executing SQL: UPDATE ACLEINTRAEGE SET ACL_ID=? WHERE ACLEINTRAG_ID=?
      2004-01-06 20:36:35,170 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreEntityCommand.AclEintrag] Rows affected = 1

      <-- There should be a DELETE-Statement here!

      2004-01-06 20:36:36,141 WARN [org.jboss.tm.TransactionImpl] XAException: tx=TransactionImpl:XidImpl [FormatId=257, GlobalId=strawberry//33, BranchQual=] errorCode=XAER_RMERR
      oracle.jdbc.xa.OracleXAException
      at oracle.jdbc.xa.OracleXAResource.checkError(OracleXAResource.java:1157)
      at oracle.jdbc.xa.client.OracleXAResource.commit(OracleXAResource.java:590)
      at org.jboss.resource.adapter.jdbc.xa.XAManagedConnection.commit(XAManagedConnection.java:185)
      at org.jboss.tm.TransactionImpl.commitResources(TransactionImpl.java:1593)
      at org.jboss.tm.TransactionImpl.commit(TransactionImpl.java:375)
      at org.jboss.ejb.plugins.TxInterceptorCMT.endTransaction(TxInterceptorCMT.java:398)
      at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:277)
      at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:128)
      at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:118)
      at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:191)
      at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)
      at org.jboss.ejb.StatelessSessionContainer.internalInvoke(StatelessSessionContainer.java:331)
      at org.jboss.ejb.Container.invoke(Container.java:700)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
      at org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:367)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:261)
      at sun.rmi.transport.Transport$1.run(Transport.java:148)
      at java.security.AccessController.doPrivileged(Native Method)
      at sun.rmi.transport.Transport.serviceCall(Transport.java:144)
      at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:460)
      at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:701)
      at java.lang.Thread.run(Thread.java:534)
      2004-01-06 20:36:36,221 WARN [org.jboss.tm.TransactionImpl] xa error: -3 (A resource manager error has occured in the transaction branch.); oracle error: 2091; oracle sql error: 0;
      oracle.jdbc.xa.OracleXAException
      at oracle.jdbc.xa.OracleXAResource.checkError(OracleXAResource.java:1157)
      at oracle.jdbc.xa.client.OracleXAResource.commit(OracleXAResource.java:590)
      at org.jboss.resource.adapter.jdbc.xa.XAManagedConnection.commit(XAManagedConnection.java:185)
      at org.jboss.tm.TransactionImpl.commitResources(TransactionImpl.java:1593)
      at org.jboss.tm.TransactionImpl.commit(TransactionImpl.java:375)
      at org.jboss.ejb.plugins.TxInterceptorCMT.endTransaction(TxInterceptorCMT.java:398)
      at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:277)
      at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:128)
      at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:118)
      at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:191)
      at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)
      at org.jboss.ejb.StatelessSessionContainer.internalInvoke(StatelessSessionContainer.java:331)
      at org.jboss.ejb.Container.invoke(Container.java:700)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
      at org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:367)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:261)
      at sun.rmi.transport.Transport$1.run(Transport.java:148)
      at java.security.AccessController.doPrivileged(Native Method)
      at sun.rmi.transport.Transport.serviceCall(Transport.java:144)
      at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:460)
      at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:701)
      at java.lang.Thread.run(Thread.java:534)
      2004-01-06 20:36:36,461 ERROR [org.jboss.ejb.plugins.LogInterceptor] TransactionRolledbackException in method: public abstract void de.fernunihagen.notifyme.ejb.Acl.AclSessionFacade.setAclEintraegeForAcl(de.fernunihagen.notifyme.ejb.Acl.AclDto,de.fernunihagen.notifyme.ejb.AclEintrag.AclEintragDto[]) throws java.rmi.RemoteException, causedBy:
      org.jboss.tm.JBossRollbackException: Unable to commit, tx=TransactionImpl:XidImpl [FormatId=257, GlobalId=strawberry//33, BranchQual=] status=STATUS_NO_TRANSACTION; - nested throwable: (oracle.jdbc.xa.OracleXAException)
      at org.jboss.tm.TransactionImpl.commit(TransactionImpl.java:413)
      at org.jboss.ejb.plugins.TxInterceptorCMT.endTransaction(TxInterceptorCMT.java:398)
      at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:277)
      at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:128)
      at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:118)
      at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:191)
      at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)
      at org.jboss.ejb.StatelessSessionContainer.internalInvoke(StatelessSessionContainer.java:331)
      at org.jboss.ejb.Container.invoke(Container.java:700)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
      at org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:367)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:261)
      at sun.rmi.transport.Transport$1.run(Transport.java:148)
      at java.security.AccessController.doPrivileged(Native Method)
      at sun.rmi.transport.Transport.serviceCall(Transport.java:144)
      at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:460)
      at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:701)
      at java.lang.Thread.run(Thread.java:534)
      Caused by: oracle.jdbc.xa.OracleXAException
      at oracle.jdbc.xa.OracleXAResource.checkError(OracleXAResource.java:1157)
      at oracle.jdbc.xa.client.OracleXAResource.commit(OracleXAResource.java:590)
      at org.jboss.resource.adapter.jdbc.xa.XAManagedConnection.commit(XAManagedConnection.java:185)
      at org.jboss.tm.TransactionImpl.commitResources(TransactionImpl.java:1593)
      at org.jboss.tm.TransactionImpl.commit(TransactionImpl.java:375)
      ... 26 more