ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc. on BPMS 6.4

Solution Verified - Updated -

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.91 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 of org.jbpm.persistence.jpa.hibernate.DisabledFollowOnLockOracle10gDialect.

  1. https://access.redhat.com/jbossnetwork/restricted/softwareDetail.html?softwareId=57111 ↩

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

  1. https://issues.jboss.org/browse/RHBPMS-4778 ↩

  2. https://issues.jboss.org/browse/RHBPMS-5115 ↩

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.

Close

Welcome! Check out the Getting Started with Red Hat page for quick tours and guides for common tasks.