1 Reply Latest reply on Dec 1, 2004 3:06 AM by Alexey Loubyansky

    ON DELETE CASCADE with MySQL

    Markus Schuler Newbie

      Hi,

      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