Questions? Feedback?powered byOlark live chat software
Bug 3492

Generating a large report crashes the application 26 January, 2020

Gene Kanten
16 October, 2019
Product: PowerBuilder Category: DataWindow
Version: 2017 R3 Publishing: Public
Classification: Priority: P3
Status: Reproducing Reason:
Ken Guo 26 January, 2020
Hi Gene,

Please send us the table create scripts.  Thanks!

Regards,
Ken
Gene Kanten 24 January, 2020
Hi Ken,

I was previewing the rp_c_property datawindow (main datawindow). I couldn't tell which nested datawindow it crashed on because it never returns from the retrieve it just crashes.

I can send you the table create scripts if you want.
Ken Guo 23 January, 2020
Hi Gene,

Please tell me when you preview which datawindow crashed under PB IDE? Is it rn_xref_contracts or rn_xref_mineral?

BTW,I need to take some time to create the database tables and data so that I can reproduce your problem.

Regards,
Ken
Chris Pollach 23 January, 2020
FWIW:  The IDE itself though takes up a lot of address space memory when various painters are running. So it just might be the DW Painter processing it that pushes the memory over that 3G memory limitation. Hard to say though - just a guess on my part.

BTW: Have you ever tried retrieving the DWO using a DataStore instead of a DW Control? Also with the PB.INI setting ...

[Data Window]
; Can improve the speed for large row retrievals
Accessibility=0


[DataStore Behavior]
; No = do NOT use MS-Window handles in DS processing (Default is yes). "No" gives much better performance and memory!
UseHwnd=no

Note: PB.INI would need to reside with your PB App's EXE.
Gene Kanten 23 January, 2020
Hi Chris,

I don't think it's related to the total memory usage of the application because I get the same error simply previewing the datawindow in the PowerBuilder IDE and I'm not running any application at that point (only PowerBuilder itself).
Chris Pollach 23 January, 2020
Hi Gene;

  Yes, you are right ... that is Meg not Gig. Wow, that is very strange to blow out at that low a limit. Of course, that does not account for what your App has already acquired memory wise out of the 4G - including other DWO's that already retrieved data, are still open, and have not been destroyed or if so, garbage collection has not cleaned up their associated memory.

  I guess the best way would be to now use the "GlobalMemoryStatusEx" API call to get the *real* memory used after the 50K rows. That would tell us for sure if its a *total* application memory address space issue that you are exceeding. If not, then we would need to focus else where.

Regards ... Chris
Gene Kanten 23 January, 2020
appeon.pbl (733KB)

Hi Chris,

I think you've misread the numbers/units. We are talking 3 MB (3,335,508 bytes), not 3 GB. 

I've attached a pbl with all the datawindows involved. This will give you the structure of the report. I think the structure is okay because I can run the report successfully as long as it doesn't exceed the "limit". 

The last time I ran it with a large data set, the main datawindow retrieved 13,907 rows and the nested datawindows retrieve 50,223 rows and then it crashes. 
Viewing the process in Task Manager I can see the memory usage grow from about 50 MB to about 300 MB then it crashes.
Chris Pollach 23 January, 2020
Hi Gene;

  Yep, that would do it at 3,335,508 bytes used. 32 Bit Apps only have a 4gig address space. However, the O/S itself uses some of that memory in the high order to control the 4G execution space. typically, leaving around just over 3G of actual app "working" memory. Once you exceed the working memory. for your 4G address space - your app is then penalized (via a nasty thread termination). 

  Since the PB IDE is also currently only a 32bit app, a large DWO retrieval like that that blows the usable memory in its 4G address space would also then crash the IDE as well.

  What I am surprised with though is your app crashing when compiled into a 64bit App as the address space and hence "usable memory" within that is much, much higher. So a full row DWO retrieve should have enough memory space.

  I am now wondering though if your crash at the 64bit level might be because you are performing computations using smaller numeric variables vs Long or better yet LongLong. Again, that's just a guess on my part.

  Lets see what Ken thinks.

Regards ... Chris
Gene Kanten 23 January, 2020
Hi Ken,

Do you need me to create an application, because I can get it to crash by doing a retrieve in Preview in the IDE?
Ken Guo 22 January, 2020
Hi Gene,

Thanks for the feedback! In order to better analyze this issue, I need you to:
1. Create a small PBT application which includes the main DW and all nested DWs. After that, try to retrieve the main DW in this small application and see if it will crash.
2. If it crashes, please send this small application for us for further analysis. Thanks in advance.


Regards,
Ken
Gene Kanten 22 January, 2020
PB170.EXE.22280.dmp (6399KB)

Hi Chris and Ken,

