MySQL Bugs: #1557: Memory leak

Bug #1557	Memory leak
Submitted:	15 Oct 2003 4:18	Modified:	30 May 2013 9:56
Reporter:	Kenneth Breit Bedsted	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.06	OS:	Windows (Win 2000 + XP)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
We have an application that makes a connection to the MySQL database each 10th second. After running for some hours the memory usage has risen from 10mb to >400mb and increasing.

The exact same program used to run against an Oracle database and there the memoryusage was stable.

How to repeat:
I can repeat the problem on both Windows 2000 and Windows XP by doing this (Borland Delphi syntax):

ADOQuery := TADOQuery.Create(nil);
ADOQuery.ConnectionString:=FormMain.optConnString;
ADOQuery.CursorType:=ctOpenForwardOnly;
ADOQuery.CursorLocation:=clUseServer;
ADOQuery.LockType:=ltReadOnly;
ADOQuery.SQL.Text := 'SELECT * FROM some_table';
ADOQuery.Active:=TRUE;

In case you are wondering I'm using ODBC via ADO. But that has always been the case, also when we ran the program against an Oracle database.

Suggested fix:
Currently I have rolled back to the 3.51.04 version, however, that has given me a number of other problems which I hopefully can work around, but there is no memory leak.

I have tested the following:

This will make the memory usage explode:
for i:=0 to 10000 do begin;
    ADOC := TADOConnection.Create(nil);
    ADOC.ConnectionString:="Provider=MSDASQL.1;Password=mypwd;Persist Security Info=True;User ID=intranet;Data Source=AppLog"    
    ADOC.Open;
    ADOC.Free;
end;

The same code in VBS fales just the same:

strConnection = "Provider=MSDASQL.1;Password=mypwd;Persist Security Info=True;User ID=intranet;Data Source=AppLog"
for i = 1 to 10000
  Set objADO = CreateObject("ADODB.Connection")
  objADO.ConnectionString = strConnection
  objADO.Open
  objADO.Close
  set objADO = nothing
next

Duplicated with:

Private Sub Form_Load()
Dim objado As ADODB.Connection
For i = 1 To 10000
  Set objado = CreateObject("ADODB.Connection")
  objado.CursorLocation = adUseClient
  objado.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
            & "SERVER=127.0.0.1;" _
            & "DATABASE=test;" _
            & "UID=root;" _
            & "PWD=96298;" _
            & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384

  objado.Open
  objado.Close
  Set objado = Nothing
Next
End Sub

Win2k 3.51.06

Correction, I duplicated this with 3.52.00

I also have a forum member with a repeatable test case using BLOB columns at http://www.vbmysql.com/forums/viewtopic.php?t=2747 Looks like memory usage goes up by the size of the blob accessed. In addition, a related issue appears to be present at http://www.experts-exchange.com/Databases/Mysql/Q_20787840.html

Zak forwarded me the bug in hopes that I might be able to comment on it.  A few general 
observations and testings to try, as I haven't really looked into what might cause the behavior 
you're seeing.  One of the things I've noticed is the ODBC cursor is set to FORWARD_ONLY.  
Typically this is excellent as it provides an optimal amount of speed and efficency, but the memory 
leaking behavior would suggest that the cursor isn't behaving properly.  Rather than using it's 
cached results each time, the cursor is re-fetching them from the database and caching them again 
(this would be break the ACID rule for transactions).  

In an effort to nail this down a bit, try doing two changes.  First change the CursorLocation to the 
client, and see if that results in any changes in behavior other than potential speed increases.  
Unfortunately, Mike Hillyer's example shows that the behavior still holds true in that change, but 
more data points could always be helpful.  Second start over again, but this time use a 
SQL_CURSOR_DYNAMIC or a SQL_CURSOR_KEYSET_DRIVEN.  Yes, these will slow down the query 
time, but it will happen because you are asking for the results not to be cached.  If the memory 
performance does not change, it looks like this will take a jump into the source to examine for 
which I don't have the time to do right now.

Hi Dan!

I should point out that the memory leak (from my tests) happens without even retrieving records. As you can see in my example, I am opening and closing the connection without retrieving any records at all. 

Anyway if someone can dig around in the diffs between 3.51.04 and 3.51.06 it would be appreciated, this really kills things for people using ASP as I have talked to webmasters who see huge memory usage as the connections build and build.

