cascade-delete: SQL DELETE Statement missing!
stupiddog Jan 6, 2004 2:28 PMI 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