[SOLVED] FPSpreadSheet, ReadAsText seems to create Memory leak

[andresayang]

Hello,

I eliminate instructions one by one ... TsWorksheet.ReadAsText seems to produce Memory leaks when reading workbook containing more than one spreadsheet.
I can give The excel book, and the code i wrote, If you please can help me ...

Thanks

[wp]

Please show your code and the Excel file. A memory leak does not happen - see attached project which creates a workbook with two sheets containing random numbers, writes it to an xlsx file and reads it back; heaptrc reports "0 unfreed memory blocks : 0".

[andresayang]

Hi,

Many thanks for having a look at it.
I'll post code and the "problematic" excel sheet tomorrow (Brazil time).

The thing is when I do remove "extra sheet" (the one I do not have data to read), no more memory leeks. (and right now I have only the "clean" version without leaks).

Cheers

[andresayang]

Hi,

So i make a new project to reduce the code, with the function i use to read excel file which gives Memory leaks.
All included in "7zip" file, I remove executable, you only have sources.

Thanks for your help,

Cheers

You will notice I'm using linux, hope it doesn't change nothing

[wp]

Tested your project in both Windows 11 and Manjaro Linux. In each case, there is *no* error leak. Maybe you are confused by the non-empty heaptrc output, saying

Code: [Select]

Heap dump by heaptrc unit of D:\Prog_Lazarus\tests\_fpspreadsheet\_forum\test_memleak\TestMemLeaks\project1.exe
1423598 memory blocks allocated : 136386984/138556552
1423598 memory blocks freed     : 136386984/138556552
0 unfreed memory blocks : 0
True heap size : 2523136 (144 used in System startup)
True free heap : 2522992The relevant line is the one with "0 unfreed memory block: 0" meaning that there are no unfreed memory blocks.

[andresayang]

So,
There is a big problem somewhere ... this is what I do obtain, simply by pushing "run" ...
(288 unfreed memory blocks means memory leaks I guess)

