Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0038195Openbravo ERPA. Platformpublic2018-03-22 11:512018-03-28 18:10
Reporteralostale 
Assigned Toalostale 
PriorityurgentSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR18Q2 
Merge Request Status
Review Assigned Tocaristu
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0038195: too many ORA open cursors when reading DB model
DescriptionWhen dbsm reads DB model using Oracle, both while updating and exporting database. Too many cursors and kept open.

This can cause the export/update to fail if there are, for example, around 2K indices and open_cursors is limited to 5K.
Steps To ReproduceUsing Oracle.

* Case 1: failure
  1.1. Add 2K indexes to DB
  2.2. Export database:
 [java] 69991 ERROR - Error while checking the table where the index XXXXX belongs
     [java] java.sql.SQLException: ORA-01000: maximum open cursors exceeded
     [java]
     [java] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
     [java] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
     [java] at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
     [java] at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
     [java] at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
     [java] at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
     [java] at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
     [java] at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
     [java] at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
     [java] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
     [java] at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
     [java] at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
     [java] at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
     [java] at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
     [java] at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
     [java] at org.apache.ddlutils.platform.oracle.OracleModelLoader.getTableNameFromIndexName(OracleModelLoader.java:430)
     [java] at org.apache.ddlutils.platform.oracle.OracleModelLoader.getIndexOperatorClass(OracleModelLoader.java:391)
     [java] at org.apache.ddlutils.platform.oracle.OracleModelLoader.access$100(OracleModelLoader.java:36)
     [java] at org.apache.ddlutils.platform.oracle.OracleModelLoader$2.fillRow(OracleModelLoader.java:353)
     [java] at org.apache.ddlutils.platform.ModelLoaderBase.fillList(ModelLoaderBase.java:872)
     [java] at org.apache.ddlutils.platform.oracle.OracleModelLoader.readIndex(OracleModelLoader.java:340)
     [java] at org.apache.ddlutils.platform.ModelLoaderBase$17.getRow(ModelLoaderBase.java:489)
     [java] at org.apache.ddlutils.platform.ModelLoaderBase.readList(ModelLoaderBase.java:821)
     [java] at org.apache.ddlutils.platform.ModelLoaderBase.readIndexes(ModelLoaderBase.java:487)
  

* Case 2: check open cursors without making it fail
  2.1. In a clean pi, export database
  2.2. While exporting monitor number of open cursors with select sql_text, count(*) as "OPEN CURSORS", user_name from v$open_cursor group by sql_text, user_name order by count(*) desc,2;
   -> Check there are a moment where the top 3 open cursors per query reach:

    SELECT table_name FROM USER_INDEXES U WHERE INDEX_NAME = :1 436
    SELECT comments FROM user_tab_comments WHERE UPPER(table_nam 236
    SELECT comments FROM user_col_comments WHERE UPPER(table_nam 200
    SELECT column_name FROM USER_IND_COLUMNS U WHERE INDEX_NAME 200


Proposed Solution
Additional Information
TagsPerformance
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2018-03-22 11:51alostaleNew Issue
2018-03-22 11:51alostaleAssigned To => platform
2018-03-22 11:51alostaleModules => Core
2018-03-22 11:51alostaleTriggers an Emergency Pack => No
2018-03-22 11:52alostaleReview Assigned To => caristu
2018-03-22 11:52alostaleAssigned Toplatform => alostale
2018-03-22 11:52alostaleTag Attached: Performance
2018-03-22 12:00alostaleSummarytoo many open ORA open cursors when reading DB model => too many ORA open cursors when reading DB model
2018-03-22 12:07hgbotCheckin
2018-03-22 12:07hgbotNote Added: 0103426
2018-03-22 12:07hgbotStatusnew => resolved
2018-03-22 12:07hgbotResolutionopen => fixed
2018-03-22 12:07hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/dbsm-main/rev/9f30ffa8fee35d78e72e1ceb315a0564b6e9f644 [^]
2018-03-22 12:07hgbotCheckin
2018-03-22 12:07hgbotNote Added: 0103427
2018-03-22 12:07hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/dbsm-main/rev/9f30ffa8fee35d78e72e1ceb315a0564b6e9f644 [^] => http://code.openbravo.com/erp/devel/pi/rev/8e91e721e8fab4f5ad8c82bd75b36b7d5a0f8764 [^]
2018-03-23 21:22hudsonbotCheckin
2018-03-23 21:22hudsonbotNote Added: 0103476
2018-03-28 18:10caristuNote Added: 0103627
2018-03-28 18:10caristuStatusresolved => closed
2018-03-28 18:10caristuFixed in Version => 3.0PR18Q2

Notes
(0103426)
hgbot   
2018-03-22 12:07   
Repository: erp/devel/dbsm-main
Changeset: 9f30ffa8fee35d78e72e1ceb315a0564b6e9f644
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Thu Mar 22 11:54:48 2018 +0100
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/9f30ffa8fee35d78e72e1ceb315a0564b6e9f644 [^]

fixed bug 38195: statement leak when reading DB model

  Many statems where not closed so they remained open till the end of the
  process.

---
M src/org/apache/ddlutils/platform/PlatformImplBase.java
M src/org/apache/ddlutils/platform/oracle/OracleBuilder.java
M src/org/apache/ddlutils/platform/oracle/OracleModelLoader.java
M src/org/apache/ddlutils/platform/postgresql/PostgreSqlModelLoader.java
M src/org/openbravo/ddlutils/util/OBDataset.java
---
(0103427)
hgbot   
2018-03-22 12:07   
Repository: erp/devel/pi
Changeset: 8e91e721e8fab4f5ad8c82bd75b36b7d5a0f8764
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Thu Mar 22 11:57:39 2018 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/8e91e721e8fab4f5ad8c82bd75b36b7d5a0f8764 [^]

fixed bug 38195: statement leak when reading DB model

  Many statements where not closed so they remained open till the end of the process.

---
M src-db/database/lib/dbsourcemanager.jar
---
(0103476)
hudsonbot   
2018-03-23 21:22   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/42e5233f5207 [^]
Maturity status: Test
(0103627)
caristu   
2018-03-28 18:10   
Code reviewed + tested OK.