Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
0037214Openbravo ERPA. Platformpublic2017-11-02 17:282018-01-03 12:39
egoitz 
inigosanchez 
normalmajoralways
closedfixed 
5
 
3.0PR18Q1 
alostale
Core
No
0037214: It is possible to produce a server crash executing on the sql query window a query returning thousands of records
If you execute a query on the sql query window that returns thousands of rows the tomcat can stop responding due to high memory usage of the jvm.
1.- Login as "System Administrator - System" in the ERP
2.- Go to [SQL Query] window.
3.- Execute the following sql:
    -> SELECT * FROM ad_field af1, ad_field af2, ad_field af3
4.- Check Tomcat is not responding due to high memory usage of the JVM. Error is raised in the log:

711327 [http-bio-8080-exec-1] ERROR org.openbravo.xmlEngine.ErrorManagement - in designorg/openbravo/erpCommon/ad_forms/SQLExecutor.structureHeader
java.lang.OutOfMemoryError: GC overhead limit exceeded
Dumping heap to java_pid21158.hprof ...
Heap dump file created [1768632959 bytes in 10,052 secs]
Performance
related to defect 0035838 closed inigosanchez Bad performance in alert window when having +20000 records in ad_alert and many of them are solved 
Issue History
2017-11-02 17:28egoitzNew Issue
2017-11-02 17:28egoitzAssigned To => platform
2017-11-02 17:28egoitzModules => Core
2017-11-02 17:28egoitzTriggers an Emergency Pack => No
2017-11-06 13:12alostaleRelationship addedrelated to 0035838
2017-11-06 13:13alostaleStatusnew => acknowledged
2017-11-06 17:42egoitzDescription Updatedbug_revision_view_page.php?rev_id=16236#r16236
2017-11-06 17:42egoitzSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=16238#r16238
2017-11-07 13:34egoitzDescription Updatedbug_revision_view_page.php?rev_id=16244#r16244
2017-11-08 17:15inigosanchezAssigned Toplatform => inigosanchez
2017-11-08 17:15inigosanchezStatusacknowledged => scheduled
2017-11-08 17:15inigosanchezSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=16252#r16252
2017-11-08 17:28inigosanchezSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=16253#r16253
2017-11-10 09:03alostaleTag Attached: Performance
2017-11-10 10:36hgbotCheckin
2017-11-10 10:36hgbotNote Added: 0100417
2017-11-10 10:36hgbotCheckin
2017-11-10 10:36hgbotNote Added: 0100418
2017-11-10 10:36hgbotStatusscheduled => resolved
2017-11-10 10:36hgbotResolutionopen => fixed
2017-11-10 10:36hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/0a1c870b2e2c159d159edcf062ce72e205c0c7b0 [^]
2017-11-10 11:14inigosanchezNote Added: 0100419
2017-11-10 12:49inigosanchezNote Edited: 0100419bug_revision_view_page.php?bugnote_id=0100419#r16267
2017-11-10 13:00alostaleNote Added: 0100423
2017-11-10 13:00alostaleStatusresolved => new
2017-11-10 13:00alostaleResolutionfixed => open
2017-11-14 16:13inigosanchezStatusnew => scheduled
2017-11-14 17:16hgbotCheckin
2017-11-14 17:16hgbotNote Added: 0100522
2017-11-14 17:16hgbotStatusscheduled => resolved
2017-11-14 17:16hgbotResolutionopen => fixed
2017-11-14 17:16hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/0a1c870b2e2c159d159edcf062ce72e205c0c7b0 [^] => http://code.openbravo.com/erp/devel/pi/rev/9ea13ff843c312c1efb407034ffc46b7dc1d012d [^]
2017-11-16 08:49hgbotCheckin
2017-11-16 08:49hgbotNote Added: 0100538
2017-11-16 08:50alostaleReview Assigned To => alostale
2017-11-16 08:50alostaleNote Added: 0100539
2017-11-16 08:50alostaleStatusresolved => closed
2017-11-16 08:50alostaleFixed in Version => 3.0PR18Q1
2018-01-03 12:38hudsonbotCheckin
2018-01-03 12:38hudsonbotNote Added: 0101398
2018-01-03 12:38hudsonbotCheckin
2018-01-03 12:38hudsonbotNote Added: 0101399
2018-01-03 12:39hudsonbotCheckin
2018-01-03 12:39hudsonbotNote Added: 0101420
2018-01-03 12:39hudsonbotCheckin
2018-01-03 12:39hudsonbotNote Added: 0101424