Heap dump by heaptrc unit of /home/geo1/Lazarus/Previous/TestMemLeaks/project1 1409448 memory blocks allocated : 218902392/220173824 1409160 memory blocks freed     : 218861509/220132576 288 unfreed memory blocks : 40883 True heap size : 2588672 True free heap : 2482944 Should be : 2492128 Call trace for block $00007FA7C8E03800 size 29   $00000000006274AD  DOATTRTEXT,  line 3858 of laz2_xmlread.pas   $0000000000621A27  EXPECTATTVALUE,  line 2010 of laz2_xmlread.pas   $0000000000625E78  PARSEATTRIBUTE,  line 3542 of laz2_xmlread.pas   $0000000000625A0F  PARSEELEMENT,  line 3411 of laz2_xmlread.pas   $00000000006253DF  PARSECONTENT,  line 3255 of laz2_xmlread.pas   $0000000000620E6B  PROCESSXML,  line 1710 of laz2_xmlread.pas   $000000000061E2F2  PARSE,  line 925 of laz2_xmlread.pas   $000000000063B900  READXMLSTREAM,  line 334 of source/common/fpsxmlcommon.pas   $00000000005A800B  READEMBEDDEDOBJS,  line 2809 of source/common/xlsxooxml.pas   $00000000005AEB0B  READFROMSTREAM,  line 4565 of source/common/xlsxooxml.pas   $000000000055C181  READFROMFILE,  line 589 of source/common/fpsreaderwriter.pas   $0000000000499F16  READFROMFILE,  line 6579 of source/common/fpspreadsheet.pas   $000000000049A442  READFROMFILE,  line 6646 of source/common/fpspreadsheet.pas   $0000000000401A81  ReadExcelFileData,  line 93 of project1.lpr   $00000000004014D8  DoRun,  line 57 of project1.lpr   $0000000000483C99 Call trace for block $00007FA7C90C1280 size 92   $00000000006274AD  DOATTRTEXT,  line 3858 of laz2_xmlread.pas   $0000000000621A27  EXPECTATTVALUE,  line 2010 of laz2_xmlread.pas   $0000000000625E78  PARSEATTRIBUTE,  line 3542 of laz2_xmlread.pas   $0000000000625A0F  PARSEELEMENT,  line 3411 of laz2_xmlread.pas   $00000000006253DF  PARSECONTENT,  line 3255 of laz2_xmlread.pas   $0000000000620E6B  PROCESSXML,  line 1710 of laz2_xmlread.pas   $000000000061E2F2  PARSE,  line 925 of laz2_xmlread.pas   $000000000063B900  READXMLSTREAM,  line 334 of source/common/fpsxmlcommon.pas   $00000000005A800B  READEMBEDDEDOBJS,  line 2809 of source/common/xlsxooxml.pas   $00000000005AEB0B  READFROMSTREAM,  line 4565 of source/common/xlsxooxml.pas   $000000000055C181  READFROMFILE,  line 589 of source/common/fpsreaderwriter.pas   $0000000000499F16  READFROMFILE,  line 6579 of source/common/fpspreadsheet.pas   $000000000049A442  READFROMFILE,  line 6646 of source/common/fpspreadsheet.pas   $0000000000401A81  ReadExcelFileData,  line 93 of project1.lpr   $00000000004014D8  DoRun,  line 57 of project1.lpr   $0000000000483C99 ..... Very long list, I guess there is 288 identical blocks ..... Call trace for block $00007FA7C93109B0 size 2048   $000000000062E37A  CREATE,  line 2514 of laz2_dom.pas   $0000000000620DF3  PROCESSXML,  line 1704 of laz2_xmlread.pas   $000000000061E2F2  PARSE,  line 925 of laz2_xmlread.pas   $000000000063B900  READXMLSTREAM,  line 334 of source/common/fpsxmlcommon.pas   $00000000005A800B  READEMBEDDEDOBJS,  line 2809 of source/common/xlsxooxml.pas   $00000000005AEB0B  READFROMSTREAM,  line 4565 of source/common/xlsxooxml.pas   $000000000055C181  READFROMFILE,  line 589 of source/common/fpsreaderwriter.pas   $0000000000499F16  READFROMFILE,  line 6579 of source/common/fpspreadsheet.pas   $000000000049A442  READFROMFILE,  line 6646 of source/common/fpspreadsheet.pas   $0000000000401A81  ReadExcelFileData,  line 93 of project1.lpr   $00000000004014D8  DoRun,  line 57 of project1.lpr   $0000000000483C99   $F0F0F0F0F0F0F0F0   $F0F0F0F0F0F0F0F0   $F0F0F0F0F0F0F0F0   $F0F0F0F0F0F0F0F0 Call trace for block $00007FA7B00F3500 size 32   $000000000062E37A  CREATE,  line 2514 of laz2_dom.pas   $0000000000620DF3  PROCESSXML,  line 1704 of laz2_xmlread.pas   $000000000061E2F2  PARSE,  line 925 of laz2_xmlread.pas   $000000000063B900  READXMLSTREAM,  line 334 of source/common/fpsxmlcommon.pas   $00000000005A800B  READEMBEDDEDOBJS,  line 2809 of source/common/xlsxooxml.pas   $00000000005AEB0B  READFROMSTREAM,  line 4565 of source/comm

On both Debain 11 + Lazarus 3.4 and Debian Testing + Lazarus 4.0 Rc1

(I just re-install FPSpreadSheet from online package manager, to check in case of version problem, and same results)

Cheers

[TRon]

I am able to reproduce on bookworm with Lazarus 3.6, 4.0RC1, and trunk with fpc 3.2.3 (x86_64)

[wp]

The same memory leak in the demo that I posted in my first response?

[TRon]

Sorry, I (initially) missed your example wp. No leaks with your example (4.0RC1) only leaks with OP's example (with exact same trace as OP).

[andresayang]

Sorry, I (initially) missed your example wp. No leaks with your example (4.0RC1) only leaks with OP's example (with exact same trace as OP).

Hi gents,