Thanks for your help Dan, this should teach you not to do Zak any favors ;) (But I haven't learned my lesson yet... hehe)

Since I cannot seem to find a copy of the 3.51.04 source easily, did this happen in 3.51.05 as well.

Mike someday I will learn to listen to my inner voice that constantly remind me "Zak lies".  It'll take 
a few more years ;)  Also it's interesting to see this memory increase occurs with just opening a 
connection.  Can you tell me if the memory consumption levels are consistent?  For example, do 
you constantly go up the same amount per new connection, or is it sporadic in size?

Sorry for the delay in posting, this bug system does not inform late joiners of new messages. Anyway, if I open memory usage graphing while doing the loop I see a nice straight line rising up until I kill the app. Memory leak is O(n) and is not exponential.

Mark Matthews sent me an alpha 3.51.07 and the memory leak is gone, so looks like this is getting taken care of.

Someone should consider closing this. I just tried MikeH VB test using latest 3.51.07 and I did not detect a memory leak. Perhaps it is just in 3.52 (which is known to be incomplete and will soon be replaced by 3.53).

According PeterH's request (issue already resolved in 3.51.07).

Hi, the bug results closed, but actually with MyODBC version 3.51.07 + MySQL 4.0.18 (but also with latest 4.1 version) under windows XP professional MDAC 2.7 SP1 on windows XP SP1, Delphi 6 (with latest patches) the memory leak is still there, I used the test case by Kenneth.  The connection string that I use is (no password, it's a localhost, test only):

DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=root;PASSWORD=;OPTION=3;

I've even tried the 3.51.4 version, no result. The problems where there also on a windows 2000 professional SP4 and on a Windows 2003 Server.

Greetings, Marco.

I have the same problem with this memory leaking. We are using W2K, IIS, MySQL 4.0.18, MySQL ODBC 3.51.6. Our DLLHOST.EXE goes from 5 MB to 800 MB in a matter of 8 hours. Sometimes the IIS stop responding with 600 MB ~ 850 MB of memory usage. We´ve got a paliative action, stopping e starting the IIS again, but this a pain in the neck, because we have to contact all the sectors (about 80 workstations using our system) and ask for them to exit the program. If we do it without warning, all the users lost the job they are working in (with the restart of IIS, all the sessions are killed).
I´ve heard that with Apache and PHP this is not happening and that memory leaking is a problem of IIS. We are considering translate the whole system to PHP, but its a lot of work and we don´t know for sure if this will solve.
Any advices? If someone could help me... (sorry for any kind of English mistakes, I´m from Brazil)

MyODBC for test

Attachment: myodbc3-bugtest.zip (application/x-zip-compressed, text), 174.63 KiB.

Hi,

Marco and Oton.

Could you please test the myodbc3.dll I attached to the
files tab and comment the results you will get.

Thanks.

I have the same problem on a W2K-machine (SP4, all recent security/MDAC-updates) w/mySQL 4.1.0 alpha & ODBC-connector 3.51 (used sub-versions 05 to the last 07. Tried the myodbc3.dll-file recently posted here by Miguel Solorzano, but with that file the line "sTtl1 += '|geplaatst: '+String(rsTDb('plaatsdat'));" results in a JScript-error (rsTDb('plaatsdat') cannot be converted to string),  rsTDb('plaatsdat') representing [DATE_FORMAT(plaatsdatum,\"%d/%m/%Y %H:%i\") AS plaatsdat] in a previous SELECT statement.

It's a very nasty and persistant leak. I checked all my code (ASP JScript) and made sure every connection it makes is closed immediately. Additionally I always use local variables, and clean up everything that I can clean at the end of my ASP-scripts. For a week now I have tried many suggestions from a diversity of Win/MS-forums without any success and have to conclude that it's MySQL/MyODBC on this particular machine that's bugging me. Using a previous version (4.0.2 production w/ODBC3.51.06) on a different W2K-machine this problem didn't occur. That (very) old server crashed, so I can't go back to that.

If my English seems strange, that may be because I'm dutch. Greetings.

Sorry, forgot the most important feedback for the trial myodbc3.dll file: it looked like the dll-host memory resources where stable and the garbage was collected after the scripts ran. The script did not finish normally with the aforementioned error, but before crashing it did connect to my DB and did return a recordset.

Similar memory leak problems have been solved by 3.51.07; I'm setting this to "No Feedback", but I believe the problem has been fixed.

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.