Uploaded image for project: 'AppFormer'
  1. AppFormer
  2. AF-1149

Dashbuilder not closing ResultSets and Statements leading to ORA-01000 error

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major Major
    • None
    • 0.5.0.Final
    • Dashbuilder
    • 2018 Week 15-16
    • Hide

      1. With a plain BPMS installation, create a user:

      $ ./bin/add-user.sh -u 'kieserver' -p 'kieserver1!' -a -g 'admin,kie-server,rest-all,user'

      2. Edit standalone/configuration/standalone.xml:

                      <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">
...
                    <statement>
                        <track-statements>true</track-statements> 
                    </statement>
                </datasource>

      3. Start the server, register a query:

      curl -X POST -u 'kieserver:kieserver1!' --data "<query-definition><query-name>getAllTasks</query-name><query-source>java:jboss/datasources/ExampleDS</query-source><query-expression>SELECT ti.*, oe.id as oeid FROM AuditTaskImpl ti, PeopleAssignments_PotOwners po, OrganizationalEntity oe WHERE ti.taskId= po.task_id AND po.entity_id = oe.id</query-expression><query-target>PO_TASK</query-target></query-definition>"  -H 'Content-type: application/xml' http://localhost:8080/kie-server/services/rest/server/queries/definitions/reproducer

      4. Execute the query:

      $ curl -u 'kieserver:kieserver1!' http://localhost:8080/kie-server/services/rest/server/queries/definitions/reproducer/data?mapper=UserTasksWithCustomVariables

      5. To reproduce the ORA-01000 error:

      • Set <track-statements>false</track-statements>
      • Reduce the open cursors limit: SQL> alter system set open_cursors = 20 scope = both;
      • Run the curl query command in a loop
      Show
      1. With a plain BPMS installation, create a user: $ ./bin/add-user.sh -u 'kieserver' -p 'kieserver1!' -a -g 'admin,kie-server,rest-all,user' 2. Edit standalone/configuration/standalone.xml : <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true"> ... <statement> <track-statements>true</track-statements> </statement> </datasource> 3. Start the server, register a query: curl -X POST -u 'kieserver:kieserver1!' --data "<query-definition><query-name>getAllTasks</query-name><query-source>java:jboss/datasources/ExampleDS</query-source><query-expression>SELECT ti.*, oe.id as oeid FROM AuditTaskImpl ti, PeopleAssignments_PotOwners po, OrganizationalEntity oe WHERE ti.taskId= po.task_id AND po.entity_id = oe.id</query-expression><query-target>PO_TASK</query-target></query-definition>" -H 'Content-type: application/xml' http: //localhost:8080/kie-server/services/ rest /server/queries/definitions/reproducer 4. Execute the query: $ curl -u 'kieserver:kieserver1!' http: //localhost:8080/kie-server/services/ rest /server/queries/definitions/reproducer/data?mapper=UserTasksWithCustomVariables 5. To reproduce the ORA-01000 error: Set <track-statements>false</track-statements> Reduce the open cursors limit: SQL> alter system set open_cursors = 20 scope = both; Run the curl query command in a loop
    • NEW
    • NEW

      If the datasource is set up with <track-statements>true</track-statements>, executing a query via REST API results in the following WARN message:

      17:17:39,765 WARN  [org.jboss.jca.adapters.jdbc.WrappedConnection] (http-127.0.0.1:8080-1) Closing a result set you left open! Please close it yourself.: java.lang.Throwable: STACKTRACE
	at org.jboss.jca.adapters.jdbc.WrappedStatement.registerResultSet(WrappedStatement.java:1357)
	at org.jboss.jca.adapters.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:345)
	at org.dashbuilder.dataprovider.sql.JDBCUtils.executeQuery(JDBCUtils.java:80) [dashbuilder-dataset-sql-0.5.0.Final-redhat-15.jar:0.5.0.Final-redhat-15]
	at org.dashbuilder.dataprovider.sql.model.Select.fetch(Select.java:189) [dashbuilder-dataset-sql-0.5.0.Final-redhat-15.jar:0.5.0.Final-redhat-15]
	at org.dashbuilder.dataprovider.sql.SQLDataSetProvider._getColumns(SQLDataSetProvider.java:416) [dashbuilder-dataset-sql-0.5.0.Final-redhat-15.jar:0.5.0.Final-redhat-15]
	at org.dashbuilder.dataprovider.sql.SQLDataSetProvider._getDataSetMetadata(SQLDataSetProvider.java:327) [dashbuilder-dataset-sql-0.5.0.Final-redhat-15.jar:0.5.0.Final-redhat-15]
	at org.dashbuilder.dataprovider.sql.SQLDataSetProvider$LookupProcessor.run(SQLDataSetProvider.java:646) [dashbuilder-dataset-sql-0.5.0.Final-redhat-15.jar:0.5.0.Final-redhat-15]
	at org.dashbuilder.dataprovider.sql.SQLDataSetProvider._lookupDataSet(SQLDataSetProvider.java:450) [dashbuilder-dataset-sql-0.5.0.Final-redhat-15.jar:0.5.0.Final-redhat-15]
	at org.dashbuilder.dataprovider.sql.SQLDataSetProvider.lookupDataSet(SQLDataSetProvider.java:235) [dashbuilder-dataset-sql-0.5.0.Final-redhat-15.jar:0.5.0.Final-redhat-15]
	at org.dashbuilder.dataset.DataSetManagerImpl.lookupDataSet(DataSetManagerImpl.java:153) [dashbuilder-dataset-core-0.5.0.Final-redhat-15.jar:0.5.0.Final-redhat-15]
	at org.jbpm.kie.services.impl.query.QueryServiceImpl.query(QueryServiceImpl.java:202) [jbpm-kie-services-6.5.0.Final-redhat-19.jar:6.5.0.Final-redhat-19]
	at org.jbpm.kie.services.impl.query.QueryServiceImpl.query(QueryServiceImpl.java:170) [jbpm-kie-services-6.5.0.Final-redhat-19.jar:6.5.0.Final-redhat-19]
	at org.kie.server.services.jbpm.QueryDataServiceBase.query(QueryDataServiceBase.java:110) [kie-server-services-jbpm-6.5.0.Final-redhat-19.jar:6.5.0.Final-redhat-19]
	at org.kie.server.remote.rest.jbpm.QueryDataResource.runQuery(QueryDataResource.java:164) [kie-server-rest-jbpm-6.5.0.Final-redhat-19.jar:6.5.0.Final-redhat-19]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_161]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_161]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_161]
	at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_161]

      This indicates that a ResultSet or a Statement is not properly closed.

      When BPMS is used with Oracle, and track-statements=false, this will eventually result in the following database error:

      14:55:58,211 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 7) SQL Error: 1000, SQLState: 72000
14:55:58,211 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 7) ORA-01000: maximum open cursors exceeded
14:55:58,211 WARN  [org.jbpm.shared.services.impl.TransactionalCommandService] (EJB default - 7) Could not commit session: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not extract ResultSet

              david.magallanes David Gutierrez
              david.magallanes David Gutierrez
              Jan Hrcek Jan Hrcek (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated:
                Resolved: