Error in creating new record in documents with lines in big tables.

Description

We test v.3.1 with our data (DB dump from our production database v.2.0).
Our DB dump contain:
C_OrderLine > 12 millions records
M_MovementLine > 1.4 millions records
M_InventoryLine > 1.1 millions records

When we try create new document on this table then occure error (see attach picture).
I reproduced the error in clean installation and clean database.
You can reproduce it too.

1. Install new iDempiere v.3.1 instance.
2. Import new clean Adempiere_pg.dmp with GardenWorld.
3. Seed >1 millions records in C_OrderLine table. You can use my little script "ip_seed_orderline" in pgAdmin or write own.
4. Start idempiere-server.
5. Logs in by SystemAdmin.
6. Open "System Configurator" window, open variable "GRIDTABLE_LOAD_TIMEOUT_IN_SECONDS" and set it to 1 or 2 (default = 30). (In our big production databse we wait 30 seconds and then error occures. But in the clean database I have error after 4 seconds)
7. Logs in by GardenAdmin.
8. Click button "Create New Record" near "Purchase Order" menu item.
9. Set some mandatory fields (Org, BPartner, Warehouse...) and click "Save" button.
10. "Processing..." panel is displayed with rolling ring for 2 seconds.
11. After 2 sec "Error: Timeout loading row 1" message panel is displayed.

In the idempiere log you can see:
16:59:23.874-----------> GridTable.waitLoadingForRow: Reached 2 seconds timeout loading row 1 for SQL=SELECT IsDescription,IsActive,C_ProjectTask_ID,Ref_OrderLine_ID,C_OrderLine_ID,C_ProjectPhase_ID,RRStartDate,QtyLostSales,DateInvoiced,DateDelivered,C_Currency_ID,S_ResourceAssignment_ID,PriceLimit,RRAmt,Processed,AD_Client_ID,AD_Org_ID,C_Order_ID,C_BPartner_ID,C_BPartner_Location_ID,DatePromised,DateOrdered,Line,M_Warehouse_ID,M_Product_ID,C_Charge_ID,M_AttributeSetInstance_ID,Description,QtyEntered,C_UOM_ID,QtyOrdered,QtyDelivered,QtyReserved,QtyInvoiced,M_Shipper_ID,PriceEntered,PriceCost,PriceActual,PriceList,FreightAmt,C_Tax_ID,Discount,C_Project_ID,C_Activity_ID,C_Campaign_ID,AD_OrgTrx_ID,User1_ID,User2_ID,LineNetAmt,Created,CreatedBy,Updated,UpdatedBy FROM C_OrderLine WHERE C_OrderLine.AD_Client_ID IN(0,11) AND C_OrderLine.AD_Org_ID IN(50007,0,50004,50005,50006,50000,50001,50002,11,12) AND (C_OrderLine.C_OrderLine_ID IS NULL OR C_OrderLine.C_OrderLine_ID NOT IN ( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 260 AND AD_User_ID <> 100 AND IsActive = 'Y' )) ORDER BY Line [31]
16:59:23.899===========> ADWindowToolbar.doOnClick: Timeout loading row 1 [31]
java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.adempiere.webui.adwindow.ADWindowToolbar.doOnClick(ADWindowToolbar.java:413)
at org.adempiere.webui.adwindow.ADWindowToolbar.onEvent(ADWindowToolbar.java:377)
...

If I try create new record again in this window then all right - C_Order saved immediately without error message.
But if I close Purchase Order window and open it again with create new record then error occures as describe above.
This error occures in Purchase Order, Sales Order, Phisical Inventory, Inventory Move - documents with Line part having big data.

Environment

None

Activity

Show:
Hiep Lq
April 16, 2016, 3:45 AM

you just move to "PEER REVIEW QUEUE" when you have a patch need to review.

Денис Кузнецов
April 18, 2016, 2:51 PM
Edited

There is some way to create new record, but for simplicity of the experiment we will take two of them:
1) from window by clicking on New Record;
2) from Favorites panel by clicking on New Record.

The first method working fine. The second one - error occures as wrote above (Only in WebUI).
Apropos, my test in swing interface did not reveal any errors in both methods. That is issue of WebUI.

I debugged code and found that problem exists in class GridTable.java procedure createSelectSql().

In this procedure SQL created which select lines from detail table.
There is line (№ 405):
m_SQL = m_SQL_Select + where.toString();

Here:
m_SQL_Select - is contains value = "SELECT .... FROM C_OrderLine"
where - is contains value "WHERE (2=3)" if record creates by method #1; - is contein value "" if record creates by method #2.

As a result m_SQL is set to:
"SELECT .... FROM C_OrderLine WHERE (2=3)" - if record creates by method #1
"SELECT .... FROM C_OrderLine" - if record creates by method #2

With this m_SQL idempiere selecting 0 lines from C_OrderLine table - if record creates by method #1,
and all records in C_OrderLine table - if record creates by method #2.
If C_OrderLine contains 12 millions records (as we have), then system show error message (timeout).

I am a poor knows this code, but I can offer patch: in GridTable.java at line #206
instead of:
. - private String m_whereClause = "";
add one:
. + private String m_whereClause = "2=3";

My little test showed, that this patch works correctly in clean base and in our big production base.

Carlos Ruiz
May 9, 2016, 3:48 AM

Thanks for the detailed description of the error and the script to reproduce it.

Committed your suggestion.

Assignee

Carlos Ruiz

Reporter

Денис Кузнецов

Labels

None

Tested By

None

Components

Priority

Major
Configure