1 Reply Latest reply on Feb 7, 2003 8:24 AM by mik1

foreign key constraint failed

mik1 Feb 5, 2003 12:22 PM

Hi,

I'm using JBoss3.0.4 with MySQL.

It seems to have a trouble with the table relation mapping when one Entity has a composed key.

Description :
I've got 2 Entities defined like this ....

=========================================================

<display-name>Application Entity Bean</display-name>
<ejb-name>ApplicationEJB</ejb-name>

<local-home>com.opensugar.manager.administration.ApplicationHome</local-home>
com.opensugar.manager.administration.Application
<ejb-class>com.opensugar.manager.administration.ApplicationBean</ejb-class>

<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.String</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>Application</abstract-schema-name>

<cmp-field><field-name>name</field-name></cmp-field>
<cmp-field><field-name>description</field-name></cmp-field>
<cmp-field><field-name>maxLevel</field-name></cmp-field>

<primkey-field>name</primkey-field>
......

<display-name>UserGroup Entity Bean</display-name>
<ejb-name>UserGroupEJB</ejb-name>

<local-home>com.opensugar.manager.administration.UserGroupHome</local-home>
com.opensugar.manager.administration.UserGroup
<ejb-class>com.opensugar.manager.administration.UserGroupBean</ejb-class>

<persistence-type>Container</persistence-type>
<prim-key-class>com.opensugar.manager.administration.UserGroupPK</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>UserGroup</abstract-schema-name>

<cmp-field><field-name>name</field-name></cmp-field>
<cmp-field><field-name>operatorName</field-name></cmp-field>
<cmp-field><field-name>description</field-name></cmp-field>
<cmp-field><field-name>dor</field-name></cmp-field>
......
=========================================================

Then I've got a many many relation ....

=========================================================
ejb-jar :

<ejb-relation>
<ejb-relation-name>Application-UserGroup</ejb-relation-name>

<ejb-relationship-role>
<ejb-relationship-role-name>usergroup-has-app</ejb-relationship-role-name>

Many

<relationship-role-source>
<ejb-name>UserGroupEJB</ejb-name>
</relationship-role-source>

<cmr-field>
<cmr-field-name>applications</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>

<ejb-relationship-role>
<ejb-relationship-role-name>application-to-usergroup</ejb-relationship-role-name>

Many

<relationship-role-source>
<ejb-name>ApplicationEJB</ejb-name>
</relationship-role-source>

<cmr-field>
<cmr-field-name>userGroups</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
</ejb-relation>

jbosscmp-jdbc

<ejb-relation>
<ejb-relation-name>Application-UserGroup</ejb-relation-name>
<relation-table-mapping>
<table-name>application_usergroup</table-name>
</relation-table-mapping>

<ejb-relationship-role>
<ejb-relationship-role-name>usergroup-has-app</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>name</field-name>
<column-name>usergroup</column-name>
</key-field>
<key-field>
<field-name>operatorName</field-name>
<column-name>operator</column-name>
</key-field>
</key-fields>
</ejb-relationship-role>

<ejb-relationship-role>
<ejb-relationship-role-name>application-to-usergroup</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>name</field-name>
<column-name>application</column-name>
</key-field>
</key-fields>
</ejb-relationship-role>
</ejb-relation>
=========================================================

According to me all is OK in my xml files, is it ?

But when I deployed my jar I've got an error because of JBoss failed to add a foreign key constraint on my table :(

