ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc. on BPMS 6.4
Environment
- Red Hat JBoss BPM Suite
- 6.4.6+
Issue
We applied the latest patch for BPM Suite 6.4.6 and we started seeing the following errors continuously:
ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 1) ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
Resolution
- Upgrade to BPM Suite 6.4.111 in order to use
org.jbpm.persistence.jpa.hibernate.DisabledFollowOnLockOracle10gDialect
- In BPM Suite 6.4.6, 6.4.7 and 6.4.8 it is required to use
org.hibernate.dialect.Oracle10gDialect
instead oforg.jbpm.persistence.jpa.hibernate.DisabledFollowOnLockOracle10gDialect
.
Root Cause
In BPMS 6.4.6, a new column priority
has been added to the RequestInfo
table to allow prioritization of asynchronous tasks.1 This results in a query syntax that is unsupported by Oracle when using org.jbpm.persistence.jpa.hibernate.DisabledFollowOnLockOracle10gDialect
.
Note that dialect should be configured in 2 places:
- business-central.war/WEB-INF/classes/META-INF/persistence.xml
- system property "org.kie.server.persistence.dialect" (e.g. in standalone.xml)
As the default Oracle dialect can potentially lead to a job getting executed by multiple job executor threads, a fix for the failing query has been provided to support the org.jbpm.persistence.jpa.hibernate.DisabledFollowOnLockOracle10gDialect
in BPMS 6.4.9 again.2 Another fix in this code has been provided in BPMS 6.4.11.3
This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.
Comments