TSQLQuery (memory leak) lazarus / 16-Databases ---------------------------------------------------------------------- mith · 2013-12-30 22:18 Hi, I'm trying to export data to a *.csv file using a TSQLQuery (connected to a postgres database). Everything works fine for a small results. When the result is bigger (for example 80k rows, each with 6 fields of varchar 255 - which isn't of course huge amount of data), my program exhausts all possible memory (more than 1,5GB). I'm wondering what's wrong. During search in google and this forum I found some similar problems but none resolved. Did anyone met with this problem? I made tests on Windows 7 64b, Win 8.1. Application was compiled on Lazarus (stable) 1.0.10. Please find below a part of code: Code: [Select]          try             AssignFile(plik, Utf8ToAnsi(SaveDialog.FileName));             Rewrite(plik);             Form1.QueryReports.First;             Count := Form1.QueryReports.FieldCount - 1;             pg.Max := Form1.QueryReports.RowsAffected;             pg.Position := 0;             Form1.Datasource1.Enabled := False;             Form1.Datasource1.DataSet := nil;             //nagłówek             for i := 0 to Count do               begin                 if i = Count then                   Writeln(plik, '"' + Form1.QueryReports.FieldDefs[i].DisplayName + '"')                 else                   Write(plik, '"' + Form1.QueryReports.FieldDefs[i].DisplayName + '"' + ',');               end;             //zawartość pliku             while not Form1.QueryReports.EOF do               begin                 for i := 0 to Count do                   begin                     if i = Count then                       begin                         tmp_string:= '"' + StringReplace(Form1.QueryReports.Fields[i].AsString, '"', '^', [rfIgnoreCase, rfReplaceAll]) + '"';                         tmp_string:= StringReplace(tmp_string, #13, ' ', [rfIgnoreCase, rfReplaceAll]);                         tmp_string:= StringReplace(tmp_string, #10, ' ', [rfIgnoreCase, rfReplaceAll]);                         Writeln(plik, tmp_string);                       end                     else                       begin                         tmp_string:= '"' + StringReplace(Form1.QueryReports.Fields[i].AsString, '"', '^', [rfIgnoreCase, rfReplaceAll]) + '"' + ',';                         tmp_string:= StringReplace(tmp_string, #13, ' ', [rfIgnoreCase, rfReplaceAll]);                         tmp_string:= StringReplace(tmp_string, #10, ' ', [rfIgnoreCase, rfReplaceAll]);                         Write(plik, tmp_string);                       end;                   end;                 Form1.QueryReports.Next;                 pg.Position := pg.Position + 1;               end;             CloseFile(plik);             ShowMessage(Form1.GetLanText('Eksport zakończony!'));             pg.Position := 0;             Form1.Datasource1.DataSet := Form1.QueryReports;           except             on E: Exception do               Form1.Log('ERROR >> ' + E.Message + ' ' + IntToStr(E.HelpContext));           end;    Thanks in advance ---------------------------------------------------------------------- BlueIcaro · 2013-12-30 22:28 Try to compile your program with program, with use HeauTrc checked. You can find it on: Options project (shift+ctrl+F11)-->> linking. When you close it, you will see some info about the use of memory, and if there is some leak (http://www.freepascal.org/docs- html/rtl/heaptrc/ /BlueIcaro ---------------------------------------------------------------------- mith · 2013-12-31 08:17 Many thanks BlueIcaro for your quick answer. I did as you wrote. It's confirmed that I have a memory leak, but unfortunately I think it's inside the SQLQuery unit.  (I turned off "write" to a file, keeping simply iterating through SQLQuery by using '.next'). I made a DumpHeap after each 10000 records saved. You can see results below. Don't know what next, probably create a bug? After first 10000 records: Code: [Select]Heap dump by heaptrc unit 1002361 memory blocks allocated : 886168757/890603048 944126 memory blocks freed     : 452070306/456341968 58235 unfreed memory blocks : 434098451 True heap size : 448954368 (112 used in System startup) True free heap : 10104368 Should be : 10966136 Call trace for block $087554F8 size 28   $004CABEA  TFORMA_RAPORTY__MENUITEM14CLICK,  line 200 of form_raporty.pas   $00601568  TMENUITEM__CLICK,  line 83 of ./include/menuitem.inc   $00601B26  TMENUITEM__DOCLICKED,  line 278 of ./include/menuitem.inc   $0040C0F6   $75877694   $75878BAA   $75878468   $75879C40 Call trace for block $087A07B8 size 14   $004CABBD   $00601568   $00601B26   $0040C0F6   $75877694   $75878BAA   $75878468   $75879C40 Call trace for block $4140A270 size 42764 [...] On 20000 records: Code: [Select]Heap dump by heaptrc unit 1828722 memory blocks allocated : 1735404672/1743811016 1760487 memory blocks freed     : 873666230/881869944 68235 unfreed memory blocks : 861738442 True heap size : 885948416 (112 used in System startup) True free heap : 18618432 Should be : 19640192 [...] On 30000 records: Code: [Select]Heap dump by heaptrc unit 2660904 memory blocks allocated : 2585303174/2597715304 2582669 memory blocks freed     : 1295924723/1308094224 78235 unfreed memory blocks : 1289378451 True heap size : 1322942464 (112 used in System startup) True free heap : 27132464 Should be : 28314232 [...] After a while: RunError(203), EOutOfMemory Once again - thanks for your help. ---------------------------------------------------------------------- BlueIcaro · 2013-12-31 18:23 Quote from: mith on December 31, 2013, 09:17:57 am Code: [Select]Heap dump by heaptrc unit 1002361 memory blocks allocated : 886168757/890603048 944126 memory blocks freed     : 452070306/456341968 58235 unfreed memory blocks : 434098451 True heap size : 448954368 (112 used in System startup) True free heap : 10104368 Should be : 10966136 Call trace for block $087554F8 size 28   $004CABEA  TFORMA_RAPORTY__MENUITEM14CLICK,  line 200 of form_raporty.pas (..)  Hi, the first think you should  check is the procedure Menuitem14click, in the line 200 of form_raporty.pas, here acording to the memory leak report, you have same object that never is free. /BlueIcaro ---------------------------------------------------------------------- mith · 2014-01-01 10:52 Thanks BlueIcaro, Menuitem 14 Click is a procedure to export data to csv. The code from this procedure I posted in first message. I reported an issue here: http://bugs.freepascal.org/view.php?id=25477 ---------------------------------------------------------------------- BigChimp · 2014-01-01 11:08 Unfortunately the bug you reported is not reproducible the way it is now - please read the note on the bug report. (Also: TSQLQuery is part of FPC - the fcl-db package - which is different from Lazarus. If you are sure the problem lies with tsqlquery, please provide an *FPC* sample project - see the link in the bug report on an example. If you think the problem has to do with Lazarus use of db components, please provide a Lazarus example - but please also see the link: the program should be self-contained so it can be used to reproduce the bug). Thanks. ---------------------------------------------------------------------- mith · 2014-01-01 13:11 Sample lazarus app has been added with DDL. (Into reported bug) Thanks and sorry ---------------------------------------------------------------------- BlueIcaro · 2014-01-01 13:31 What do you have on line 200?. Why this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Form1.Datasource1.DataSet := nil;  /BlueIcaro ---------------------------------------------------------------------- mith · 2014-01-01 13:42 The report is initially displayed in DBgrid. I have to turn off displaying before export, because it dramatically slowing process. Simple and complete application example I have added to reported bug (it is without displaying and unnecessary code). ---------------------------------------------------------------------- BigChimp · 2014-01-01 13:57 Quote from: mith on January 01, 2014, 02:11:16 pmSample lazarus app has been added with DDL. (Into reported bug) Thanks and sorry No problem. Simplified it to an FPC console program and see the problem in FPC 2.6.2 x86 and trunk x86 on Windows. Will comment on bug report if I find out more. ---------------------------------------------------------------------- mangakissa · 2014-01-01 14:08 Quote from: mithThe report is initially displayed in DBgrid. I have to turn off displaying before export, because it dramatically slowing process.Use enable/disablecontrontrols to set your display of. Code: [Select]begin    try      SQLQuery.disablecontrols;      ......   finally      SQLQuery.enablecontrols;   end; end; ---------------------------------------------------------------------- mith · 2014-01-01 14:17 Quote from: mangakissa on January 01, 2014, 03:08:03 pmQuote from: mithThe report is initially displayed in DBgrid. I have to turn off displaying before export, because it dramatically slowing process.Use enable/disablecontrontrols to set your display of. Code: [Select]begin    try      SQLQuery.disablecontrols;      ......   finally      SQLQuery.enablecontrols;   end; end; Thank you for your advice. ---------------------------------------------------------------------- mith · 2014-01-05 22:39 Quote from: mith on December 30, 2013, 11:18:55 pmI'm trying to export data to a *.csv file using a TSQLQuery (connected to a postgres database). Everything works fine for a small results. When the result is bigger (for example 80k rows, each with 6 fields of varchar 255 - which isn't of course huge amount of data), my program exhausts all possible memory (more than 1,5GB). I'm wondering what's wrong. D For anyone who has this problem. Please use UniDirectional:=True (before Open dataset) on TSQLQuery. It should help. ---------------------------------------------------------------------- BigChimp · 2014-01-07 13:03 Mmm yes. I suppose building memory management into bufdataset is asking too much... Closing the bug and added some docs http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery#Out_of_memor y_errors @@Lacak: I suppose you're right that there's nothing much we can do about bufdataset. Have added some docs http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery#Out_of_memor y_errors @Michał: please close the bug report if it's ok with you. Thanks.