Lazarus

Programming => Databases => Topic started by: mith on December 30, 2013, 11:18:55 pm

Title: TSQLQuery (memory leak)
Post by: mith on December 30, 2013, 11:18:55 pm

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

Title: Re: TSQLQuery (memory leak)
Post by: BlueIcaro on December 30, 2013, 11:28:06 pm

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/ (http://www.freepascal.org/docs-html/rtl/heaptrc/)

/BlueIcaro

Title: Re: TSQLQuery (memory leak)
Post by: mith on December 31, 2013, 09:17:57 am

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.

Title: Re: TSQLQuery (memory leak)
Post by: BlueIcaro on December 31, 2013, 07:23:34 pm

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

Title: Re: TSQLQuery (memory leak)
Post by: mith on January 01, 2014, 11:52:50 am

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 (http://bugs.freepascal.org/view.php?id=25477)

Title: Re: TSQLQuery (memory leak)
Post by: BigChimp on January 01, 2014, 12:08:54 pm

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.

Title: Re: TSQLQuery (memory leak)
Post by: mith on January 01, 2014, 02:11:16 pm

Sample lazarus app has been added with DDL. (Into reported bug)
Thanks and sorry

Title: Re: TSQLQuery (memory leak)
Post by: BlueIcaro on January 01, 2014, 02:31:40 pm

What do you have on line 200?.
Why this:

Code: Pascal [Select][+]

Form1.Datasource1.DataSet := nil;
 

/BlueIcaro

Title: Re: TSQLQuery (memory leak)
Post by: mith on January 01, 2014, 02:42:34 pm

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).

Title: Re: TSQLQuery (memory leak)
Post by: BigChimp on January 01, 2014, 02:57:12 pm

Quote from: mith on January 01, 2014, 02:11:16 pm

Sample 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.

Title: Re: TSQLQuery (memory leak)
Post by: mangakissa on January 01, 2014, 03:08:03 pm

Quote from: mith

The 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;

Title: Re: TSQLQuery (memory leak)
Post by: mith on January 01, 2014, 03:17:26 pm

Quote from: mangakissa on January 01, 2014, 03:08:03 pm

Quote from: mith
The 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.

Title: Re: TSQLQuery (memory leak)
Post by: mith on January 05, 2014, 11:39:40 pm

Quote from: mith on December 30, 2013, 11:18:55 pm

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. D

For anyone who has this problem.
Please use UniDirectional:=True (before Open dataset) on TSQLQuery. It should help.

Title: Re: TSQLQuery (memory leak)
Post by: BigChimp on January 07, 2014, 02:03:05 pm

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_memory_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_memory_errors

@Michał: please close the bug report if it's ok with you. Thanks.