TMySQL51Connection memory leak already on connect
lazarus / 16-Databases

----------------------------------------------------------------------
DimProfi · 2012-06-09 16:04

I made a post before about 5.0 leak. I tried some things and would be
"glad" to show the facts.

Code: [Select]uses ... mysql51conn;


Code: [Select]procedure TForm1.Button3Click(Sender: TObject);
var
   conn:TMySQL51Connection;
   c:integer;

begin
   for c:=0 to 10000000 do
   begin
      conn:=TMySQL51Connection.Create(nil);
      conn.HostName:='localhost';
      conn.DatabaseName:='?database?';
      conn.UserName:='?user?';
      conn.Password:='?password?';

      conn.Connected:=true;
      conn.Connected:=false;

      conn.CloseDataSets;
      conn.CloseDataSets;
      conn.Close;

      FreeAndNil(conn);
  end;
end;

Memory usage in Task-Manager:

FPC 2.6.0 - Win32-i386:
libmysql.dll - 5.1.63 (x32)
---
before start: const. 2.800 K
after start: max. 3.700 K
in 30 sec: max. 4.600 K
... and the growth continue.


FPC 2.6.0 - win64-x64:
libmysql.dll - 5.1.63 (x64)
---
before start: const. 3.292 K
after start: max. 4.600 K
in 30 sec: max. 5.800 K
... and the growth continue.


It makes no fun!


Update:
here is a memory leak too.

Code: [Select]procedure TForm1.Button3Click(Sender: TObject);
var
   conn:TMySQL51Connection;
   c:integer;

begin
      conn:=TMySQL51Connection.Create(nil);
      conn.HostName:='localhost';
      conn.DatabaseName:='?database?';
      conn.UserName:='?user?';
      conn.Password:='?password?';

      for c:=0 to 10000000 do
      begin
         conn.Connected:=true;
         conn.Connected:=false;
      end;

      conn.CloseDataSets;
      conn.CloseDataSets;
      conn.Close;

      FreeAndNil(conn);
  end;
end;

----------------------------------------------------------------------
ludob · 2012-06-09 19:30

I downloaded libmysql.dll 5.1.63 and repeated your test with a remote
database. I can wait as long as I wan't the memory won't go up. If I
run the same test with a local database (XAMP) I see the memory usage
going up but also after almost 4000 connections, the program aborts
with a 'can't connect error'. The local connection is much faster than
my network connection and my guess is that there is some connection
queuing going on in the mysql driver or perhaps even on the network
level. The network layer comes to my mind because when the program
crashes, I can't connect from MySQL Query browser neither for almost a
minute. There is no mysqld activity during that period. 

Can you redo the same test and slow down the connections by inserting
a sleep(50); ? The value 50 is working on my rather slow system. The
crash doesn't occur any more and the memory usage still fluctuates but
the peak value isn't increasing. If slowing the connections down
solves the problem then it is proven that there is no memory leak but
somewhere a resource freeing "backlog" that is getting longer and
longer.

Admittedly, the test you are doing is not a typical use of a database
I understand your concern though and once bitten, twice shy.

----------------------------------------------------------------------
DimProfi · 2012-06-09 20:01

I tried. Memory leak

I identified this problem in a real software which connects to REMOTE
MySQL-Server.
The software runs week without restart and RECONNECTS to a MySQL-
Server if the connection was lost. All fine and stable, but starting
with 20 MB it comes to 800(!) MB in 1-2 weeks 

So i began searching for a leak. And here it is 


While digging today, i made some more funny code with a leak.


What the hell is going on?


Code: [Select],dynlibs,ctypes;
Code: [Select]var
  Form1: TForm1;
  mysql_library_init: function (argc:cint; argv:PPchar;
groups:PPchar):cint;stdcall;
  mysql_library_end: procedure (); stdcall;

Code: [Select]procedure TForm1.Button1Click(Sender: TObject);
var c:integer;
    lib:TLibHandle;
    p:pointer;
    pp:^integer;
begin
   lib:=loadlibrary('libmysql_5.1.63_win64.dll');

   p:=GetProcedureAddress(lib,'mysql_server_init');
   if not assigned(p) then showmessage('err1!');
   Pointer(mysql_library_init):=p;

   p:=GetProcedureAddress(lib,'mysql_server_end');
   if not assigned(p) then showmessage('err2!');
   Pointer(mysql_library_end):=p;

   for c:=0 to 1000 do
   begin
      mysql_library_init(-1,nil,nil);
      mysql_library_end();

      //And two times more for a fun! :)
      mysql_library_end();
      mysql_library_end();
     sleep(50);
   end;

  UnloadLibrary(lib);

  showmessage('ok!');
end;

I inserted "sleep(50)" and now i can see how it increases in 4K / 5sec

----------------------------------------------------------------------
ludob · 2012-06-10 08:09

