1 2 3 Previous Next 30 Replies Latest reply on Oct 19, 2009 8:40 AM by Michael Wohlfart

    Problems with indexes in jbpm.history.hbm.xml

    Bogdan Jarnea Newbie

      I think there is a problem with the way indexes are defined in hibernate for history tables (jbpm.history.hbm.xml).

      Example:

      <class name="HistoryProcessInstanceImpl" table="JBPM4_HIST_PROCINST">
       <id name="dbid" column="DBID_">
       <generator class="assigned" />
       </id>
      
       ...
      
       <set name="details"
       cascade="all">
       <key foreign-key="FK_HDETAIL_HPROCI">
       <column name="HPROCI_" index="IDX_HDETAIL_HPROCI" />
       </key>
       <one-to-many class="HistoryDetailImpl" />
       </set>
      
       </class>
      


      If you go down in the file you will find the table for HistoryDetailImpl with the many-to-one definition:

      <class name="HistoryDetailImpl" table="JBPM4_HIST_DETAIL">
       ...
      
       <many-to-one name="historyProcessInstance"
       class="HistoryProcessInstanceImpl"
       column="HPROCI_"
       foreign-key="FK_HDET_HPROCI"
       index="IDX_HDET_HPROCI" />
       <property name="historyProcessInstanceIndex" column="HPROCIIDX_" />
      
       ...
      


      So there are two name for the same index (on the same column HPROCI_): IDX_HDETAIL_HPROCI (from the inverse one-to-many relation) and IDX_HDET_HPROCI (from the direct many-to-one relation).

      That causes hbm2ddl tool to create a faulty sql (in the jbpm-4.0 ditribution see db/jbpm.oracle.create.sql):

      create index IDX_HDET_HACTI on JBPM4_HIST_DETAIL (HACTI_);
      
       create index IDX_HDET_HPROCI on JBPM4_HIST_DETAIL (HPROCI_);
      
       create index IDX_HDETAIL_HACTI on JBPM4_HIST_DETAIL (HACTI_);
      
       create index IDX_HDETAIL_HVAR on JBPM4_HIST_DETAIL (HVAR_);
      
       create index IDX_HDETAIL_HTASK on JBPM4_HIST_DETAIL (HTASK_);
      
       create index IDX_HDETAIL_HPROCI on JBPM4_HIST_DETAIL (HPROCI_);
      
       create index IDX_HDET_HVAR on JBPM4_HIST_DETAIL (HVAR_);
      
       create index IDX_HDET_HTASK on JBPM4_HIST_DETAIL (HTASK_);


      Oracle will throw an error about duplicate index on the same column for columns HACTI_, HVAR_, HTASK_. For whatever reason MySql and HSQLDB work fine with this indexes but oracle has a problem.

        • 1. Re: Problems with indexes in jbpm.history.hbm.xml
          Ronald van Kuijk Master

          Can it be driver related as well? Or oracle version? Because as far as I know, Oracle is in the QA round.

          • 2. Re: Problems with indexes in jbpm.history.hbm.xml
            Shekhar Vemuri Newbie

            I get the same error when running the script for ORacle.


            Error starting at line 305 in command:
            create index IDX_HDETAIL_HACTI on JBPM4_HIST_DETAIL (HACTI_)
            Error at Command Line:305 Column:53
            Error report:
            SQL Error: ORA-01408: such column list already indexed
            01408. 00000 - "such column list already indexed"
            *Cause:
            *Action:
            create index succeeded.
            create index succeeded.

            Error starting at line 311 in command:
            create index IDX_HDETAIL_HPROCI on JBPM4_HIST_DETAIL (HPROCI_)
            Error at Command Line:311 Column:54
            Error report:
            SQL Error: ORA-01408: such column list already indexed
            01408. 00000 - "such column list already indexed"
            *Cause:
            *Action:

            Error starting at line 313 in command:
            create index IDX_HDET_HVAR on JBPM4_HIST_DETAIL (HVAR_)
            Error at Command Line:313 Column:49
            Error report:
            SQL Error: ORA-01408: such column list already indexed
            01408. 00000 - "such column list already indexed"
            *Cause:
            *Action:

            Error starting at line 315 in command:
            create index IDX_HDET_HTASK on JBPM4_HIST_DETAIL (HTASK_)
            Error at Command Line:315 Column:50
            Error report:
            SQL Error: ORA-01408: such column list already indexed
            01408. 00000 - "such column list already indexed"
            *Cause:
            *Action:

            I will report a JIRA

            • 3. Re: Problems with indexes in jbpm.history.hbm.xml
              Bogdan Jarnea Newbie

               

              "kukeltje" wrote:
              Can it be driver related as well? Or oracle version? Because as far as I know, Oracle is in the QA round.


              It doesn't have anything to do with oracle version or driver. As I already mentioned I'm almost sure that hibernate mappings for JBPM4_HIST_DETAIL table are not correct. Indexes are index="IDX_HDET_..." instead of "IDX_HDETAIL_..." (as they are defined for the other tables) and that causes hibernate to generate two indexex for the same column.


              • 4. Re: Problems with indexes in jbpm.history.hbm.xml
                Ronald van Kuijk Master

                Yes, I understand the mapping is 'incorrect' and that you mentioned that. But since Oracle is in the QA round and works there, it *might* have something to do with differences in drivers (e.g. silently ignoring the error). But from what I recently understood, the tables are generated by hibernate in the tests, the already generated ddl is not used. Maybe you can see if that helps, at least until the 'real' issue is fixed

                • 6. Re: Problems with indexes in jbpm.history.hbm.xml
                  Ha Wa Newbie

                   

                  "kukeltje" wrote:
                  http://www.jboss.org/index.html?module=bb&op=viewtopic&p=4247916#4247916


                  I don't see the error in the post referred by the link above, but I also see the problem with indexes already indexed when running jbpm.oracle.create.sql:

                  create.jbpm.schema:
                   [sql] Executing resource: C:\SEAM\jbpm-4.0\db\schema.scripts\jbpm.oracle.create.sql
                   [sql] Failed to execute: create index IDX_HDETAIL_HACTI on JBPM4_HIST_DETAIL (HACTI_)
                   [sql] java.sql.SQLException: ORA-01408: such column list already indexed
                   [sql] Failed to execute: create index IDX_HDETAIL_HPROCI on JBPM4_HIST_DETAIL (HPROCI_)
                   [sql] java.sql.SQLException: ORA-01408: such column list already indexed
                   [sql] Failed to execute: create index IDX_HDET_HVAR on JBPM4_HIST_DETAIL (HVAR_)
                   [sql] java.sql.SQLException: ORA-01408: such column list already indexed
                   [sql] Failed to execute: create index IDX_HDET_HTASK on JBPM4_HIST_DETAIL (HTASK_)
                   [sql] java.sql.SQLException: ORA-01408: such column list already indexed
                   [sql] 76 of 80 SQL statements executed successfully
                  


                  Did anyone ever find a way to solve or work around this problem? which index is supposed to be right: IDX_HDET or IDEX_HDETAIL?

                  • 7. Re: Problems with indexes in jbpm.history.hbm.xml
                    Ronald van Kuijk Master

                    The reason I posted this link is that it was not a problem in jBPM with this other poster. jBPM4 has an oracle db in the QA run and we do not see this problem. So maybe in your case it is also db related

                    • 8. Re: Problems with indexes in jbpm.history.hbm.xml
                      Ha Wa Newbie

                      It's weird because my Oracle DB worked just fine with jBPM 4.0 CR1 but not with jBPM 4.0 GA...

                      I also uninstalled my DB and installed a new one and the problem still persists.

                      • 9. Re: Problems with indexes in jbpm.history.hbm.xml
                        Michael Wohlfart Expert

                        hmm, I get a similar error for the indexes

                        23:35:13,342 ERROR [SchemaExport] Unsuccessful: create index IDX_HDETAIL_HVAR on JBPM4_HIST_DETAIL (HVAR_)
                        23:35:13,342 ERROR [SchemaExport] ORA-01408: such column list already indexed
                        
                        23:35:13,348 ERROR [SchemaExport] Unsuccessful: create index IDX_HDETAIL_HTASK on JBPM4_HIST_DETAIL (HTASK_)
                        23:35:13,348 ERROR [SchemaExport] ORA-01408: such column list already indexed
                        
                        23:35:13,349 ERROR [SchemaExport] Unsuccessful: create index IDX_HDETAIL_HACTI on JBPM4_HIST_DETAIL (HACTI_)
                        23:35:13,349 ERROR [SchemaExport] ORA-01408: such column list already indexed
                        
                        23:35:13,351 ERROR [SchemaExport] Unsuccessful: create index IDX_HDETAIL_HPROCI on JBPM4_HIST_DETAIL (HPROCI_)
                        23:35:13,351 ERROR [SchemaExport] ORA-01408: such column list already indexed
                        
                        23:35:24,049 ERROR [SchemaExport] Unsuccessful: create index IDX_HDETAIL_HVAR on JBPM4_HIST_DETAIL (HVAR_)
                        23:35:24,049 ERROR [SchemaExport] ORA-01408: such column list already indexed
                        
                        23:35:24,055 ERROR [SchemaExport] Unsuccessful: create index IDX_HDETAIL_HTASK on JBPM4_HIST_DETAIL (HTASK_)
                        23:35:24,055 ERROR [SchemaExport] ORA-01408: such column list already indexed
                        
                        23:35:24,056 ERROR [SchemaExport] Unsuccessful: create index IDX_HDETAIL_HACTI on JBPM4_HIST_DETAIL (HACTI_)
                        23:35:24,056 ERROR [SchemaExport] ORA-01408: such column list already indexed
                        
                        23:35:24,058 ERROR [SchemaExport] Unsuccessful: create index IDX_HDETAIL_HPROCI on JBPM4_HIST_DETAIL (HPROCI_)
                        23:35:24,058 ERROR [SchemaExport] ORA-01408: such column list already indexed
                        


                        • 11. Re: Problems with indexes in jbpm.history.hbm.xml
                          Michael Wohlfart Expert

                           

                          "kukeltje" wrote:
                          cool


                          yeah, but please don't tell anyone,
                          we want to keep this cool feature don't we?

                          • 12. Re: Problems with indexes in jbpm.history.hbm.xml
                            Ronald van Kuijk Master

                            Indeed, and as long as noone comes up with more detail, thos hot thing is going to stay, since we cannot reproduce, people listen not that carefully, some don't know how to troubleshoot or use forums

                            But i like your response, seriously

                            • 13. Re: Problems with indexes in jbpm.history.hbm.xml
                              Michael Wohlfart Expert

                               

                              Oracle JDBC Driver version - "11.1.0.6.0-Production+"
                              Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
                              Oracle Database 10g Express Edition Release 10.2.0.1.0
                              


                              I patched jbpm.history.hbm.xml like hwa suggested:

                              43c43
                              < <column name="HPROCI_" index="IDX_HDET_HPROCI" />
                              ---
                              > <column name="HPROCI_" index="IDX_HDETAIL_HPROCI" />
                              76c76
                              < <column name="HACTI_" index="IDX_HDET_HACTI" />
                              ---
                              > <column name="HACTI_" index="IDX_HDETAIL_HACTI" />
                              115c115
                              < <column name="HTASK_" index="IDX_HDET_HTASK" />
                              ---
                              > <column name="HTASK_" index="IDX_HDETAIL_HTASK" />
                              162c162
                              < <column name="HVAR_" index="IDX_HDET_HVAR" />
                              ---
                              > <column name="HVAR_" index="IDX_HDETAIL_HVAR" />
                              


                              this did the trick for me but I just started to replace a jbpm3 setup with jbpm4 and have no idea what side effects this changes may have :-/

                              • 14. Re: Problems with indexes in jbpm.history.hbm.xml
                                Ronald van Kuijk Master

                                First helpfull info... Afaik, jbpm is tested with a 10.x.y.z. Driver. Can you check if that makes a difference. If it works then without the 'patch'. If so, reopen the jira issue and add this additional info

                                1 2 3 Previous Next