I added the memory calls to the main datawindow. It started with 63,728 bytes?  and ended with 3,335,508, using 3,271,780. I'm thinking this doesn't include the memory for the nested datawindows. 

I was watching the program (PB170.EXE) in windows Task Manager and when it crashed it was using about 370MB.

I've attached the dump file.
Chris Pollach 22 January, 2020
PS: for 64bit Apps ... 

LongLong lll_mem_start, lll_mem_end

lll_mem_start = LongLong (THIS.Object.DataWindow.Storage)
lll_mem_end = LongLong (THS.Object.DataWindow.Storage)
Gene Kanten 22 January, 2020
Good to know, thanks Chris. I'll give that a whirl.
Chris Pollach 22 January, 2020
Hi Gene;

    Tip: You can use this to check out the DWO's memory consumption - say at 50K rows ...

mem_end = Long (THIS.Object.DataWindow.Storage)

Note: Using this a the RetrieveStart event will give you the base memory used just by loading the DWO. Using this above at the RetrieveEnd event will give you the overall memory used. Subtracting mem_end - mem-used will goive you just the row overhead. I would personally do this at the parent DC/DS level to get the overall parent and child DWO(s) O/S overhead.

HTH
Regards ...Chris
Gene Kanten 22 January, 2020
Hi Ken,

If the report is run for everything there are 237,241 rows to report.
13907 rows in the main datawindow and 223,334 rows in the nested child datawindows.
The maximum no. of rows in a child data window is 10,614.

It seems to crash after processing around 50,000 rows. This is around 2400 printed pages (8.5 X 11 landscape).
There are not many columns printed for each row (max. of 8 columns on a row). They are mostly dates, codes and short text descriptions.
If the report is run for less than 50K rows it completes successfully.

The main datawindow contains 11 nested datawindows. 
Two of the nested datawindows each contain contain another two nested datawindows.
Three of the nested datawindows are optional and either none or one of them will be printed.

I can consistently get it to crash if I process more than 50,000 or so rows. 

I haven't checked to see how much memory is being used.
Ken Guo 22 January, 2020
Hi Gene,

Thanks for the feedback!
From your description, this issue seems to be related to the amount of data and memory. 
Please tell me how many lines of records are included in your DW. When the application is running, how much memory it will use?
Could you try to provide a reproducible case for us for further analysis?


Regards,
Ken
Gene Kanten 21 January, 2020
OK. Thanks for all your help Chris.
Chris Pollach 21 January, 2020
Hi Gene;

  Thank you so much for trying these suggestions. I am sorry that we have still not been able to resolve your run-time stability issue.

  I will now transfer this ticket over to the main Support / Engineering team for their review and other possible suggestions for this problem DLL issue.

Regards ... Chris
Gene Kanten 20 January, 2020
Hi Chris;

I've checked the data processing and no rows returned is properly handled. In fact, I've been able to run this report for all the entries contained in the entire database so I know the data is good and the report works. I've checked that the dberror and error events have code in them but these events are not being fired. 
I can not run the report for the entire database because the report crashes, however if I run it for a subset of entries that is under a certain row limit the report completes successfully. 

Even the 64 bit version seems to be generating an exception in dwTableAggrSortCmp, it just seems to be handling it differently, see the stack text below.

