ON DELETE CASCADE with MySQL
schuler Nov 29, 2004 4:52 AMHi,
I work with jboss 3.2.3 and MySQL 4.0.18-nt. I have an unidirectional
one to may relation between DataCollectionConfiguration and
DataToCollect. If the configuration is removed, all assigned
DataToCollect elements must be removed too. I used a foreign
key relationship. The tables are generated by jboss.
If I remove a configuration, I get always the following exception:
10:43:39,868 ERROR [LogInterceptor] TransactionRolledbackLocalException in method: public abstract void javax.ejb.EJBLoc alObject.remove() throws javax.ejb.RemoveException,javax.ejb.EJBException, causedBy: java.sql.SQLException: General error, message from server: "Cannot add or update a child row: a foreign key constraint fails" at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1905) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1109) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1203) at com.mysql.jdbc.Connection.execSQL(Connection.java:2090) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1680) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1527) at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:324) at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreEntityCommand.execute(JDBCStoreEntityCommand.java:136) at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.storeEntity(JDBCStoreManager.java:627) at org.jboss.ejb.plugins.CMPPersistenceManager.storeEntity(CMPPersistenceManager.java:421) at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.storeEntity(CachedConnectionInterceptor.java :387) at org.jboss.ejb.EntityContainer.storeEntity(EntityContainer.java:714) at org.jboss.ejb.GlobalTxEntityMap.synchronizeEntities(GlobalTxEntityMap.java:149) at org.jboss.ejb.EntityContainer.synchronizeEntitiesWithinTransaction(EntityContainer.java:119) at org.jboss.ejb.plugins.cmp.jdbc.JDBCRemoveEntityCommand.execute(JDBCRemoveEntityCommand.java:95) at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.removeEntity(JDBCStoreManager.java:667) at org.jboss.ejb.plugins.CMPPersistenceManager.removeEntity(CMPPersistenceManager.java:492) at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.removeEntity(CachedConnectionInterceptor.jav a:430) at org.jboss.ejb.EntityContainer.remove(EntityContainer.java:505) 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.ejb.EntityContainer$ContainerInterceptor.invoke(EntityContainer.java:1084) at org.jboss.ejb.plugins.cmp.jdbc.JDBCRelationInterceptor.invoke(JDBCRelationInterceptor.java:72) at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invoke(EntitySynchronizationInterceptor.java:282) at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:185) at org.jboss.ejb.plugins.EntityReentranceInterceptor.invoke(EntityReentranceInterceptor.java:114) at org.jboss.ejb.plugins.EntityInstanceInterceptor.invoke(EntityInstanceInterceptor.java:163) at org.jboss.ejb.plugins.EntityLockInterceptor.invoke(EntityLockInterceptor.java:89) at org.jboss.ejb.plugins.EntityCreationInterceptor.invoke(EntityCreationInterceptor.java:54) at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:84) at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:267) 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.EntityContainer.internalInvoke(EntityContainer.java:489) at org.jboss.ejb.Container.invoke(Container.java:700) at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:375) at org.jboss.ejb.plugins.local.EntityProxy.invoke(EntityProxy.java:38) at $Proxy97.remove(Unknown Source) at com.dcx.fa100.datacollection.DataCollectionServiceBean.deleteDataCollectionConfiguration(DataCollectionServic eBean.java:318) 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.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:683) at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:185) at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:72) at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:84) at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:267) 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 org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:375) at org.jboss.ejb.plugins.local.StatelessSessionProxy.invoke(StatelessSessionProxy.java:83) at $Proxy103.deleteDataCollectionConfiguration(Unknown Source) at com.dcx.fa100.tg.webservice.ServiceProviderWebServiceImpl.deleteDataCollectionConfiguration(ServiceProviderWe bServiceImpl.java:483) at com.dcx.fa100.tg.webservice.ServiceProviderWebService__ServiceProvider__Tie.invoke_deleteDataCollectionConfig uration(ServiceProviderWebService__ServiceProvider__Tie.java:250) at com.dcx.fa100.tg.webservice.ServiceProviderWebService__ServiceProvider__Tie.processingHook(ServiceProviderWeb Service__ServiceProvider__Tie.java:1805) at com.sun.xml.rpc.server.StreamingHandler.handle(StreamingHandler.java:228) at com.sun.xml.rpc.server.http.JAXRPCServletDelegate.doPost(JAXRPCServletDelegate.java:316) at com.sun.xml.rpc.server.http.JAXRPCServlet.doPost(JAXRPCServlet.java:69) at javax.servlet.http.HttpServlet.service(HttpServlet.java:760) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643) at org.jboss.web.tomcat.security.JBossSecurityMgrRealm.invoke(JBossSecurityMgrRealm.java:220) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:553) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641) at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641) at org.jboss.web.tomcat.tc4.statistics.ContainerStatsValve.invoke(ContainerStatsValve.java:76) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2417) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643) at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641) at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:65) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:577) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:197) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:781) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:549) at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:605) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:677) at java.lang.Thread.run(Thread.java:534)
The following code snippet shows the relationship between the tables:
/** * @ejb:interface-method view-type="local" * @ejb:relation name="DataCollectionConfiguration-DataToCollect" role-name="Configuration" target-ejb="DataToCollectEJB" target-role-name="assigned-data-to-collect" target-cascade-delete="yes" target-multiple="no" * @jboss:target-relation related-pk-field="configurationId" fk-column="configurationId" fk-constraint="true" * @return collections of DataToCollectLocal beans */ public abstract Collection getDataToCollect();
I use xdoclet to generate the ejb-jar.xml file. The snippet shows the
generated code:
<ejb-relation > <ejb-relation-name>DataCollectionConfiguration-DataToCollect</ejb-relation-name> <ejb-relationship-role > <ejb-relationship-role-name>Configuration</ejb-relationship-role-name> <multiplicity>One</multiplicity> <relationship-role-source > <ejb-name>DataCollectionConfigurationEJB</ejb-name> </relationship-role-source> <cmr-field > <cmr-field-name>dataToCollect</cmr-field-name> <cmr-field-type>java.util.Collection</cmr-field-type> </cmr-field> </ejb-relationship-role> <ejb-relationship-role > <ejb-relationship-role-name>assigned-data-to-collect</ejb-relationship-role-name> <multiplicity>Many</multiplicity> <cascade-delete/> <relationship-role-source > <ejb-name>DataToCollectEJB</ejb-name> </relationship-role-source> </ejb-relationship-role> </ejb-relation>
What is wrong? Could anybody help me, please?
Thanks in advance.
Markus