Description of problem: Platform issue for JBPM-3589: In the EVENTTABLES table, the INSTANCEID column isn't indexed. As the column is a foreign key to the ProcessInstanceInfo table, and as unindexed foreign key columns are a common cause of deadlock issues in Oracle, an index should be added: CREATE INDEX IDX_EVENTTYPES ON EVENTTYPES (INSTANCEID);
Marco Rietveld <marco.rietveld> made a comment on jira JBPM-3589 The following is knowledge associated with this issue that is important to know for people who end up dealing with this later: * Hibernate.hbm2ddl.import_files would be a valid option.. _if_ it were available in Hib (core) 3.3. Unfortunately, it's not available until Hib (core) 3.6. Even we did go with that option, that would probably mean creating an "import_file" with the "create index" statement for all of the supported database. Regardless, this would be the best option. * Unfortunately, modifying the {{ProcessInstanceInfo.hbm.xml}} file so that an index on the EventTypes would be created (via the hbm configuration) is not an option. The DTD ([here|http://www.jboss.org/dtd/hibernate/hibernate-mapping-3.0.dtd]) for the hbm.xml syntax doesn't allow setting an index on a CollectionOfElements/set. See under line 562 ({{<!ELEMENT set}}) where no index element or attribute is mentioned. Wait! Maybe we could redefine the .eventTypes field as a List? In which case we could put an index on it -- and possible keep the same table structure! Trying that now..
Marco Rietveld <marco.rietveld> made a comment on jira JBPM-3589 AWESOME! Available in Hibernate 3.3: database-object and dialect-scope: http://old.nabble.com/create-index-through-hibernate-td18204775.html And it works! {noformat} <database-object> <create>CREATE INDEX IDX_EVENTTYPES ON EventTypes (InstanceId);</create> <drop/> <dialect-scope name="org.hibernate.dialect.Oracle9Dialect" /> <dialect-scope name="org.hibernate.dialect.OracleDialect" /> <dialect-scope name="org.hibernate.dialect.PostgreSQLDialect" /> </database-object> {noformat} Adding the index for all supported databases, since it can't hurt.
See https://github.com/droolsjbpm/jbpm/compare/0ea5d89...ce7b57f (the second file). I haven't tested the create statement included with all the databases, but according to the various documentation, it should work. Unfortunately, I think one or two of the databases are case-sensitive in which case the statement might need to be modfied. :/ See BZ 857912 for the documentation part (we'll add documentation about this for people currently not using the JPA 1 + hibernate combo).
I have tested this with MySQL 5.5.23 and EAP 6.0 (Hibernate 4). My findings are: 1.) The fix for this issue needs to be done also for EAP 6.0 (Hibernate 4). I have seen no IDX_EVENTTYPES index in the EVENTTYPES table. 2.) Even when I tried to run the SQL command "CREATE INDEX IDX_EVENTTYPES ON EVENTTYPES (INSTANCEID);" manually, I have faced the problem with the case sensitivity. The Hibernate 4 creates a table "EventTypes" which is not the same as "EVENTTYPES" at least not on MySQL database. I'll check how this fix behaves on supported databases with EAP 5 (Hibernate 3). However, for the reasons mentioned above, I am setting the state of this bug back to ASSIGNED.
*** Bug 864558 has been marked as a duplicate of this bug. ***
So far I have tested BRMS 5.3.1 ER3 standalone (EAP 5.2 + Hibernate 3) with MySQL 5.5.23 and PostgreSQL 9.0.8. However, both of these databases are not fixed. I could not see an index IDX_EVENTTYPES in the EVENTTYPES tables.
This has been modified, see https://source.jboss.org/changelog/jBPM?cs=9f8c77c5cdc0322c1e8d2e2b872e493bf733da51a
Adding github commit ref, for my own reference: https://github.com/droolsjbpm/jbpm/commit/9f8c77c5cdc0322c1e8d2e2b872e493bf733da51
Hey guys, I created some Release Notes based on the comments provided. Thanks again for the information. Please let me know if it does not look correct. - Doug
I have tested the issue again and the problem persist. Couldn't see the IDX_EVENTTYPES in EVENTTYPES table. Tested: BRMS 5.3.1 ER4 ee6 on EAP 6.0 - PostgreSQL 9.0.8 and MySQL 5.5.23 BRMS 5.3.1 ER4 standalone on EAP 5.2 (SVNTag=JBPAPP_5_2_0) - MySQL 5.5.23
@Doug: comments look good. I changed them a little -- yeah, I know, everyone's an editor. ;D
Argh, looked at it this morning (confirmed that bug doesn't work on EAP 6/PostgreSQL 9.0). Will try to finish fix today.
Marco, please make sure to apply the fix also to Oracle10g dialect (I didn't find it in ExtraIndexes.hbm.xml). Cheers, Alessandro
after investigation it turned out that Auxiliary database objects (defined in ExtraIndexes.hbm.xml) are only executed when hibernate.ddl.auto is set to either create-drop or create. So it does not work for default setup which uses update for hibernate.ddl.auto. As you could all agree that ddl auto settings is not welcome in real environments I would like to suggest to put this into documentation - maybe part of installation notes that it is required for "real" data bases. This could be first element of some sort of tuning guide for jBPM in long run where we could capture similar adjustments to jBPM/task service schema. Trying to put it into default code base makes us more dependent on hibernate and will make it much more difficult to proceed with support for other jpa providers e.g openjpa as I think there were already requests for it. In community where jbpm installer is the main entry point we provided ant tasks to generate DDL scripts based on persistence.xml and its mapping files for both jBPM and human task schemas. That could be seen as an alternative to putting this into code base. It is still hibernate specific but it's outside of the main modules and can be used as optional tool. Could you please comment on this?
(In reply to comment #14) > after investigation it turned out that Auxiliary database objects (defined > in ExtraIndexes.hbm.xml) are only executed when hibernate.ddl.auto is set to > either create-drop or create. So it does not work for default setup which > uses update for hibernate.ddl.auto. Lee, would you please document the following: 1) The indexes will be created when the hibernate.ddl.auto is set to either create-drop or create. 2) The Hibernate best practices suggest that you shouldn't use this functionality in production systems at all. You generate your schema once, and then disable the schema-generating feature for good. Other than that, I agree that there is nothing to do on this issue.
Hi Lukas, the docs currently state[1] that 'using create as the value for the hibernate.hbm2ddl.auto property will result in the jBPM schemas being recreated every time the server is restarted'. I'll expand that definition and post a link as soon as it is available on the internal doc stage. Thanks Lee [1]http://documentation-devel.engineering.redhat.com/docs/en-US/JBoss_Enterprise_BRMS_Platform/5/html/BRMS_Business_Process_Management_Guide/Starting_the_Human_Task_Service.html
I've updated the datasource configuration instructions in the admin guide (link to follow) with a note that reads: Setting the hibernate.hbm2ddl.auto value to create or create-drop will create the indexes each time the server is restarted. The indexes should only be created once and then this functionality should be disabled. This can be achieved by placing comment tags around the hibernate.hbm2ddl.auto property after the schema has been generated. I've also updated section 10.3.6 of the BPM guide with the same information, link to follow.
This issue has been discussed with developers and GSS. We have agreed that current solution with ExtraIndexes.hbm.xml file is very hard to manage. I have fixed this by removing this file and its references by this commit: https://github.com/droolsjbpm/jbpm/compare/6e99187...0fa529f Database specific stuff should be handled by BRMS users. Now we have to document these DB related things - that users should create indexes on db schema, which was generated by BRMS. I'll file a new bugzilla for documentation which should describe what needs to be done with BRMS database schema before using the database in production. I have also updated doc text for this bug. Setting to VERIFIED.
Documentation bug for this issue: https://bugzilla.redhat.com/show_bug.cgi?id=883412
Marco Rietveld <marco.rietveld> updated the status of jira JBPM-3589 to Resolved
Marco Rietveld <marco.rietveld> made a comment on jira JBPM-3589 This has been fixed in the latest version of 5.2.x.
Marco Rietveld <marco.rietveld> made a comment on jira JBPM-3589 This has been fixed in the latest version of 5.2.x (which has not been released yet, I believe).