[bug_fixed] Another memory leak MSSQL2000 TZQuery.ExecSQL

[Petya]

Hi All !

I have ZeosDBO 6.5.1-alpha build at 2004-11-14 03:26:23 - well, not the newest (just looked for the version right now) on Delphi 5 Enterprise.
I connect to MSSQL server 2000 via Zeos. Connection is fine.
Each time I load a query to TZQuery's SQL property, when I issue an ExecSQL statement for insertion or updation I got 240-270 bytes more mem usage from system.AllocMemSize (system.pas line 403). This number never decreases. If I do it several thousand times, mem usage is constantly increasing in Task Manager, and doesn't decrease even if I disconnect from database. It's freed fine if I exit my program, but my program is meant for constant operation. I've tried both static and dynamically created TZQuery components, same behaviour. Object creation and destroyment is fine.
If I comment out only the ExecSQL statement, the problem is away.
But I'm so ambitious that I need a better solution :-))

There have been some other topics related to memory leakage, so if anybody has same experience, or has information whether this is corrected recently or not, please post here.

Tomorrow I'll try the latest Zeos, will post results no matter good or bad.

Cheers,

Peter

[Petya]

So,

I looked after this issue a bit.
The first thing I did was trying another database server.
My assumpion was correct, there's no problem at all if I connect to MySQL 4.0 with the same program, without restarting it. I ported the corresponding tables fine.
Today I dl'ed the newest CVS build Mark posted tuesday - very sad results.
Despite the original problem didn't change any way, two new problems came up :

1. When I try to properly exit from my program it generates a GPF. It doesn't generate GPF if I don't connect to any database. This is only with MySQL. It's frustrating because I have other programs that use MySQL under the same IDE.

2. TZQuery now produces the following exceptions :

2006-10-05 12:43:31.892
SELECT * FROM Online WHERE CI=999 ;
General SQL Server error: Check messages from the SQL Server.
Invalid column name 'Online'.
2006-10-05 12:43:31.932
SELECT * FROM FTPCommands WHERE (FszgId=999) AND ((Command=1) OR (Command=2))
AND (StartDate IS NULL) AND (FinishDate IS NULL) ORDER BY IssueDate ASC ;
General SQL Server error: Check messages from the SQL Server.
Invalid column name 'FTPCommands'.

How the hell does it confuse the table name with a column name ?

Then I also tried the MySQL Server with the newest Zeos, he behaved the same way as with MS, but no memory leak as expected.

Interesting.


SUMMARY :

- old Zeos, MSSQL :
memory leak error
- old Zeos, MySQL :
fine !
- new Zeos, MSSQL :
memory leak error, confuses names inside itself
- new Zeos, MySQL :
GPF on exit


Anybody involved such way ?

Cheeers,

Peter

[fduenas]

Well I'm working correctly with this kind of queries and i dont' have this type of errors. what mysql client dll are you usign and what version of mysql you're using. because it seems here all works fine.

[Petya]

Well, it also occured to my mind that the client dll tricks me.

For Microsoft SQL Server 2000 I use one of two files :
- ntwdblib.dll 290816 bytes, 2005.05.04, VarFileInfo 2000.080.2039.00
- MSVCR71.dll, one is a renamed version of the ebove, another is
- MSVCR71.dll, 348160 bytes, 2003.02.21, VarFileInfo 7.10.3052.4
For each individual machine, one of the above works. It seems totally random, which one.

For MySQL I use libmysql40.dll from Zeos' library dir.

My problem belongs to Microsoft SQL Server 2000. MySQL was only a try whether the problem exists with other servers or not, to make sure it is Zeos related. I succeeded to isolate that it is.

So my problem is that my proggy takes 100MB RAM /hour during heavy load.
I must use Microsoft SQL Server 2000 because the ordering party has already bought that, and they wanna use that.

Thanks for any help,

Peter

[Petya]

I use MySQL 4.0 as I mentioned above, but it's no matter from the problem's point of view.

[Petya]

Next week I must dig myself deep into Zeos' source's internal structure, if I don't get any advice in a reasonable time - the thing I've never done before. Neither with Zeos, nor with any other third-party or native Borland component. I give myself a 20-30 % chance to succeed. As far as I know, trying to find & fix bugs in someone else's code is the hardest task any programmer can attempt to accomplish.