KERNELBASE!RaiseException+0x69
OraOCIEI12!slgtd+0x12135
OraOCIEI12!kpeDbgHdlPostop+0x114f
OraOCIEI12!slgtd+0x12487
KERNELBASE!UnhandledExceptionFilter+0x1bc
ntdll!RtlUserThreadStart$filt$0+0xaa
ntdll!_C_specific_handler+0x96
ntdll!RtlpExecuteHandlerForException+0xf
ntdll!RtlDispatchException+0x40f
ntdll!KiUserExceptionDispatch+0x2e
PBDWE170!dwTableAggrSortCmp+0x15784
PBDWE170!dwTableSortCmp+0xe45
PBDWE170!dwSyntaxFromSQL+0x94586
PBDWE170!dwSyntaxFromSQL+0x94835
PBDWE170!dwSyntaxFromSQL+0x77291
PBDWE170!dwSyntaxFromSQL+0x65e82
PBDWE170!dwSyntaxFromSQL+0x65f95
PBDWE170!dwSyntaxFree+0x25bf4
PBDWE170!dwSyntaxFree+0x26420
PBDWE170!dwSyntaxFree+0x2676c
PBDWE170!dwSyntaxFree+0x3123a
PBDWE170!dwSyntaxFree+0x33309
PBDWE170!dwSyntaxFree+0x2507f
PBDWE170!dwSyntaxFromSQL+0x1832a
PBDWE170!dwSyntaxFromSQL+0x94944
PBDWE170!dwSyntaxFromSQL+0x77291
PBDWE170!dwSyntaxFromSQL+0x65e82
PBDWE170!dwSyntaxFromSQL+0x65f95
PBDWE170!dwSyntaxFree+0x25bf4
PBDWE170!dwSyntaxFree+0x26420
PBDWE170!dwSyntaxFree+0x2676c
PBDWE170!dwSyntaxFree+0x3123a
PBDWE170!dwSyntaxFree+0x33309
PBDWE170!dwSyntaxFree+0x2507f
PBDWE170!dwSyntaxFromSQL+0x1832a
PBDWE170!dwTableAggrSortCmp+0x16852
PBDWE170!dwTableSortCmp+0x10b4
PBDWE170!CreateXHTMLTemplate+0x1176f
PBDWE170!dwSetTransparency+0x358f
PBVM170!fnRetrieve+0x4e9
PBVM170!ob_get_global_func_class+0x2565
PBVM170!ob_get_global_func_class+0x1f5b
PBVM170!ob_create_interface_in_library+0x17e6
PBVM170!ot_dbg_funccall+0x6d4
PBVM170!getVtableInfo_plugincontextkeyword+0x2a3ed
PBVM170!ob_get_pcode_stack_effect+0xbca
PBVM170!ob_get_global_func_class+0x2ae4
PBVM170!ob_get_global_func_class+0x1f5b
PBVM170!ob_create_interface_in_library+0x17e6
PBVM170!ot_dbg_funccall+0x6d4
PBVM170!getVtableInfo_plugincontextkeyword+0x2a3ed
PBVM170!ob_get_pcode_stack_effect+0xbca
PBVM170!rt_hit_level_0+0xf26
PBVM170!rtRoutineExec+0x406
PBVM170!fnHandleTriggerEventMsg+0x31d
PBVM170!FN_PostedRoutine+0xa9c
PBVM170!ob_deactivate_session+0x174
PBVM170!ob_run_dispatch_loop+0x10b
PBVM170!FN_PluginPollLoop+0x247
PBVM170!FN_RunExecutableEx+0x5f2
PBVM170!FN_RunExecutable+0x95
cs_land+0x10ff
cs_land+0x14f4
kernel32!BaseThreadInitThunk+0x14
ntdll!RtlUserThreadStart+0x21

Do you know if there are any limits in dwTableAggrSortCmp or what it does if it runs out of memory?
Chris Pollach 20 January, 2020
Hi Gene;

   Looks like the last SQL statement executed but the 1st FETCH returned no rows (Null result set). So make sure that your application can handle that scenario. If it tried to process the data without checking the DB return code - that could certainly lead to a GPF.

    If this SQL is from a DWO within a DW Control / DataStore, also check to make sure that the ERROR and DBERROR events are coded to properly trap any inappropriate DB result set / processing behaviour. 

Regards ... Chris
Gene Kanten 17 January, 2020
dbtrace1.log (7KB)

Hi Chris;

I created a trace file. It didn't notice any error conditions in it. It was quite large. I've attached the end of the trace file. It appears to process everything until the app crashes.
Chris Pollach 15 January, 2020
Hi Gene;

   My suggestion now would be to start an SQL trace as any "ORAOCIxxx.dll" typically related to the Oracle client. SO I wonder if a) its a an SQL statement originating from the PB App or b) trying to handle a particular result set coming back to the PB App.

   You can start an SQL Trace in your PB App by setting the Trace feature on, as follows ...

SQLCA.DBMS = "TRACE ORA"

Regards ... Chris
Gene Kanten 14 January, 2020
CrashDumps.zip (2662KB)

Hi Chris,

I tried running a 64 bit version but it crashed as well but in a different DLL (OraOCIEI12.dll) although it seems to be called from the same spot the others fail on. I've attached four dump files. One for the 32 bit version, one for the 64 bit version and two for the PowerBuilder IDE, one using the PB.ini settings you recommended and one without. They seem to fail in pbdwe170.dwTableAggrSortCmp.
Chris Pollach 14 January, 2020
Hi Gene;

  Thank you for trying those settings! Along with better performance, these settings do reduce memory consumption but, obviously not enough (if that is the case).

  Please let us know how a 64bit App EXE fairs under this type of data load.

Also, please confirm that the PB2017R3 App EXE crashes in the same DLL (check the O/S Event Logs).

Regards ... Chris
Gene Kanten 14 January, 2020
Hi Chris,

I tried changing the PB.ini settings but that didn't help. 
I will try creating a 64 bit exe but it will take a while because we currently don't have our build pipeline setup to do that.
Chris Pollach 14 January, 2020
Hi Gene;

  If its memory related (sounds like a possibility here in the 32bit world), here are a few PB.ini settings that might help ...