Notes
(0100417)
hgbot   
2017-11-10 10:36   
Repository: erp/devel/pi
Changeset: 273381bbd0b51fbbdd92b1ca33bca79e82d16ffd
Author: Inigo Sanchez <inigo.sanchez <at> openbravo.com>
Date: Fri Nov 10 10:34:03 2017 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/273381bbd0b51fbbdd92b1ca33bca79e82d16ffd [^]

Related to issue 37214: initial code clean-up
- Removed commented code.
- The showsErrorMessage method is used to avoids duplicate code.
- The errors now are showed to the user.

---
M src/org/openbravo/erpCommon/ad_forms/SQLExecutor.java
---
(0100418)
hgbot   
2017-11-10 10:36   
Repository: erp/devel/pi
Changeset: 0a1c870b2e2c159d159edcf062ce72e205c0c7b0
Author: Inigo Sanchez <inigo.sanchez <at> openbravo.com>
Date: Fri Nov 10 10:35:18 2017 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/0a1c870b2e2c159d159edcf062ce72e205c0c7b0 [^]

Fixed issue 37214: Improved performance in SQL Query manual window

It was possible to produce a server crash executing on the sql query window a query returning
thousands of records. An OutOfMemoryError error is raised although only 20 records were showed
to the user because in fact, all the records were retrieving from the db instead of the 20 needed
records.

In order to fix the performance problem, now the queries are executed as a Native SQL with OFFSET
(org.hibernate.SQLQuery.setFirstResult) and LIMIT (org.hibernate.SQLQuery.setMaxResults). Now the
performance problem has been fixed.

---
M src/org/openbravo/erpCommon/ad_forms/SQLExecutor_Query.java
---
(0100419)
inigosanchez   
2017-11-10 11:14   
(edited on: 2017-11-10 12:49)
Tested in PG 9.4 and ORA 12. Times in org.openbravo.erpCommon.ad_forms.SQLExecutor.doPost:
- With PG 9.4:
 -- Query [1]. Before: OutOfMemoryError. After improvements: 280 ms.
 -- Query [2]. Before: 5577 ms. After improvements: 252 ms.


Queries:
[1] SELECT * FROM AD_FIELD A1, AD_FIELD A2, AD_FIELD A3
[2] SELECT * FROM AD_ORG A1, AD_ORG A2, AD_ORG A3, AD_ORG A4, AD_ORG A5

(0100423)
alostale   
2017-11-10 13:00   
reopening due to several problems detected in review:
https://docs.google.com/spreadsheets/d/18l2biKWeG6iGDIPqO6VuYQ_TtP_6YxyQU4KSrPvQlJ8/edit#gid=0 [^]
(0100522)
hgbot   
2017-11-14 17:16   
Repository: erp/devel/pi
Changeset: 9ea13ff843c312c1efb407034ffc46b7dc1d012d
Author: Inigo Sanchez <inigo.sanchez <at> openbravo.com>
Date: Tue Nov 14 17:16:19 2017 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/9ea13ff843c312c1efb407034ffc46b7dc1d012d [^]

Fixed issue 37214: Code review improvements.

Several changes have been done:
  - Now it is possible to execute a query with a single column in the result because it is take into account when the result has a single column as result.
  - The first column is not ommited and it is shown as expected.
  - Fixed a problem in the export to excel: The first row is not shown in the excel. Now this problem has been fixed.
  - Same errors are shown when clicking on FIND icon or in REFRESH icon.
  - Several unnecessary logs have been removed.
  - The PreparedStatement is closed.

---
M src/org/openbravo/erpCommon/ad_forms/SQLExecutor.java
M src/org/openbravo/erpCommon/ad_forms/SQLExecutor_Query.java
---
(0100538)
hgbot   
2017-11-16 08:49   
Repository: erp/devel/pi
Changeset: 2d234dbfd9da1b8e88ec0ccfacb2a8417226ce01
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Thu Nov 16 08:31:03 2017 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/2d234dbfd9da1b8e88ec0ccfacb2a8417226ce01 [^]

related to bug 37214: removed printStackTrace

---
M src/org/openbravo/erpCommon/ad_forms/SQLExecutor.java
---
(0100539)
alostale   
2017-11-16 08:50   
reviewed + tested
(0101398)
hudsonbot   
2018-01-03 12:38   
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/c81e0d3cbab5 [^]
Maturity status: Test
(0101399)
hudsonbot   
2018-01-03 12:38   
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/c81e0d3cbab5 [^]
Maturity status: Test
(0101420)
hudsonbot   
2018-01-03 12:39   
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/c81e0d3cbab5 [^]
Maturity status: Test
(0101424)
hudsonbot   
2018-01-03 12:39   
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/c81e0d3cbab5 [^]
Maturity status: Test