This is quite nice for me, thanks to confirm.
But if @wp do not have it, and we do, maybe something outside FPSPreadSheet (I was thinking about locals setup because it should be the only thing from system which could influence FPSpreadSheet).

So my locals are utf8 - us

Cheers

The thing is, in the example, if you keep only "SUMMARY" sheet in the workbook, no more memory leak. The function is supposed to read only "SUMMARY" sheet.

[TRon]

But if @wp do not have it, and we do, maybe something outside FPSPreadSheet (I was thinking about locals setup because it should be the only thing from system which could influence FPSpreadSheet).

It seems more related to the xml parser. Note that xml_dom seem to be showing its colours (the memory blocks that are not freed are much bigger there).

So, either the spreadsheet itself contains xml that can't be handled or the xml parser chokes itself by not freeing something which it allocated.

So my locals are utf8 - us

Same here but might be pure coincidence.

The thing is, in the example, if you keep only "SUMMARY" sheet in the workbook, no more memory leak. The function is supposed to read only "SUMMARY" sheet.

And it does only 'handle' one sheet, though I am not familiar with what the search engine actually does.

Note that the example code you've posted just runs and produce the results the program seem to be looking for. It seem the xml backend used by fpSpreadsheet seems to be tripping over something in particular (which it shouldn't).

I admit, it is out of the ordinary that wp is not able to reproduce... Still thinking about that one.

[andresayang]

Hi,

Is FPSpreadSheet developed and maintained by WP ? (Maybe I should submit my example directly to maintainers ?)

Cheers

[TRon]

Is FPSpreadSheet developed and maintained by WP ? (Maybe I should submit my example directly to maintainers ?)

To my knowledge yes, though afaik nothing official. I consider WP an authority on/at/for fpSpreadsheet.

The "problem" is he's doing a lot of other (important) development as well so he might be a bit busy and isn't always capable of immediately directing his attention to a specific issue. That is why I tried to reproduce and am still trying to see what might be the culprit though I am not that familiar with the code-base of fpSpreadSheet (but I am at least capable of reading a trace  ).

[andresayang]

The "problem" is he's doing a lot of other (important) development as well so he might be a bit busy and isn't always capable of immediately directing his attention to a specific issue. That is why I tried to reproduce and am still trying to see what might be the culprit though I am not that familiar with the code-base of fpSpreadSheet (but I am at least capable of reading a trace  ).

Man, yes of course, no problem and thanks a lot for Lazarus !
Lazarus is a free software thanks to all developers from a guys who learn Pascal (UCSD was the first compiler I use), then Turbopascal, then Delphi which was too expensive for me (as I use it at work, to reduce my daily work load, can not play with non legal software).

Cheers

So i digg a little bit more deeper: (I hope it can help)

The leak is created directly at the workbook creation, and seems to come from the "PLOT" sheet (If I remove it, no more leaks).

If you comment all after "mySearchEngine:= TsSearchEngine.Create(TheWorkBook);" (line 95) and close the comment just before the "finally" (line 155) we still have the same leaks, with same "288 unfreed memory blocks".

So I went deeper on this and I do not think the problem is from FPSpreadSheet .... but from the excel worksheet itself (it should be somehow wrongly formatted or I do not know).

I try: moving the "PLOT" sheet in another workbook, saved the original file then move back the "PLOT" worksheet in main workbook: No more leaks.

I tag the subject as Solved ...

[wp]

Looking up the trace I see a call to ReadEmbeddedObjs. This is normally used for images, pictures etc. And your test file does contain a chart which is handled by this function, too. I assume that you are using the OPM version of fpspreadsheet which does not have chart support at all, or partial - I don't remember, while I am using the svn version of FPSpreadsheet where much work was put into chart support.

To verify this idea I switched to the OPM version of FPSpreadsheet, and now I can confirm the issue. And going a step further - deleting the charts from the PLOT sheet removes the memory leak.

Therefore, the solution for you would be to either remove the charts from the xlsx file, or to install the svn version (use svn, or get the zipped snapshot from https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/)