3 Replies Latest reply on Jun 15, 2004 5:57 AM by Bart Vanhaute

    Error while adding foreign key constraint

    Vance Karimi Newbie

      Foriegn key constraint not working in 3.2.2/3.2.3 but is working in 3.2.1.
      On mySQL 4.0.17.
      I have the following:

      /*** AccountBean.java ***/
      /**
      * @ejb.interface-method
      * view-type = "local"
      * @ejb.relation
      * name = "Account-Transaction"
      * role-name = "Account-has-Transactions"
      * target-ejb = "Transaction"
      * target-role-name = "Transaction-belongs-to-Account"
      * @return
      */
      public abstract java.util.Collection getTransactions();


      /*** TransactionBean.java ***/
      /**
      * @ejb.interface-method
      * view-type = "local"
      * @ejb.relation
      * name = "Account-Transaction"
      * role-name = "Transaction-belongs-to-Account"
      * target-ejb = "Account"
      * target-role-name = "Account-has-Transactions"
      * @jboss.relation
      * fk-column = "account"
      * related-pk-field = "number"
      * fk-constraint = "true"
      public abstract AccountLocal getAccount();


      When I deploy in Jboss3.2.2/3.2.3 I get the following:

      org.jboss.deployment.DeploymentException: Error while adding foreign key constraint; - nested throwable: (java.sql.SQLException: General error, message from server: "Can't create table './eftgw/#sql-2cb3_8.frm' (errno: 150)")
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.addForeignKeyConstraint(JDBCStartCommand.java:724)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.addForeignKeyConstraint(JDBCStartCommand.java:637)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.execute(JDBCStartCommand.java:172)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.startStoreManager(JDBCStoreManager.java:484)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.start(JDBCStoreManager.java:388)
      at org.jboss.ejb.plugins.CMPPersistenceManager.start(CMPPersistenceManager.java:152)
      at org.jboss.ejb.EntityContainer.startService(EntityContainer.java:342)
      at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
      at sun.reflect.GeneratedMethodAccessor21.invoke(Unknown Source)
      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.system.ServiceController$ServiceProxy.invoke(ServiceController.java:976)
      at $Proxy14.start(Unknown Source)
      at org.jboss.system.ServiceController.start(ServiceController.java:394)
      at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
      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.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
      at $Proxy31.start(Unknown Source)
      at org.jboss.ejb.EjbModule.startService(EjbModule.java:331)
      at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
      at sun.reflect.GeneratedMethodAccessor21.invoke(Unknown Source)
      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.system.ServiceController$ServiceProxy.invoke(ServiceController.java:976)
      at $Proxy14.start(Unknown Source)
      at org.jboss.system.ServiceController.start(ServiceController.java:394)
      at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
      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.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
      at $Proxy12.start(Unknown Source)
      at org.jboss.ejb.EJBDeployer.start(EJBDeployer.java:544)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:824)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:642)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:605)
      at sun.reflect.GeneratedMethodAccessor22.invoke(Unknown Source)
      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.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
      at $Proxy6.deploy(Unknown Source)
      at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:302)
      at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:476)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:201)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.loop(AbstractDeploymentScanner.java:212)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.run(AbstractDeploymentScanner.java:191)
      Caused by: java.sql.SQLException: General error, message from server: "Can't create table './eftgw/#sql-2cb3_8.frm' (errno: 150)"
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1825)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1020)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1109)
      at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1070)
      at com.mysql.jdbc.Connection.execSQL(Connection.java:2027)
      at com.mysql.jdbc.Connection.execSQL(Connection.java:1984)
      at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1248)
      at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1194)
      at org.jboss.resource.adapter.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:262)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.addForeignKeyConstraint(JDBCStartCommand.java:711)
      ... 56 more


      But do not have this problem under Jboss3.2.1.
      Can anyone help. Please....

        • 1. Re: Error while adding foreign key constraint
          Vance Karimi Newbie

          standardjbosscmp-jdbc.xml under jboss3.2.3 for mysql shows:

          <type-mapping>
          mySQL
          <row-locking-template>SELECT ?1 FROM ?2 WHERE ?3 FOR UPDATE</row-locking-template>
          <pk-constraint-template>CONSTRAINT ?1 PRIMARY KEY (?2)</pk-constraint-template>
          <!-- does not work for compound keys <fk-constraint-template>ALTER TABLE ?1 ADD INDEX ind_?3 (?3), ADD CONSTRAINT ?2 FOREIGN KEY (?3) REFERENCES ?4 (?5)</fk-constraint-template> -->
          <fk-constraint-template>ALTER TABLE ?1 ADD CONSTRAINT ?2 FOREIGN KEY (?3) REFERENCES ?4 (?5)</fk-constraint-template>
          ....

          After looking at jboss3.2.1 I noticed the difference. Setting:

          <fk-constraint-template>ALTER TABLE ?1 ADD INDEX ind_?3 (?3), ADD CONSTRAINT ?2 FOREIGN KEY (?3) REFERENCES ?4 (?5)</fk-constraint-template>

          instead of:

          <fk-constraint-template>ALTER TABLE ?1 ADD CONSTRAINT ?2 FOREIGN KEY (?3) REFERENCES ?4 (?5)</fk-constraint-template>

          works. But anyone knows why it is set 'does not work for compound keys'. This does worry me for a prod system.

          I read on a mysql news group somewhere that the erno 150 is sometimes due to no index being created on foreign keys. The new fk-constraint-template does add the index whereas the original does not.

          Anyone know if this will lead to any problems?

          Cheers

          • 2. Re: Error while adding foreign key constraint
            void Newbie

            ALTER TABLE ?1 ADD INDEX ind_?3 (?3), ADD CONSTRAINT ?2 FOREIGN KEY (?3) REFERENCES ?4 (?5)

            as row-locking-template works fine if the key contains only one field.

            The problem is that if you have more than one field in the foreign key, ?3 is replaced as field1, field2, ...

            I modified it to

            ALTER TABLE ?1 ADD INDEX ?2_idx (?3), ADD CONSTRAINT ?2 FOREIGN KEY (?3) REFERENCES ?4 (?5)

            that works with JBoss 3.2.3 and MySQL 4.1.

            chris

            • 3. Re: Error while adding foreign key constraint
              Bart Vanhaute Newbie

              Just a side note: to find out why foreign key constraint fails under MySQL with INNODB tables, use

              SHOW INNODB STATUS

              You will have to be a superuser on the mysql server to execute the command.