13 Replies Latest reply on Oct 6, 2004 9:10 AM by mbeyer73

    Index already exists in statement

    sbocquet Newbie

      Hi,

      I use JBoss IDE (XDoclet) to generate my CMP EJBs, and HSQQLDB as database.
      I have 2 EJBs, Groups and Roles. A groups can have many roles and a role can have many groups. There is a relation table between them, GroupsRolesLink.

      The first time I deploy them, the container creates the tables, the contraints and the indexes correctly.
      The second time I deploy them, I have the following error

      18:16:54,811 ERROR [EntityContainer] Starting failed
      org.jboss.deployment.DeploymentException: Error while creating table; - nested throwable: (java.sql.SQLException: Index already exists in statement [CREATE INDEX roleId_idx0 ON GroupsRolesLink (roleId)])
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createIndex(JDBCStartCommand.java:305)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createCMRIndex(JDBCStartCommand.java:527)
      ...
      


      Here are the descriptors generated by XDoclet.

      ejb-jar.xml
      <entity >
       <description><![CDATA[Entity bean that represents a role.]]></description>
       <display-name>Roles Entity</display-name>
      
       <ejb-name>Roles</ejb-name>
      
       <home>org.dyndns.bet4fun.interfaces.RolesHome</home>
       <remote>org.dyndns.bet4fun.interfaces.Roles</remote>
       <local-home>org.dyndns.bet4fun.interfaces.RolesLocalHome</local-home>
       <local>org.dyndns.bet4fun.interfaces.RolesLocal</local>
      
       <ejb-class>org.dyndns.bet4fun.entity.RolesCMP</ejb-class>
       <persistence-type>Container</persistence-type>
       <prim-key-class>org.dyndns.bet4fun.interfaces.RolesPK</prim-key-class>
       <reentrant>False</reentrant>
       <cmp-version>2.x</cmp-version>
       <abstract-schema-name>Roles</abstract-schema-name>
       <cmp-field >
       <description><![CDATA[Get the Id of the role]]></description>
       <field-name>roleId</field-name>
       </cmp-field>
       <cmp-field >
       <description><![CDATA[Get the name of the role]]></description>
       <field-name>name</field-name>
       </cmp-field>
       <cmp-field >
       <description><![CDATA[Get the description of the role]]></description>
       <field-name>description</field-name>
       </cmp-field>
      
       <query>
       <query-method>
       <method-name>findAll</method-name>
       <method-params>
       </method-params>
       </query-method>
       <ejb-ql><![CDATA[SELECT OBJECT(R) FROM Roles AS R]]></ejb-ql>
       </query>
       <!-- Write a file named ejb-finders-RolesBean.xml if you want to define extra finders. -->
       </entity>
      
       <entity >
       <description><![CDATA[Entity bean that represents a Group.]]></description>
       <display-name>Groups Entity</display-name>
      
       <ejb-name>Groups</ejb-name>
      
       <home>org.dyndns.bet4fun.interfaces.GroupsHome</home>
       <remote>org.dyndns.bet4fun.interfaces.Groups</remote>
       <local-home>org.dyndns.bet4fun.interfaces.GroupsLocalHome</local-home>
       <local>org.dyndns.bet4fun.interfaces.GroupsLocal</local>
      
       <ejb-class>org.dyndns.bet4fun.entity.GroupsCMP</ejb-class>
       <persistence-type>Container</persistence-type>
       <prim-key-class>org.dyndns.bet4fun.interfaces.GroupsPK</prim-key-class>
       <reentrant>False</reentrant>
       <cmp-version>2.x</cmp-version>
       <abstract-schema-name>Groups</abstract-schema-name>
       <cmp-field >
       <description><![CDATA[Get the Id of the group]]></description>
       <field-name>groupId</field-name>
       </cmp-field>
       <cmp-field >
       <description><![CDATA[Get the name of the group]]></description>
       <field-name>name</field-name>
       </cmp-field>
       <cmp-field >
       <description><![CDATA[Get the description of the group]]></description>
       <field-name>description</field-name>
       </cmp-field>
      
       <query>
       <query-method>
       <method-name>findAll</method-name>
       <method-params>
       </method-params>
       </query-method>
       <ejb-ql><![CDATA[SELECT OBJECT(G) FROM Groups AS G]]></ejb-ql>
       </query>
       <!-- Write a file named ejb-finders-GroupsBean.xml if you want to define extra finders. -->
       </entity>
      ...
       </relationships>
       <ejb-relation >
       <ejb-relation-name>GroupsRolesRelation</ejb-relation-name>
      
       <ejb-relationship-role >
       <ejb-relationship-role-name>RolesHasGroups</ejb-relationship-role-name>
       <multiplicity>Many</multiplicity>
       <relationship-role-source >
       <ejb-name>Roles</ejb-name>
       </relationship-role-source>
       <cmr-field >
       <cmr-field-name>groups</cmr-field-name>
       <cmr-field-type>java.util.Collection</cmr-field-type>
       </cmr-field>
       </ejb-relationship-role>
      
       <ejb-relationship-role >
       <ejb-relationship-role-name>GroupsHasRoles</ejb-relationship-role-name>
       <multiplicity>Many</multiplicity>
       <relationship-role-source >
       <ejb-name>Groups</ejb-name>
       </relationship-role-source>
       <cmr-field >
       <cmr-field-name>roles</cmr-field-name>
       <cmr-field-type>java.util.Collection</cmr-field-type>
       </cmr-field>
       </ejb-relationship-role>
      
       </ejb-relation>
       </relationships>
      


      and jbosscmp-jdbc.xml
      ...
       <relationships>
       <ejb-relation>
       <ejb-relation-name>GroupsRolesRelation</ejb-relation-name>
       <relation-table-mapping>
       <table-name>GroupsRolesLink</table-name>
       <create-table>true</create-table>
       </relation-table-mapping>
      
       <ejb-relationship-role>
       <ejb-relationship-role-name>RolesHasGroups</ejb-relationship-role-name>
       <fk-constraint>true</fk-constraint>
       <key-fields>
       <key-field>
       <field-name>roleId</field-name>
       <column-name>roleId</column-name>
       </key-field>
       </key-fields>
      
       </ejb-relationship-role>
       <ejb-relationship-role>
       <ejb-relationship-role-name>GroupsHasRoles</ejb-relationship-role-name>
       <fk-constraint>true</fk-constraint>
       <key-fields>
       <key-field>
       <field-name>groupId</field-name>
       <column-name>groupId</column-name>
       </key-field>
       </key-fields>
      
       </ejb-relationship-role>
       </ejb-relation>
       </relationships>
      


      As there is an error on the deployment, my EJB is not deployed !
      I don't undestand why this woks the first time and not the second one !
      Can someone explains to me how JBoss works on that case, and what is the solution ?

      Thanks,

        • 1. Re: Index already exists in statement
          Erik Sliman Apprentice

          It appears as though it creates the index the first time, but can't detect that it already exists the next time, since it's trying to create it again.

          I would try another database, such as MySQL, to try to determine if it's a JDBC problem. If it works in MySQL or another database, then it is most likely a JDBC problem with HSQLDB.

          If it iturns out to be a problem in other databases, and thus unlikely to be a specific JDBC problem, can you show the entire trace instead of just the first 3 lines?

          • 2. Re: Index already exists in statement
            sbocquet Newbie

            Hi,

            I'm trying to install mySQL to do some testing...

            Here is the complète log with HSQLDB

            2004-05-31 00:20:36,048 WARN [org.jboss.system.ServiceController] Problem starting service jboss.j2ee:jndiName=ejb/bet4fun/Evenments,service=EJB
            org.jboss.deployment.DeploymentException: Error while creating table; - nested throwable: (java.sql.SQLException: Index already exists in statement [CREATE INDEX roleId_idx0 ON GroupsRolesLink (roleId)])
             at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createIndex(JDBCStartCommand.java:305)
             at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createCMRIndex(JDBCStartCommand.java:527)
             at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.execute(JDBCStartCommand.java:150)
             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.GeneratedMethodAccessor37.invoke(Unknown Source)
             at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
             at java.lang.reflect.Method.invoke(Unknown Source)
             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.GeneratedMethodAccessor8.invoke(Unknown Source)
             at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
             at java.lang.reflect.Method.invoke(Unknown Source)
             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.GeneratedMethodAccessor37.invoke(Unknown Source)
             at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
             at java.lang.reflect.Method.invoke(Unknown Source)
             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.GeneratedMethodAccessor8.invoke(Unknown Source)
             at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
             at java.lang.reflect.Method.invoke(Unknown Source)
             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.GeneratedMethodAccessor29.invoke(Unknown Source)
             at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
             at java.lang.reflect.Method.invoke(Unknown Source)
             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.startService(AbstractDeploymentScanner.java:274)
             at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
             at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
             at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
             at java.lang.reflect.Method.invoke(Unknown Source)
             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 $Proxy0.start(Unknown Source)
             at org.jboss.system.ServiceController.start(ServiceController.java:394)
             at sun.reflect.GeneratedMethodAccessor8.invoke(Unknown Source)
             at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
             at java.lang.reflect.Method.invoke(Unknown Source)
             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 $Proxy4.start(Unknown Source)
             at org.jboss.deployment.SARDeployer.start(SARDeployer.java:226)
             at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
             at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:642)
             at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:605)
             at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:589)
             at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
             at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
             at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
             at java.lang.reflect.Method.invoke(Unknown Source)
             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 $Proxy5.deploy(Unknown Source)
             at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:384)
             at org.jboss.system.server.ServerImpl.start(ServerImpl.java:291)
             at org.jboss.Main.boot(Main.java:150)
             at org.jboss.Main$1.run(Main.java:395)
             at java.lang.Thread.run(Unknown Source)
            Caused by: java.sql.SQLException: Index already exists in statement [CREATE INDEX roleId_idx0 ON GroupsRolesLink (roleId)]
             at org.hsqldb.Trace.getError(Unknown Source)
             at org.hsqldb.Result.<init>(Unknown Source)
             at org.hsqldb.jdbcConnection.executeHSQL(Unknown Source)
             at org.hsqldb.jdbcConnection.execute(Unknown Source)
             at org.hsqldb.jdbcStatement.fetchResult(Unknown Source)
             at org.hsqldb.jdbcStatement.executeUpdate(Unknown Source)
             at org.jboss.resource.adapter.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:262)
             at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createIndex(JDBCStartCommand.java:292)
             ... 89 more
            


            • 3. Re: Index already exists in statement
              Alexey Loubyansky Master

              It seems like a JBossCMP bug. We don't drop index at undeploy. The code should be in org.jboss.ejb.plugins.cmp.jdbc.JDBCStopCommand.

              • 4. Re: Index already exists in statement
                sbocquet Newbie

                Hi,

                I'm surprise that JBoss drop the indexex at undeploy time !... I was thinking that if the table does not exist, it creates it, with the constraints and indexes, and if the table is already created, it just detects it, and uses it as it is.

                • 5. Re: Index already exists in statement
                  Joachim Van der Auwera Expert

                  No, JBoss does not drop indexes.

                  It creates indexes on create-table.
                  It checks whether indexes exists and creates if they don't exist with alter-table.

                  So my guess is that you have alter-table set and that hsqldb does not implement the JDBC metadata calls properly.

                  Joachim

                  • 6. Re: Index already exists in statement
                    sbocquet Newbie

                    Hi,

                    Perhaps, but It deploys the same ear two times... with no change in it. Just start and stop the server two times.
                    Can someone tests my ear if I send it to him, in order to see if it's a JBoss bug ?
                    I'm trying to debug JDBCStartCommand.java to see what it does, but it's quite difficult.... I'm not familiar with JbossCMP ;-)))

                    • 7. Re: Index already exists in statement
                      Joachim Van der Auwera Expert

                      Can you publish the "defaults" section in your jbosscmp-jdbc.xml file?

                      Joachim

                      • 8. Re: Index already exists in statement
                        sbocquet Newbie

                        Here is it

                         <defaults>
                         <datasource>java:/DefaultDS</datasource>
                         <datasource-mapping>Hypersonic SQL</datasource-mapping>
                        
                         <create-table>true</create-table>
                         <remove-table>false</remove-table>
                         <read-only>false</read-only>
                         <read-time-out>300000</read-time-out>
                         <row-locking>false</row-locking>
                         <pk-constraint>true</pk-constraint>
                         <fk-constraint>false</fk-constraint>
                         <preferred-relation-mapping>foreign-key</preferred-relation-mapping>
                         <read-ahead>
                         <strategy>on-load</strategy>
                         <page-size>1000</page-size>
                         <eager-load-group>*</eager-load-group>
                         </read-ahead>
                         <list-cache-max>1000</list-cache-max>
                        
                         <unknown-pk>
                         <key-generator-factory>UUIDKeyGeneratorFactory</key-generator-factory>
                         <unknown-pk-class>java.lang.String</unknown-pk-class>
                         <jdbc-type>VARCHAR</jdbc-type>
                         <sql-type>VARCHAR(32)</sql-type>
                         </unknown-pk>
                        
                         <entity-command name="default"/>
                        
                         </defaults>
                        


                        the <fk-constraint> is set to false, but I think it is overridden by the EJB descriptor value, which is set to true.

                        • 9. Re: Index already exists in statement
                          Joachim Van der Auwera Expert

                          Ok, send me your ear and I will have a look, address is joachim at triathlon98.com

                          Joachim

                          • 10. Re: Index already exists in statement
                            sbocquet Newbie

                            Hi,

                            In JDBCStartCommand.java...
                            It seems that JBossCMP doesn't detects that there is an index already created. It detects that the tables are created, but still try to create indexes. Can't understand why !

                            • 11. Re: Index already exists in statement
                              sbocquet Newbie

                              Hi,

                              Just try with mySQL database, and have the same error :

                              org.jboss.deployment.DeploymentException: Error while creating table; - nested throwable: (java.sql.SQLException: Invalid argument value, message from server: "Duplicate key name 'roleId_idx0'")
                              


                              So I think that this is a JBoss problem, and not a database problem...

                              • 12. Re: Index already exists in statement
                                sbocquet Newbie

                                All is about the @jboss.relation dbindex="xxx" tag for the relation tables.
                                It works with a mySQL database when you don't have the @jboss.relation
                                dbindex="true" tag set with XDoclet for the 2 fields of the relation
                                (GROUPS & ROLES here... and USERS & GROUPS).
                                Can't remenber what this tag generate in ejb-jar.xml and jbosscmp-jdbc.xml, but this tag MUST not exist for those EJBs. If it's set to "false", the bug appends, because the test done is "!=null"
                                So, it doesn't works when the flag is set to "true" or "false", and works without the tag !!!

                                This is normal. If you look at jbosscmp-jdbc_3_2.dtd (can be found in
                                the docs directory), it says :

                                <!--
                                If present, the server will try to create an index on the column in the database called _index
                                -->
                                <!ELEMENT dbindex EMPTY>

                                So the value is irrelevant and the presence of the tag is what counts.

                                The only thing that I can spot as being wrong is that the createCMRIndex
                                call in JDBCStartCommand should probably be inside the test
                                "if(relationMetaData.getCreateTable())". However, this code is only
                                executed if the relation table did not exist. Which kind of puts us back
                                at the drawing board.

                                I have also tried comparing the xdoclet relation definition you use with
                                what I normally use and the only differences I see is that I normally
                                use @jboss.target-relation and don't have a create-table anywhere (I
                                have this defined in my defaults section, in the
                                jbosscmp-jdbc-defaults.xml merge file.

                                • 13. Re: Index already exists in statement
                                  mbeyer73 Newbie

                                  I can't find this issue in the bug database. Don't you consider it as a bug?