[Data Window]
; Can improve the speed for large row retrievals
Accessibility=0

[DataStore Behavior]
; No = do NOT use MS-Window handles in DS processing (Default is yes). "No" gives much better performance!
UseHwnd=no

  Please locate your working PB.ini in the PB IDE's System Options dialogue's "General" tab in the "Initialization Path" entry. Please make the above modifications to that INI instance.

  Note that if the above works, then create a PB.ini file in the same folder as your PB App's EXE. You only need the above entries in that INI instance. Then deploy this PB.INI with your PB Apps.

PS: If the above fails, then the other thing to try would be a 64bit App EXE. Foos for thought.

Regards ... Chris
Gene Kanten 14 January, 2020
Hi Chris,

I've investigated all your suggestions and other than a) which I have corrected, none of these conditions appear to be present. The issue seems to be related to the amount of data rows that are being processed. I have proven that it is not bad data as I am able to report on all the data but I have to break it up into chunks. It appears that if the selected data rows are over a certain threshold then the application crashes. For the database I'm investigating the main datawindow returns 13907 rows and the multiple nested datawindows (about a dozen) should select 237241 rows but the application crashes after retrieving around 48-59000 rows. I suspect it could be a memory issue. This same behavior occurs when I preview the datawindow in the IDE and it crashes PowerBuilder. I can attach a dump file if that would help.
Gene Kanten 07 January, 2020
Thanks Chris!
Chris Pollach 07 January, 2020
Hi Gene;

   HTH .. http://chrispollach.blogspot.com/2013/02/pbevents.html

Regards ... Chris
Gene Kanten 07 January, 2020
Hi Chris,

I'm back looking at this. For b) do you have a list of the events that  are fired in a different order that I can investigate?
Chris Pollach 13 November, 2019
Hi Gene ... OK, will do!
Gene Kanten 13 November, 2019
Hi Chris,

Yes please keep this open for now. Thanks.
Chris Pollach 12 November, 2019
Hi Gene;

  Would you like to keep this ticket open for now then?

Regards ... Chris
Gene Kanten 12 November, 2019
Hi Chris,
Sorry, no things are still crashing. 
I haven't had time to investigate this further but I will at some point in the future. 
Got pulled off to work on another project. 
Thanks for following up.
Chris Pollach 12 November, 2019
Hi Gene;

 We have not heard back from you for a while. Were you able to resolve your crash issue(s) OK?

Regards ... Chris
Chris Pollach 17 October, 2019
Hi Gene;

  Thank you for trying this 1st step. Sometimes that can be the source of a crash after a migrations. The next considerations would be:

a) Bad DWO source that remains bad in PB2017/2019 even after migration but newer PB run-times cannot handle the DW(s) at run time properly for some reason.
b) Older PB migrated PowerScript that errors out because some events are fired in a different order and the code does not check for IsValid() situations.
c) DWO's that call global functions and the GF has an issue.
d) DWO's that use expressions and the expression is no longer valid in PB2017/2019.
e) Sometimes, a corrupt or misbehaving DropDownDataWindow will cause this to happen as well

  Given the above circumstances, a few others who have encountered this situation have chosen to rebuild the parent DWO and / or a child from scratch. That usually cures the problem. However, this alternative can be labourious.

Regards ... Chris
Gene Kanten 17 October, 2019
Chris, I've updated all the DW source definitions to release 17 but it still crashes.
Gene Kanten 16 October, 2019
The main datawindow reads release 17 but the other included nested datawindows are various older versions 12.5, 11.5 and even 9.0.
Chris Pollach 16 October, 2019
Hi Gene;

  It sounds like it might be the DW source "definition" that might be the issue. If you perform a "Edit Source" on the suspect DWO - does its 1st line read "release 19;"?

Regards ... Chris
Gene Kanten 16 October, 2019
*Phenomenon: 
Application crashes when running a large report. The same report with smaller data sets run fine.


*Reproduce Steps:
We have a nested datawindow that is used as a report. When a user attempts to run the report with parameters that return lots of data, the application crashes. 

Remarks: 
The application is aborting during a datawindow retrieve. A crash dump from production identifies dwTableAggrSortCmp in PBDWE125.DLL is failing with a c0000005 (Access violation). This was a Windows 7, PowerBuilder 12.5.2 environment but the application also crashes in PowerBuilder 2017 R3 on Windows 10, although I don't know if it's at the same spot in the PBDWE170.dll. If I preview the offending datawindow in the IDE, it also crashes the IDE. I suspect it's maybe running out of memory  but would expect it to return an error code on the retrieve rather than crash.
OS:
Windows 10 
Platform:
All 
Database Type:
Oracle 
Database Version:
11.2