Bug 1309083 - SQL Exception when trying to view process instances in Business Central Console
Summary: SQL Exception when trying to view process instances in Business Central Console
Keywords:
Status: CLOSED EOL
Alias: None
Product: JBoss BPMS Platform 6
Classification: Retired
Component: BAM
Version: 6.2.0
Hardware: x86_64
OS: Linux
unspecified
high
Target Milestone: ER1
: 6.3.0
Assignee: David Gutierrez
QA Contact: Gui Jospin
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2016-02-16 19:45 UTC by David Murphy
Modified: 2020-03-27 20:10 UTC (History)
0 users

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Clone Of:
Environment:
Last Closed: 2020-03-27 20:10:59 UTC
Type: Bug
Embargoed:


Attachments (Terms of Use)
Description of investigation. (30.27 KB, application/rtf)
2016-02-16 19:45 UTC, David Murphy
no flags Details

Description David Murphy 2016-02-16 19:45:49 UTC
Created attachment 1127699 [details]
Description of investigation.

Description of problem:
We  updated our BPMS Database to utilize DB2.  We create a simple BPM project.  We deployed and created a process instance based on this simple project.  Next we navigated to the Process Instance View of the Business Central to view our newly created process instance.   We receive the below exception when trying to access this page.   

15:23:01,992 ERROR [org.dashbuilder.dataprovider.backend.sql.JDBCUtils] (http-/0.0.0.0:8080-1) SELECT * FROM PROCESSINSTANCELOG LIMIT 1
15:23:01,994 ERROR [org.dashbuilder.dataset.backend.exception.ExceptionManager] (http-/0.0.0.0:8080-1) Can't get metadata on specified data set: jbpmProcessInstances: org.dashbuilder.dataset.exception.DataSetLookupException: Can't get metadata on specified data set: jbpmProcessInstances
        at org.dashbuilder.dataset.backend.BackendDataSetManager.getDataSetMetadata(BackendDataSetManager.java:156) [dashbuilder-dataset-core-0.3.4.Final-redhat-4.jar:0.3.4.Final-redhat-4]
        at org.dashbuilder.dataset.backend.BackendDataSetManager$Proxy$_$$_WeldClientProxy.getDataSetMetadata(BackendDataSetManager$Proxy$_$$_WeldClientProxy.java) [dashbuilder-dataset-core-0.3.4.Final-redhat-4.jar:0.3.4.Final-redhat-4]
        at org.dashbuilder.dataset.backend.DataSetLookupServicesImpl.lookupDataSetMetadata(DataSetLookupServicesImpl.java:98) [dashbuilder-services-0.3.4.Final-redhat-4.jar:0.3.4.Final-redhat-4]
        at org.dashbuilder.dataset.backend.DataSetLookupServicesImpl$Proxy$_$$_WeldClientProxy.lookupDataSetMetadata(DataSetLookupServicesImpl$Proxy$_$$_WeldClientProxy.java) [dashbuilder-services-0.3.4.Final-redhat-4.jar:0.3.4.Final-redhat-4]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_71]
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_71]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_71]
        at java.lang.reflect.Method.invoke(Method.java:497) [rt.jar:1.8.0_71]
        at org.jboss.errai.bus.server.io.AbstractRPCMethodCallback.invokeMethodFromMessage(AbstractRPCMethodCallback.java:48) [errai-bus-3.2.1.Final-redhat-2.jar:3.2.1.Final-redhat-2]
        at org.jboss.errai.bus.server.io.ValueReplyRPCEndpointCallback.callback(ValueReplyRPCEndpointCallback.java:22) [errai-bus-3.2.1.Final-redhat-2.jar:3.2.1.Final-redhat-2]
        at org.jboss.errai.bus.server.io.RemoteServiceCallback.callback(RemoteServiceCallback.java:54) [errai-bus-3.2.1.Final-redhat-2.jar:3.2.1.Final-redhat-2]
        at org.jboss.errai.cdi.server.CDIExtensionPoints$2.callback(CDIExtensionPoints.java:396) [errai-weld-integration-3.0.6.Final-redhat-1.jar:3.0.6.Final-redhat-1]
        at org.jboss.errai.bus.server.DeliveryPlan.deliver(DeliveryPlan.java:47) [errai-bus-3.2.1.Final-redhat-2.jar:3.2.1.Final-redhat-2]
        at org.jboss.errai.bus.server.ServerMessageBusImpl.sendGlobal(ServerMessageBusImpl.java:296) [errai-bus-3.2.1.Final-redhat-2.jar:3.2.1.Final-redhat-2]
        at org.jboss.errai.bus.server.SimpleDispatcher.dispatchGlobal(SimpleDispatcher.java:46) [errai-bus-3.2.1.Final-redhat-2.jar:3.2.1.Final-redhat-2]
        at org.jboss.errai.bus.server.service.ErraiServiceImpl.store(ErraiServiceImpl.java:97) [errai-bus-3.2.1.Final-redhat-2.jar:3.2.1.Final-redhat-2]
        at org.jboss.errai.bus.server.service.ErraiServiceImpl.store(ErraiServiceImpl.java:114) [errai-bus-3.2.1.Final-redhat-2.jar:3.2.1.Final-redhat-2]
        at org.jboss.errai.bus.server.servlet.DefaultBlockingServlet.doPost(DefaultBlockingServlet.java:142) [errai-bus-3.2.1.Final-redhat-2.jar:3.2.1.Final-redhat-2]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:754) [jboss-servlet-api_3.0_spec-1.0.2.Final-redhat-2.jar:1.0.2.Final-redhat-2]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:847) [jboss-servlet-api_3.0_spec-1.0.2.Final-redhat-2.jar:1.0.2.Final-redhat-2]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:295) [jbossweb-7.5.12.Final-redhat-1.jar:7.5.12.Final-redhat-1]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214) [jbossweb-7.5.12.Final-redhat-1.jar:7.5.12.Final-redhat-1]
        at org.uberfire.ext.security.server.SecureHeadersFilter.doFilter(SecureHeadersFilter.java:53) [uberfire-servlet-security-0.7.4.Final-redhat-4.jar:0.7.4.Final-redhat-4]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:246) [jbossweb-7.5.12.Final-redhat-1.jar:7.5.12.Final-redhat-1]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214) [jbossweb-7.5.12.Final-redhat-1.jar:7.5.12.Final-redhat-1]
        at org.uberfire.ext.security.server.SecurityIntegrationFilter.doFilter(SecurityIntegrationFilter.java:45) [uberfire-servlet-security-0.7.4.Final-redhat-4.jar:0.7.4.Final-redhat-4]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:246) [jbossweb-7.5.12.Final-redhat-1.jar:7.5.12.Final-redhat-1]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214) [jbossweb-7.5.12.Final-redhat-1.jar:7.5.12.Final-redhat-1]
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:231) [jbossweb-7.5.12.Final-redhat-1.jar:7.5.12.Final-redhat-1]
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:149) [jbossweb-7.5.12.Final-redhat-1.jar:7.5.12.Final-redhat-1]
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:512) [jbossweb-7.5.12.Final-redhat-1.jar:7.5.12.Final-redhat-1]
        at org.jboss.as.jpa.interceptor.WebNonTxEmCloserValve.invoke(WebNonTxEmCloserValve.java:50) [jboss-as-jpa-7.5.5.Final-redhat-3.jar:7.5.5.Final-redhat-3]
        at org.jboss.as.jpa.interceptor.WebNonTxEmCloserValve.invoke(WebNonTxEmCloserValve.java:50) [jboss-as-jpa-7.5.5.Final-redhat-3.jar:7.5.5.Final-redhat-3]
        at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:169) [jboss-as-web-7.5.5.Final-redhat-3.jar:7.5.5.Final-redhat-3]
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:150) [jbossweb-7.5.12.Final-redhat-1.jar:7.5.12.Final-redhat-1]
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:97) [jbossweb-7.5.12.Final-redhat-1.jar:7.5.12.Final-redhat-1]
        at org.apache.catalina.authenticator.SingleSignOn.invoke(SingleSignOn.java:419) [jbossweb-7.5.12.Final-redhat-1.jar:7.5.12.Final-redhat-1]
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:102) [jbossweb-7.5.12.Final-redhat-1.jar:7.5.12.Final-redhat-1]
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344) [jbossweb-7.5.12.Final-redhat-1.jar:7.5.12.Final-redhat-1]
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:854) [jbossweb-7.5.12.Final-redhat-1.jar:7.5.12.Final-redhat-1]
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:653) [jbossweb-7.5.12.Final-redhat-1.jar:7.5.12.Final-redhat-1]
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:926) [jbossweb-7.5.12.Final-redhat-1.jar:7.5.12.Final-redhat-1]
        at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_71]
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=LIMIT;;FETCH FIRST <n> ROWS ONLY, DRIVER=4.16.53
        at com.ibm.db2.jcc.am.fd.a(fd.java:739)
        at com.ibm.db2.jcc.am.fd.a(fd.java:60)
        at com.ibm.db2.jcc.am.fd.a(fd.java:127)
        at com.ibm.db2.jcc.am.to.c(to.java:2771)
        at com.ibm.db2.jcc.am.to.d(to.java:2759)
        at com.ibm.db2.jcc.am.to.a(to.java:2192)
        at com.ibm.db2.jcc.t4.ab.h(ab.java:141)
        at com.ibm.db2.jcc.t4.ab.b(ab.java:41)
        at com.ibm.db2.jcc.t4.o.a(o.java:32)
        at com.ibm.db2.jcc.t4.tb.i(tb.java:145)
        at com.ibm.db2.jcc.am.to.kb(to.java:2161)
        at com.ibm.db2.jcc.am.to.a(to.java:3258)
        at com.ibm.db2.jcc.am.to.a(to.java:697)
        at com.ibm.db2.jcc.am.to.executeQuery(to.java:676)
        at org.jboss.jca.adapters.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:344)
        at org.dashbuilder.dataprovider.backend.sql.JDBCUtils.executeQuery(JDBCUtils.java:77) [dashbuilder-dataset-sql-0.3.4.Final-redhat-4.jar:0.3.4.Final-redhat-4]
        at org.dashbuilder.dataprovider.backend.sql.model.Select.fetch(Select.java:172) [dashbuilder-dataset-sql-0.3.4.Final-redhat-4.jar:0.3.4.Final-redhat-4]
        at org.dashbuilder.dataprovider.backend.sql.SQLDataSetProvider._getColumns(SQLDataSetProvider.java:333) [dashbuilder-dataset-sql-0.3.4.Final-redhat-4.jar:0.3.4.Final-redhat-4]
        at org.dashbuilder.dataprovider.backend.sql.SQLDataSetProvider._getDataSetMetadata(SQLDataSetProvider.java:274) [dashbuilder-dataset-sql-0.3.4.Final-redhat-4.jar:0.3.4.Final-redhat-4]
        at org.dashbuilder.dataprovider.backend.sql.SQLDataSetProvider.getDataSetMetadata(SQLDataSetProvider.java:189) [dashbuilder-dataset-sql-0.3.4.Final-redhat-4.jar:0.3.4.Final-redhat-4]
        at org.dashbuilder.dataprovider.backend.sql.SQLDataSetProvider$Proxy$_$$_WeldClientProxy.getDataSetMetadata(SQLDataSetProvider$Proxy$_$$_WeldClientProxy.java) [dashbuilder-dataset-sql-0.3.4.Final-redhat-4.jar:0.3.4.Final-redhat-4]
        at org.dashbuilder.dataset.backend.BackendDataSetManager.getDataSetMetadata(BackendDataSetManager.java:153) [dashbuilder-dataset-core-0.3.4.Final-redhat-4.jar:0.3.4.Final-redhat-4]
        ... 42 more



