Bug 778821 (SOA-1271)

Link: Added: This issue depends JBPM-2143

We will defer this to CP03.

Resolving platform issue as the project peer JBPM-2143 has been resolved.

Please review the following draft text which has been added to the Resolved Issues section of the Release Notes. Also, what was the impact of having double indices?  Did it have a performance impact or was it confusing the database?  Please supply that info and I will add it to the followng note.:


https://jira.jboss.org/jira/browse/JBPM-2143

    jBPM was creating two indices for each foreign key column on MySQL databases. This problem
    has been rectified by the addition of customised MYSQLDialect statements for the addition
    of foreign keys. When foreign keys are added by the use of these statements, an index is not
    created, preventing the duplication issue from arising anymore.

David, here is an updated text for the release notes that addresses your questions.

<<
The MySQL schema creation script that jBPM versions prior to 3.2.9 contained statements that resulted in the duplicate creation of indexes on table columns constrained by a foreign key. The statements below illustrate the problem:

create index IDX_PROCIN_PROCDEF on JBPM_PROCESSINSTANCE (PROCESSDEFINITION_);
alter table JBPM_PROCESSINSTANCE add index FK_PROCIN_PROCDEF (PROCESSDEFINITION_), add constraint FK_PROCIN_PROCDEF foreign key (PROCESSDEFINITION_) references JBPM_PROCESSDEFINITION (ID_);

When executed, these statements create two separate indices IDX_PROCIN_PROCDEF and FK_PROCIN_PROCDEF on the JBPM_PROCESSINSTANCE.PROCESSDEFINITION_ column. Both indexes contain the same data, but maintaining the redundant copy costs the DBMS additional effort.

The standard MySQLDialect provided with Hibernate adds an index clause to every foreign key statement it generates. jBPM provides a slight MySQLDialect variation that does not add such index clause. In lieu of the "alter table..." statement presented earlier, the custom dialect yields the next statement:

alter table JBPM_PROCESSINSTANCE add constraint FK_PROCIN_PROCDEF foreign key (PROCESSDEFINITION_) references JBPM_PROCESSDEFINITION (ID_);
>>

In the issue description Jiri mentions that it is necessary to provide an update script to remove duplicates from db schemas created by older versions of the mysql script. The update script should be referenced from the release notes as well.

Hello Alejandro, 

Many thanks for the detailed feedback.  Much appreciated. The Release Note now reads as per the below:



https://jira.jboss.org/jira/browse/JBPM-2143

    The MySQL schema creation script used by jBPM versions prior to 3.2.9 contained statements
    that resulted in the duplicate creation of indices on table columns constrained by a foreign
    key. Both indices contained the same data but maintaining the redundant copy resulted in the
    DBMS doing additional work. This problem has been rectified by the addition of a customized
    MYSQLDialect. The standard MYSQLDialect provided with Hibernate adds an index clause to
    every foreign key statement it generates. jBPM provides a slight variant of MYSQLDialect that
    does not add such an index clause. As a result, the duplicates are no longer created.

    Note that an update script is being provided to remove duplicate indices from database schemas
    created by older versions of the MySQL script.

The script to remove duplicated indices was not provided.

I have removed the para about the scripts at this stage.  It can be reinstated if they are provided before the release.

Release Notes Docs Status: Added: Not Required
Writer: Added: dlesage

Release Notes Docs Status: Removed: Not Required Added: Documented as Resolved Issue
Release Notes Text: Added: https://jira.jboss.org/jira/browse/JBPM-2143

The MySQL schema creation script used by jBPM versions prior to 3.2.9 contained statements
that resulted in the duplicate creation of indices on table columns constrained by a foreign
key. Both indices contained the same data but maintaining the redundant copy resulted in the
DBMS doing additional work. This problem has been rectified by the addition of a customized
MYSQLDialect. The standard MYSQLDialect provided with Hibernate adds an index clause to
every foreign key statement it generates. jBPM provides a slight variant of MYSQLDialect that
does not add such an index clause. As a result, the duplicates are no longer created.

Release Notes Docs Status: Removed: Documented as Resolved Issue Added: Documented as Known Issue
Release Notes Text: Removed: https://jira.jboss.org/jira/browse/JBPM-2143

The MySQL schema creation script used by jBPM versions prior to 3.2.9 contained statements
that resulted in the duplicate creation of indices on table columns constrained by a foreign
key. Both indices contained the same data but maintaining the redundant copy resulted in the
DBMS doing additional work. This problem has been rectified by the addition of a customized
MYSQLDialect. The standard MYSQLDialect provided with Hibernate adds an index clause to
every foreign key statement it generates. jBPM provides a slight variant of MYSQLDialect that
does not add such an index clause. As a result, the duplicates are no longer created. Added: https://jira.jboss.org/jira/browse/JBPM-2143

The MySQL schema creation script used by jBPM versions prior to 3.2.9 contains statements
that result in the creation of duplicate indices on table columns constrained by a foreign
key. Both indices contain the same data but maintaining the redundant copy means the 
DBMS has to do additional work. 

In the release notes for 4.3 CP05 - this issue is described as:

https://jira.jboss.org/jira/browse/JBPM-2143
    The MySQL schema creation script used by jBPM versions prior to 3.2.9 contains statements that result in the creation of duplicate indices on table columns constrained by a foreign key. Both indices contain the same data but maintaining the redundant copy means the DBMS has to do additional work.