Error while adding foreign key constraint

[persabi]

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....

[persabi]

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

[void]

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

[bartvh]

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.