tracker issue | What iT iS dESign studios

Title:

Memory leak in spreadsheet functions

| View in Tracker

Status/Resolution/Reason: To Fix/Withdrawn/Investigate

Reporter/Name(from Bugbase): Trevor Cotton / Trevor Cotton ()

Created: 09/27/2017

Components: Document Management, Office Integration

Versions: 11.0

Failure Type: Memory Leak

Found In Build/Fixed In Build: 11 update 9, 11 update 12, 11 update 13, 2016 update 3, 2016 update 5 /

Priority/Frequency: Normal / All users will encounter

Locale/System: English / Linux SuSE

Vote Count: 2

Problem Description:
Generating a spreadsheet and not saving it to disk will cause the heap memory to not be freed up causing an eventual server hang
Steps to Reproduce:
Example code to read from a database, create spreadsheet and stream to the user. With a 100,000 row, 5 column database and a heap size of 2GB, running this code four times will consume all of the heap and CF will hang.

<cfset spreadsheet = spreadSheetNew("Report",True)>
<cfset spreadsheetAddRow(spreadsheet,"Id,Name,Date,State,Zip")>
<cfquery name="MyQuery" datasource="test1">
SELECT    id, Name, Date, State, Zip      
FROM      myTable
</cfquery>
<cfset spreadSheetAddrows(spreadsheet,myQuery)>
<cfset spreadsheetFormatRow(spreadsheet,
{            
bold=true,             
fontsize=12     
},      
1)>
<cfset filename = "Report.xlsx">
<cfheader name="content-disposition" value="attachment;filename=#filename#">
<cfcontent type="application/msexcel"variable="#spreadsheetReadBinary(spreadsheet)#"reset="true">

Actual Result:
Memory is not freed up after code is executed. Heap will fill up and GC will not be able to recover memory.
Expected Result:
Memory should be freed up after the code is executed.
Any Workarounds:
Write spreadsheet to disk, read back in, stream to client, delete temporary file.
e.g.
<cfset spreadsheet = spreadSheetNew("Report",True)>
<cfset spreadsheetAddRow(spreadsheet,"Id,Name,Date,State,Zip")>
<cfquery name="MyQuery" datasource="test1">
        SELECT    id, Name, Date, State, Zip
        FROM      myTable
</cfquery>
<cfset spreadSheetAddrows(spreadsheet,myQuery)>
<cfset spreadsheetFormatRow(spreadsheet,
        {
                bold=true,
                fontsize=12
        },
        1)>
<cfset spreadsheetWrite(spreadsheet,"/var/www/html/report.xlsx","yes")>
<cfset spreadsheet = spreadsheetRead("/var/www/html/report.xlsx", "Report")>
<cfset filename = "Report.xlsx">
<cffile action="delete" file="/var/www/html/report.xlsx">
<cfheader name="content-disposition" value="attachment; filename=#filename#">
<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(spreadsheet)#" reset="true">

Attachments:

Comments:

Trevor,
I don't see a memory leak with with max heap size set to 1G.
The generating the output does take a lot of time though. The following code takes about 8-9 mins to generate the excel file.
After running the test code 3-4 times, the memory was reclaimed after each run.
You've mentioned that CF freezes up. Do you see any errors in the exception log file. Is it "GC overhead exceeded"? Do you still see the error if you increase the heap size.

This is related to CF-4198618. We can look at it from improving on the processing time perspective, unless it really is a memory leak.

<cfscript>
    rowcount = 60000;
    col_count = 5;
    qry_col_lst = "";
    col_nm_ltrl = "column_";

    for(c=1; c<=col_count; c++)
        qry_col_lst = qry_col_lst & col_nm_ltrl & c & ",";

    qData = QueryNew(qry_col_lst);
    
    for(i=1; i<= rowcount; i++)
    {
        QueryAddRow(qData);
        for(c=1; c<=col_count; c++)
        {
            c_no = col_nm_ltrl & c;
            QuerySetCell(qData, c_no, "some random text in sheet no.1 in row " & i & " col " & c);
        }
    }
</cfscript>
<cfset spreadsheet = spreadSheetNew("Report", True)>
<cfset spreadsheetAddRow(spreadsheet,"Id,Name,Date,State,Zip")>
<cfset spreadSheetAddrows(spreadsheet, qData)>
<cfset spreadsheetFormatRow(spreadsheet, { bold=true, fontsize=12 }, 2)>
<cfset spreadsheetWrite(spreadsheet, "#expandpath('./')#report11.xlsx", true)>

Comment by Piyush K.

293 | October 04, 2017 08:42:40 AM GMT

Unless the reporter confirms this is the memory leak issue, this is a duplicate of CF-4198618.

Comment by Piyush K.

294 | October 10, 2017 06:49:35 AM GMT

You missed what I was saying I think.
The workaround is to write the spreadsheet - that frees memory. If you don't write the spreadsheet, the memory never gets freed.
In your code above, you do a spreadsheetWrite.
This code shows the memory leak.

Problem Description:
Generating a spreadsheet and not saving it to disk will cause the heap memory to not be freed up causing an eventual server hang
Steps to Reproduce:
Example code to read from a database, create spreadsheet and stream to the user. With a 100,000 row, 5 column database and a heap size of 2GB, running this code four times will consume all of the heap and CF will hang.

<cfset spreadsheet = spreadSheetNew("Report",True)>
<cfset spreadsheetAddRow(spreadsheet,"Id,Name,Date,State,Zip")>
<cfquery name="MyQuery" datasource="test1">
SELECT    id, Name, Date, State, Zip      
FROM      myTable
</cfquery>
<cfset spreadSheetAddrows(spreadsheet,myQuery)>
<cfset spreadsheetFormatRow(spreadsheet,
{            
bold=true,             
fontsize=12     
},      
1)>
<cfset filename = "Report.xlsx">
<cfheader name="content-disposition" value="attachment;filename=#filename#">
<cfcontent type="application/msexcel"variable="#spreadsheetReadBinary(spreadsheet)#"reset="true">

Actual Result:
Memory is not freed up after code is executed. Heap will fill up and GC will not be able to recover memory.
Expected Result:
Memory should be freed up after the code is executed.

Comment by Trevor C.

295 | December 19, 2017 08:04:21 PM GMT

This still happens in CF 2018..

Comment by Vladimir P.

31781 | October 31, 2019 08:03:17 PM GMT

tracker issue : CF-4199829

Memory leak in spreadsheet functions