QuoteI tried. Memory leak
Here too. Left it running overnight with Process Explorer monitoring
it because Task manager was showing the memory used going down after a
while. I have also added a counter to see how much memory is leaked.
327000 cycles leak 13MB. That is 40 bytes per connection.
QuoteThe software runs week without restart and RECONNECTS to a MySQL-
Server if the connection was lost. All fine and stable, but starting
with 20 MB it comes to 800(!) MB in 1-2 weeks 
At 40 bytes per connection there would have been a lot of reconnects
to end up with 780MB. There is probably something else eating memory.
The libmysql 5.5 release notes mention again the fix of memory leaks
found when running the mysql test suites. A 40 byte per connection
leak can easily be overseen.
QuoteWhile digging today, i made some more funny code with a leak.That
shows the leak is indeed in libmysql. TMySQL51Connection does call
mysql_library_end and releases the dll when the last connection is
closed. I modified your previous test program a bit and added a second
connection that is connected all the time:
Code: [Select]procedure TForm1.Button3Click(Sender: TObject);
var
   conn,conn2:TMySQL51Connection;
   c:integer;

begin
   conn2:=TMySQL51Connection.Create(nil);
   conn2.HostName:='localhost';
   conn2.DatabaseName:='?database?';
   conn2.UserName:='?user?';
   conn2.Password:='?password?';
   conn2.Connected:=true;
   for c:=0 to 10000000 do
   begin
      conn:=TMySQL51Connection.Create(nil);
      conn.HostName:='localhost';
      conn.DatabaseName:='?database?';
      conn.UserName:='?user?';
      conn.Password:='?password?';

      conn.Connected:=true;
      conn.Connected:=false;

      conn.CloseDataSets;
      conn.CloseDataSets;
      conn.Close;

      FreeAndNil(conn);
  end;
end;This way sqldb never calls mysql_library_end or releases the dll
inside the loop. The result is that the memory usage (process
explorer) is completely flat. 0 bytes increase after 15000 cycles.
This confirms your last test that the leak is in initialising and
releasing libmysql.
If you think the leak in your production program is linked to this you
can use the same technique (adding a dummy connection that is
connected). When the connection is lost, sqldb won't know and as long
as you don't close the dummy connection in your code, libmysql won't
be released.

BTW when you lose the connection, how do you clean up the memory
associated with the broken connection (resultsets, ...)? If you just
reconnect when TMySQL51Connection raises an exception, you have
potentially a big leak.

----------------------------------------------------------------------
DimProfi · 2012-06-10 11:12

QuoteAt 40 bytes per connection there would have been a lot of
reconnects to end up with 780MB. There is probably something else
eating memory.
Oh, i forgot to tell you about 20 Threads in my program   And
something tells me, if there is one leak, it could be more in
libmysql   Or may be it would be much more unreleased memory if you
make some queries in a time before releasing.

QuoteThat shows the leak is indeed in libmysql. TMySQL51Connection
does call mysql_library_end and releases the dll when the last
connection is closed.
From this day on i began to hate mysql. Are the developers not smart
enough to make unleaking library? or what?  Gonna try to use mysql
just in php, where memoryleaks doesnt matter.
Is there alternative client for mysql? without .dll? Some MySQL
protocol component?

QuoteI modified your previous test program a bit and added a second
connection that is connected all the time:That's funny and cool code
I will implement it


QuoteBTW when you lose the connection, how do you clean up the memory
associated with the broken connection (resultsets, ...)? If you just
reconnect when TMySQL51Connection raises an exception, you have
potentially a big leak.
That's the point i thought if i have an exception it's better to
FreeAndNil everything i have inkl. TMySQLConnection. Ok, i'm gonna try
to live with it

----------------------------------------------------------------------
ludob · 2012-06-10 12:00

QuoteOh, i forgot to tell you about 20 Threads in my program  Ah. One
more reason to use the dummy connection trick. From
http://dev.mysql.com/doc/refman/5.0/en/mysql-library-init.html :
QuoteHowever, mysql_library_init() is not thread-safe in a multi-
threaded environment, and thus neither is mysql_init(), which calls
mysql_library_init(). You must either call mysql_library_init() prior
to spawning any threads, or else use a mutex to protect the call,
whether you invoke mysql_library_init() or indirectly through
mysql_init(). Do this prior to any other client library call.So, make
your dummy connection from the main thread as soon as possible.
QuoteIs there alternative client for mysql? without .dll? Some MySQL
protocol component?Not that I know of. The MySQL wire protocol is
proprietary and the MySQL licence makes it very difficult to make a
free or commercial client without paying a lot of money to MySQL.
There are a few drivers that are not dependent on libmysql (php
mysqlnd, .net, ...) but they are closely controlled by MySQL.
QuoteFrom this day on i began to hate mysql.Happened to me a long time
ago

----------------------------------------------------------------------
BigChimp · 2012-06-10 12:10

Just to add to the flames, but perhaps food for thought: if possible,
you might want to look at changing databases to PostgreSQL or
Firebird.
Both of these are well supported by FPC and have a more liberal
license than MySQL.

Firebird doesn't require much tuning or maintenance, and also has an
embedded version. PostgreSQL has as advantage that it can be
extensively tuned , it offers some more features out of the box than
Firebird (e.g. replication) and is probably most often used as a MySQL
replacement.

Of course, if your customer insists on MySQL, then... presenting a
proper business case with benefits-costs for migrating and staying on
MySQL could be in order...

----------------------------------------------------------------------
DimProfi · 2012-06-10 12:20

Thanx. You are reading my thoughts 

I didn't knew about so despotical politics of MySQL.
So i'm gonna change in a next time completely  to PostgreSQL.
It would be no problem for a customer.

MySQL sux!

----------------------------------------------------------------------
DimProfi · 2012-06-10 12:21

QuoteSo, make your dummy connection from the main thread as soon as
possible.
That was my plan too