Bug 3492

Generating a large report crashes the application 02 March, 2020

Gene Kanten
16 October, 2019
Product: PowerBuilder Category: DataWindow
Version: 2017 R3 Build:
Classification: Publishing: Public
Priority: P3
Status: Verifying Reason:
Ken Guo @Appeon 02 March, 2020
#51
Hi Gene,

I have escalated this issue to the development team but we don’t have a plan to fix them at present.
Thus I hope you try to use the method we provided to work around it for the time being.


Regards,
Ken
Gene Kanten 28 February, 2020
#50
Hi Ken,

Thanks for looking into this. I will implement the workaround. We have other reports with a similar structure so I may need to change them as well. 
Will this issue be addressed in a future release? 

Regards,
Gene
Ken Guo @Appeon 27 February, 2020
#49
Change Report

Hi Gene,

I can import the data you provided and can also reproduce the bug when previewing rp_c_property.
This bug is caused due to that there are subreports in both rn_xref_contracts and rn_xref_mineral in the rp_c_property. We can’t fix this bug at present but I suggest you work around it via the following:

Split rn_xref_contracts and rn_xref_mineral reports, that is, in rp_c_property, 
1. delete the rn_xref_contracts report, add rn_xref_contracts_base and rn_xref_contracts_sub.
2. delete rn_xref_mineral report, add rn_xref_mineral_base and rn_xref_mineral_sub.

You can see the attachment for details.

Regards,
Ken
Gene Kanten 26 February, 2020
#48
Hi Ken,

Were you able to load the data set?
Gene Kanten 20 February, 2020
#47
Hi Ken,

I've uploaded the database data for this. 
It's a zip file containing an Oracle 18 data dump. 
You should be able to recreate the issue with this dataset.
Ken Guo @Appeon 19 February, 2020
#46
FTP Settings

Hi Gene,

Please upload the test case to our FTP Site. Thanks.

Ftp server: download.appeon.com
Username: gkanten@p2energysolutions.com
Password: It will be sent in a separate email. 

We suggest that you use a FTP Tool (like the free FileZilla client) to connect to the ftp using the settings(see attachment).
encryption: require implicit ftp over tls
 
Please only upload a reproducible test case, environment image or other files with mock data (no real data) to this account.

Regards,
Ken
Gene Kanten 14 February, 2020
#45
Hi Ken,

I have an Oracle data dump file for you. It's about 84 MB. How should I send it to you?
Gene Kanten 12 February, 2020
#44
Hi Ken,

I will try and put some data together for you. It will take me a while.
Ken Guo @Appeon 11 February, 2020
#43
Hi Gene,

Even though I tried to use your pbl to run the case, due to that this main DW contains dozens of nested reports and I have no idea what data are in each nested report, I still can’t reproduce this issue.

Could you please provide some data of these nested reports for us?
In addition, could you please try to delete some unnecessary nested reports again so that we could use fewer nested reports to replicate this issue?

Regards,
Ken
Gene Kanten 28 January, 2020
#42
Queried rows.xlsx (266KB)

Thanks Mark.

I've attached a spreadsheet that shows the nested row counts and where it seems to blow up.
Mark Lee @Appeon 28 January, 2020
#41
Hi Gene,

Thanks for provide the SQL syntax. 
We will be working on analyzing your case. We will keep you posted
on the results here.

Regards,
Mark Lee
Gene Kanten 27 January, 2020
#40
appeon_c1202tc.sql (13KB)

Hi Ken, 

I've attached the table creates. If I missed any please let me know.
Ken Guo @Appeon 26 January, 2020
#39
Hi Gene,

Please send us the table create scripts.  Thanks!

Regards,
Ken
Gene Kanten 24 January, 2020
#38
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 @Appeon 23 January, 2020
#37
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 @Appeon 23 January, 2020
#36
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
#35
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 @Appeon 23 January, 2020
#34
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
#33
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 @Appeon 23 January, 2020
#32
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
#31
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 @Appeon 22 January, 2020
#30
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
#29
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 @Appeon 22 January, 2020
#28
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
#27
Good to know, thanks Chris. I'll give that a whirl.
Chris Pollach @Appeon 22 January, 2020
#26
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
#25
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 @Appeon 22 January, 2020
#24
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
#23
OK. Thanks for all your help Chris.
Chris Pollach @Appeon 21 January, 2020
#22
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
#21
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 @Appeon 20 January, 2020
#20
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
#19
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 @Appeon 15 January, 2020
#18
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
#17
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 @Appeon 14 January, 2020
#16
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
#15
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 @Appeon 14 January, 2020
#14
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
#13
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
#12
Thanks Chris!
Chris Pollach @Appeon 07 January, 2020
#11
Hi Gene;

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

Regards ... Chris
Gene Kanten 07 January, 2020
#10
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 @Appeon 13 November, 2019
#9
Hi Gene ... OK, will do!
Gene Kanten 13 November, 2019
#8
Hi Chris,

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

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

Regards ... Chris
Gene Kanten 12 November, 2019
#6
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 @Appeon 12 November, 2019
#5
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 @Appeon 17 October, 2019
#4
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
#3
Chris, I've updated all the DW source definitions to release 17 but it still crashes.
Gene Kanten 16 October, 2019
#2
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 @Appeon 16 October, 2019
#1
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
#0
*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