I've put it on debug mode and this is what it gives :
=========================================================
18:18:53,215 INFO [ApplicationEJB] Created table 'application_usergroup' successfully.
18:18:53,215 DEBUG [ApplicationEJB] Executing SQL: ALTER TABLE application_usergroup ADD INDEX ind_application (application), ADD CONSTRAINT fk_application_user
group_userGroups FOREIGN KEY (application) REFERENCES application (NAME)
18:18:53,275 INFO [ApplicationEJB] Added foreign key constraint to table 'application_usergroup'
18:18:53,275 DEBUG [ApplicationEJB] Executing SQL: ALTER TABLE application_usergroup ADD INDEX ind_usergroup, operator (usergroup, operator), ADD CONSTRAINT fk_
application_usergroup_applications FOREIGN KEY (usergroup, operator) REFERENCES usergroup (NAME, OPERATORNAME)
18:18:53,295 WARN [ApplicationEJB] Could not add foreign key constraint: table=application_usergroup
18:18:53,295 WARN [ServiceController] Problem starting service jboss.j2ee:jndiName=osmadmin/UserGroupHome,service=EJB
org.jboss.deployment.DeploymentException: Error while adding foreign key constraint; - nested throwable: (java.sql.SQLException: Syntax error or access violatio
n: You have an error in your SQL syntax near ' operator (usergroup, operator), ADD CONSTRAINT fk_application_usergroup_applica' at line 1)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.addForeignKeyConstraint(JDBCStartCommand.java:384)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.addForeignKeyConstraint(JDBCStartCommand.java:295)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.execute(JDBCStartCommand.java:127)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.startStoreManager(JDBCStoreManager.java:457)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.start(JDBCStoreManager.java:369)
at org.jboss.ejb.plugins.CMPPersistenceManager.start(CMPPersistenceManager.java:198)
at org.jboss.ejb.EntityContainer.start(EntityContainer.java:376)
at org.jboss.ejb.Container.invoke(Container.java:756)
at org.jboss.ejb.EntityContainer.invoke(EntityContainer.java:1058)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:978)
at $Proxy5.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:398)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
at org.jboss.util.jmx.MBeanProxy.invoke(MBeanProxy.java:174)
at $Proxy163.start(Unknown Source)
at org.jboss.ejb.EjbModule.startService(EjbModule.java:430)
at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:165)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:978)
at $Proxy5.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:398)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
at org.jboss.util.jmx.MBeanProxy.invoke(MBeanProxy.java:174)
at $Proxy9.start(Unknown Source)
at org.jboss.ejb.EJBDeployer.start(EJBDeployer.java:395)
at org.jboss.deployment.MainDeployer.start(MainDeployer.java:807)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:621)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:585)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
at org.jboss.util.jmx.MBeanProxy.invoke(MBeanProxy.java:174)
at $Proxy4.deploy(Unknown Source)
at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:435)
at org.jboss.deployment.scanner.URLDeploymentScanner.scanDirectory(URLDeploymentScanner.java:656)
at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:507)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:212)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.loop(AbstractDeploymentScanner.java:225)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.run(AbstractDeploymentScanner.java:202)
+ nested throwable:
java.sql.SQLException: Syntax error or access violation: You have an error in your SQL syntax near ' operator (usergroup, operator), ADD CONSTRAINT fk_applicati
on_usergroup_applica' at line 1
at com.mysql.jdbc.MysqlIO.sendCommand(Unknown Source)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(Unknown Source)
at com.mysql.jdbc.MysqlIO.sqlQuery(Unknown Source)
at com.mysql.jdbc.Connection.execSQL(Unknown Source)
at com.mysql.jdbc.Connection.execSQL(Unknown Source)
at com.mysql.jdbc.Statement.executeUpdate(Unknown Source)
at com.mysql.jdbc.jdbc2.Statement.executeUpdate(Unknown Source)
at org.jboss.resource.adapter.jdbc.local.LocalStatement.executeUpdate(LocalStatement.java:231)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.addForeignKeyConstraint(JDBCStartCommand.java:375)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.addForeignKeyConstraint(JDBCStartCommand.java:295)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.execute(JDBCStartCommand.java:127)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.startStoreManager(JDBCStoreManager.java:457)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.start(JDBCStoreManager.java:369)
at org.jboss.ejb.plugins.CMPPersistenceManager.start(CMPPersistenceManager.java:198)
at org.jboss.ejb.EntityContainer.start(EntityContainer.java:376)
at org.jboss.ejb.Container.invoke(Container.java:756)
at org.jboss.ejb.EntityContainer.invoke(EntityContainer.java:1058)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:978)
at $Proxy5.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:398)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
at org.jboss.util.jmx.MBeanProxy.invoke(MBeanProxy.java:174)
at $Proxy163.start(Unknown Source)
at org.jboss.ejb.EjbModule.startService(EjbModule.java:430)
at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:165)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:978)
at $Proxy5.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:398)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
at org.jboss.util.jmx.MBeanProxy.invoke(MBeanProxy.java:174)
at $Proxy9.start(Unknown Source)
at org.jboss.ejb.EJBDeployer.start(EJBDeployer.java:395)
at org.jboss.deployment.MainDeployer.start(MainDeployer.java:807)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:621)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:585)
at java.lang.reflect.Method.invoke(Native Method)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
at org.jboss.util.jmx.MBeanProxy.invoke(MBeanProxy.java:174)
at $Proxy4.deploy(Unknown Source)
at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:435)
at org.jboss.deployment.scanner.URLDeploymentScanner.scanDirectory(URLDeploymentScanner.java:656)
at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:507)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:212)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.loop(AbstractDeploymentScanner.java:225)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.run(AbstractDeploymentScanner.java:202)
18:18:53,375 INFO [EjbModule] Started
18:18:53,375 DEBUG [EJBDeployer] Deployed: file:/C:/Java/jboss-3.0.4_tomcat-4.1.12/server/default/deploy/Administration.jar
18:18:53,375 DEBUG [MainDeployer] End deployment start on package: Administration.jar
18:18:53,375 INFO [MainDeployer] Deployed package: file:/C:/Java/jboss-3.0.4_tomcat-4.1.12/server/default/deploy/Administration.jar
18:18:53,375 DEBUG [URLDeploymentScanner] Watch URL for: file:/C:/Java/jboss-3.0.4_tomcat-4.1.12/server/default/deploy/Administration.jar -> file:/C:/Java/jboss
-3.0.4_tomcat-4.1.12/server/default/deploy/Administration.jar
18:18:53,385 ERROR [URLDeploymentScanner] MBeanException: Exception in MBean operation 'checkIncompleteDeployments()'
Cause: Incomplete Deployment listing:
Packages waiting for a deployer:

Incompletely deployed packages:

MBeans waiting for classes:

MBeans waiting for other MBeans:
[ObjectName: jboss.j2ee:jndiName=osmadmin/UserGroupHome,service=EJB
state: FAILED
I Depend On:
Depends On Me: org.jboss.deployment.DeploymentException: Error while adding foreign key constraint; - nested throwable: (java.sql.SQLException: Syntax error or
access violation: You have an error in your SQL syntax near ' operator (usergroup, operator), ADD CONSTRAINT fk_application_usergroup_applica' at line 1)]

=========================================================
It seems that the syntax ADD INDEX ind_usergroup, operator (usergroup, operator) is not good !!!

The MySQL manual :
ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
or ADD [COLUMN] (create_definition, create_definition,...)
or ADD INDEX [index_name] (index_col_name,...)
or ADD PRIMARY KEY (index_col_name,...)
or ADD UNIQUE [index_name] (index_col_name,...)
or ADD FULLTEXT [index_name] (index_col_name,...)
or ADD [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
[reference_definition]
......

Can you help me ?

1. Re: foreign key constraint failed

mik1 Feb 7, 2003 8:24 AM (in response to mik1)

It comes from standardjbosscmp-jdbc.xml :

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

The name of the index becomes :
"ind_field1, field2"

How can I correct that ?
Actions