I'm still waiting for ANY information that has ANYTHING to do with the above.


Cheers,

Peter

[rautgigi]

Hi,

For memory leak, I think the problem is the error handle and the message handle. when receiving a error or a message from a mssql server Zeos will create some records, alloc memorie and save the error message. When calling the CheckError (if I rememeber correclty) it will call Dispose(ErrrList[Index]) something like this. The memorie leak is a common error for calling Dispose to a structure that holds strings. You should save the entry into a variable and call dispose to a typed variable.

You have something like :
while MSSqlMessages.Count > 0 do
begin
Dispose(MSSqlMessages.Items[0]);
MSSqlMessages.Delete(0);
end;
MSSqlMessages.Clear;
while MSSqlErrors.Count > 0 do
begin
Dispose(MSSqlErrors.Items[0]);
MSSqlErrors.Delete(0);
end;
MSSqlErrors.Clear;

and you should have :
var
lErrorEntry: PDBLibError;
lMesageEntry: PDBLibMessage;
.....
while MSSqlMessages.Count > 0 do
begin
lMessageEntry := MSSqlMessages.Items[0];
Dispose(lMessageEntry);
MSSqlMessages.Delete(0);
end;
MSSqlMessages.Clear;
while MSSqlErrors.Count > 0 do
begin
lErrorEntry := MSSqlErrors.Items[0];
Dispose(lErrorEntry);
MSSqlErrors.Delete(0);
end;
MSSqlErrors.Clear;

Regarding the other problem, I think you should run SQL Profiler and see what query is run on the sql side. This is a error from the sql not from the Zeos. It can be when retriving the metadata ... or something, but also on the SQL Profiler you will find the answer.

Edit:
I download the last version of ZeosLib and I think i found the problem. You have to modify ZDbcDbLibMsSqlMetadata.pas at line : 1599 [TableNamePattern] into [AQSNull(TableNamePattern)] from the :
with GetStatement.ExecuteQuery(......, [TableNamePattern]) do
into the :
function TZMsSqlDatabaseMetadata.GetColumns(...)



Hope this will help...
All the best

[mdaems]

Hi Rautgigi,

Thanks for your analysis of these problems. Do you use mssql? Please, change the necessary in your last version, check if 'everything' still works and send me the changed files (or an SVN patch file against testing branch if you use SVN). As we don't have very active mssql user in the dev team, this is the only way we can do decent bug fixing.

Thanx!!

Mark

[rautgigi]

Hi Mark,

Sorry for this late response. I will pack the file and post it here. I will configure the SVN and try to use it for future, but right now I'm a little bit busy. I use thi excelent library (ver. 6.1.5) for some time on the mssql without a problem and now I try to port some application to ver. 6.5. If I found some other problems, I will let you know.

All the best

[Petya]

Hi Rautgigi,

Congrats !
You have solved the mem leak problem.
TO ALL :
Please include this patch in the next release.
Hereby I confirm that Rautgigi's suggestion to Dispose variables instead of StringList elements eliminated the problem.
Thank you very much.

For the other problem I raised :
Now I must go to a lesson on the university. When I got back I will try the other one also.

YOU MADE MY DAY HAPPY
:-)

Cheers,

Peter

[rautgigi]

Hi,

This are the modified files. There are little modifications. For ZPlainDbLibDriver.pas it should be ok, for the MetaData, as far as i so, in the cvs version this problem ( the one with invalid column name) not exists any more, because a diffrent approach. I put also the modified file for the ZEOS_TESTING_REV_102.7z package.

Peter I'm glade to here that

All the best

[rautgigi]

Also the patch file for the ZPlainDbLibDriver.pas

[Petya]

I download the last version of ZeosLib and I think i found the problem. You have to modify ZDbcDbLibMsSqlMetadata.pas at line : 1599 [TableNamePattern] into [AQSNull(TableNamePattern)] from the :
with GetStatement.ExecuteQuery(......, [TableNamePattern]) do
into the :
function TZMsSqlDatabaseMetadata.GetColumns(...)

I can confirm this also worx. I've tried it as I promised and succeed.

Thanx again :-)

[mdaems]

Committed to SVN. Can you please check both things work and confirm here? Then I can mark this one as closed.

Mark

[Petya]

Confirmed.
Build 113 eliminated both errors.
Thank you again.

Cheers,

Peter