Bug 854612 - Missing index in EVENTTABLES table
Missing index in EVENTTABLES table
Status: VERIFIED
Product: JBoss Enterprise BRMS Platform 5
Classification: JBoss
Component: jBPM 5 (Show other bugs)
BRMS 5.3.0.GA
Unspecified Unspecified
high Severity high
: ER4
: BRMS 5.3.1 GA
Assigned To: Shelly McGowan
Marek Baluch
:
: 864558 (view as bug list)
Depends On:
Blocks: 857192 864558
  Show dependency treegraph
 
Reported: 2012-09-05 08:37 EDT by Martin Weiler
Modified: 2016-09-20 01:08 EDT (History)
8 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Missing index on column InstanceId of table EventTypes caused a deadlock issue in Oracle database. Product documentation will be updated to provide information describing which indexes should be created on BRMS database schema.
Story Points: ---
Clone Of:
: 857192 864558 (view as bug list)
Environment:
Last Closed:
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)


External Trackers
Tracker ID Priority Status Summary Last Updated
JBoss Issue Tracker JBPM-3589 Major Resolved drools-flow: deadlock for concurrent process completions in oracle 2016-05-27 09:18 EDT

  None (edit)
Description Martin Weiler 2012-09-05 08:37:15 EDT
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);
Comment 1 JBoss JIRA Server 2012-09-13 11:42:59 EDT
Marco Rietveld <marco.rietveld@redhat.com> 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..
Comment 2 JBoss JIRA Server 2012-09-13 14:05:07 EDT
Marco Rietveld <marco.rietveld@redhat.com> 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.
Comment 3 Marco Rietveld 2012-09-13 15:02:17 EDT
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).
Comment 4 Jiri Svitak 2012-10-15 06:09:46 EDT
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.
Comment 5 Marco Rietveld 2012-10-15 07:50:56 EDT
*** Bug 864558 has been marked as a duplicate of this bug. ***
Comment 6 Jiri Svitak 2012-10-16 05:48:39 EDT
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.
Comment 7 Marco Rietveld 2012-10-17 12:07:39 EDT
This has been modified, see 

https://source.jboss.org/changelog/jBPM?cs=9f8c77c5cdc0322c1e8d2e2b872e493bf733da51a
Comment 8 Marco Rietveld 2012-10-25 09:24:11 EDT
Adding github commit ref, for my own reference:

https://github.com/droolsjbpm/jbpm/commit/9f8c77c5cdc0322c1e8d2e2b872e493bf733da51
Comment 9 Douglas Hoffman 2012-11-08 19:53:55 EST
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
Comment 10 Jiri Svitak 2012-11-13 12:08:49 EST
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
Comment 11 Marco Rietveld 2012-11-15 03:31:01 EST
@Doug: comments look good. I changed them a little -- yeah, I know, everyone's an editor. ;D
Comment 12 Marco Rietveld 2012-11-20 08:50:19 EST
Argh, looked at it this morning (confirmed that bug doesn't work on EAP 6/PostgreSQL 9.0). Will try to finish fix today.
Comment 13 Alessandro Lazarotti 2012-11-21 09:56:06 EST
Marco, please make sure to apply the fix also to Oracle10g dialect (I didn't find it in ExtraIndexes.hbm.xml).

Cheers,
Alessandro
Comment 14 Maciej Swiderski 2012-11-28 13:20:02 EST
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?
Comment 15 Lukáš Petrovický 2012-11-29 03:29:47 EST
(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.
Comment 16 lcarlon 2012-11-29 03:57:35 EST
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
Comment 17 lcarlon 2012-11-29 20:06:25 EST
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.
Comment 19 Jiri Svitak 2012-12-04 04:49:45 EST
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.
Comment 20 Jiri Svitak 2012-12-04 09:48:33 EST
Documentation bug for this issue:
https://bugzilla.redhat.com/show_bug.cgi?id=883412
Comment 21 JBoss JIRA Server 2012-12-18 17:35:25 EST
Marco Rietveld <marco.rietveld@redhat.com> updated the status of jira JBPM-3589 to Resolved
Comment 22 JBoss JIRA Server 2012-12-18 17:35:25 EST
Marco Rietveld <marco.rietveld@redhat.com> made a comment on jira JBPM-3589

This has been fixed in the latest version of 5.2.x.
Comment 23 JBoss JIRA Server 2012-12-18 17:36:00 EST
Marco Rietveld <marco.rietveld@redhat.com> 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).

Note You need to log in before you can comment on or make changes to this bug.