App's memory increase after running Insert stored procedure

[nostradumbass]

Hi,

Using: D2007E, Zeos DBO 6.6.4, Firebird 2.1.1 (embedded).

After running code that calls an Insert stored proc, my application memory usage increases between 8K to 30K (different each time). This is not acceptable, as the application runs through the night, and by morning, all Windows memory is used up, and app crashes!

I've used FastMM4 to check memory leaks, but no memory leaks exist in my application.

Is it a bug in the component code?

Appreciate any help.
-ND



Here is my code:

begin
with datamod.ZSP_LOG_I do
begin
ParamByName('Log_Time').AsDateTime := Now;
ParamByName('Log_User').AsString := CurrentUser;
ParamByName('Log_Description').AsString := TErrorTypeStrings[Ord(AErrorType)] + Trim(ADescr);
try
ExecProc;
except
showmessage('Error adding log entry.');
end;
end;
datamod.ZSP_LOG_S.Refresh;
if pc_main.ActivePageIndex = 3 then
datamod.ZSP_LOG_S.First;
end;


...and here is the stored proc:

CREATE PROCEDURE LOG_I(
LOG_TIME TIMESTAMP,
LOG_USER VARCHAR(20) CHARACTER SET UTF8,
LOG_DESCRIPTION VARCHAR(200) CHARACTER SET UTF8)
AS
BEGIN
INSERT INTO LOG (

LOG_TIME,
LOG_USER,
LOG_DESCRIPTION)
VALUES (

:LOG_TIME,
:LOG_USER,
:LOG_DESCRIPTION);
END;


Note: The TZStoredProc component is dropped on a datamodule, and the StoredProcName property is set.

[trupka]

I have app that runs similar stored proc approx. every 15 sec 24/7 (Zeos/PostgreSQL/Delphi 2007-win32/Lazarus-linux) and it works perfectly over a year on both platforms.
Currently I'm trying to "expand" supported databases, including Firebird, so your problem is interesting issue for me. If you can post some sample code that produces that "leak", I'm willing to test it.

[nostradumbass]

Do as described above, and watch the memory usage in Task Manager. Every time the code is called (it is in a procedure that is called by a Timer event every 5 seconds), the memory increases between 20K and 200K (random).

[trupka]

Confirmed, something, somewhere, eats database resources (app memory slightly increases during time, also)
- it's definitely ZEOS issue
- it seems that only Interbase/Firebird are affected, at least FB1.5.x and 2.x. Didn't test with other databases (except Postgres).
- No memory leaks after closing app, probably "caused" by Interface memory manger.
It seems that bug will be hard to solve, but, who knows ... I will investigate this further.
Has anyone already reported this in [target=zeosbugs.firmos.at]zeosbugs[/target]?

[lukezeoslib]

Point to this problem, I highly recommand using fastmm4 in zeoslib's development and, developer can make a swtich in zeos.inc to turn on or off fastmm4 for compiling component.

[seawolf]

I think to have found 2 memory leaks:

1. TZFirebirdNativeLibraryLoader (ZplainFirebird10.pas,ZplainFirebird15.pas,
ZplainFirebird20.pas,ZplainFirebird21.pas)

declare FPrelocations which is never freed. So I suggest

{$IFDEF ENABLE_INTERBASE_CRYPT}
destructor TZFirebirdNativeLibraryLoader.Destroy;
begin
SetLength(FPreLocations,0);
inherited Destroy;
end;
{$ENDIF ENABLE_INTERBASE_CRYPT}

2. TZNativeLibraryLoader (ZPlainLoader.pas) declare FLocations which is never freed. So I suggest to add, in destructor Destroy,

destructor TZNativeNativeLibraryLoader.Destroy;
begin
if Loaded then
FreeNativeLibrary;
SetLength(FPreLocations,0);
inherited Destroy;
end;

[mdaems]

seawolf,

Must arrays of strings be deallocated? I was thinking this happens automatically when the variable gets out of scope?

BTW: we're experimenting with a way which completely rewrites this code at the moment.

Mark

[trupka]

Solved.
Problem is in ZDbcInterbase6Statement.pas, function TZInterbase6CallableStatement.ExecuteUpdatePrepared.
Here is explanation:
Function allocates statement handle (var StmtHandle: TISC_STMT_HANDLE) in line 921

Code: Select all

      StatementType := ZDbcInterbase6Utils.PrepareStatement(GetPlainDriver,
        GetDBHandle, GetTrHandle, GetDialect, ProcSql, StmtHandle);

but never frees it or pass it to another object which will be responsible for that. Actually, it does free it, but only in case of exception as writen in lines 945-950:

Code: Select all

    except
      on E: Exception do begin
        FreeStatement(GetPlainDriver, StmtHandle);
        raise;
      end;
    end;

So, every time function is called (eg. inside TZStoredProc.ExecProc), it allocates resources on server but never frees it. That explains why application (and server) memory continually increases.

Why FastMM4 leak checker doesn't work?
Line 932 and little note in Interbase APGuide explains that:
----
The isc_dsql_allocate_statement2() function is similar to the
isc_dsql_alloc_statement() function except that statement handles allocated using isc_dsql_allocate_statement2() are automatically reset to NULL when the database under which they are allocated is detached.
---
In other words - when app closes, all resources are freed - no leaks to detect.

Now I'll stop bothering and upload a patch against svn://zeos.firmos.at/zeos/branches/6.6-patches, rev636

NB: Also corected SQL_DIALECT_V6 constant in ZPlainFirebirdInterbaseConstants.pas. According to ibase.h, shuld be 3 instead 2.

[mdaems]

SVN rev. 637. Will be move to trunk and 6.6-patches in a while.

Nostradumbass, can you test your case to verify this also solves your problem?

Mark