Hide Forgot
Date of First Response: 2009-05-28 10:32:34 project_key: SOA See JBPM-2143 If this issue will be fixed in CP then it is necessary to provide update scripts to remove duplicates from db schemas created by this version
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.