By default, DB2 does not support LIMIT statements, and setting DB2_COMPATIBILITY_VECTOR to include MYS is not an option in this environment.

org.dashbuilder.dataprovider.backend.sql.dialect.DB2Dialect needs to be adjusted to correctly use the FETCH FIRST <n> ROWS ONLY syntax for limited queries.


How reproducible:
Always

Steps to Reproduce:
1. Configure BPMS to use a DB2 datasource
2. Deploy and start a process
3. Attempt to view process instances dashboard

Actual results:
SQL exception


Expected results:
Display process instances


Additional info:

Comment 2 David Gutierrez 2016-02-17 08:08:03 UTC
Can you please indicate what DB2 version & driver are you using?

Our test suite includes IBM DB2 9.7 and 10.5 so I'm wondering what is the scenario that causes this to fail.

- David -

Comment 3 David Murphy 2016-02-17 14:00:21 UTC
(In reply to David Gutierrez from comment #2)
> Can you please indicate what DB2 version & driver are you using?
> 
> Our test suite includes IBM DB2 9.7 and 10.5 so I'm wondering what is the
> scenario that causes this to fail.
> 
> - David -

The environment is DB2 LUW 10.1. 
Specifically, the DB2_COMPATIBILITY_VECTOR in this environment is empty. DB2 does not support LIMIT by default [1]. Note that the documentation is from 10.5, so this remains true even in the version you test. Can you confirm the value of that setting in the test DB2 environment?

[1] http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.porting.doc/doc/r0052867.html?lang=en

Comment 5 David Gutierrez 2016-03-03 11:29:52 UTC
This has been reported as a core dashbuilder issue: 

https://issues.jboss.org/browse/DASHBUILDE-96

Comment 6 David Gutierrez 2016-03-07 14:13:38 UTC
The related dashbuilder issues has been fixed.

Comment 7 Jan Hrcek 2016-03-18 13:04:41 UTC
The issue has been fixed. All the tests are passing both on DB2 9.7 and 10.5


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