getSQLDriverMySQLDirect under Red Hat 7.2 with Kylix - Socket Leak

[leroyflo]

I use libsqlmda.so.2.60 to connect on Mysql database with my program.
My config file seems like :

Code: Select all

[DRIVER]
GetDriverFunc=getSQLDriverMySQLDirect
LibraryName=libsqlmda.so.2.60
VendorLib=libmysqlclient.so
[CONNECTION]
DriverName=MySQL Direct (Core Lab)
HostName=host
Database=dbname
User_Name=root
Password=
BlobSize=-1
FetchAll=True

After each connection to the database I do, one socket stay open even if I close and free My SQLCOnnection object. (/proc/"processid"/fd)
After 1000 connection the program doesn't want to do anything else, is freezed, because of these opened sockets.

I try with libsqlmda.so.3.10 and the problem is the same.

I try on a oracle database with libsqloda.so.2.50 and no problem, each socket are closed after usage.

I test to with direct connection It work with no problem :

Code: Select all

[DRIVER]
GetDriverFunc=getSQLDriverMySQL
LibraryName=libsqlmda.so.2.60
VendorLib=libmysqlclient.so
[CONNECTION]
DriverName=MySQL Direct (Core Lab)
HostName=192.168.0.30
Database=perti313
User_Name=root
Password=
BlobSize=-1
FetchAll=True

But it is not my favourite solution because of the multiplicity of libmysqlclient.so who exist for each OS.

Have you a solution to fix this problem with dbExpress for Mysql?

Thanks

[Antaeus]

Please supply some more information:
- Does the problem appear with the same settings for Direct mode (HostName, Database)?
- When does the problem appear (after simple connect/disconnect, after executing any query like SELECT 1, after executing a specific query)?
- What version of MySQL are you using?

[leroyflo]

- What do you call direct mode ? can you explain ?
isn't it getSQLDriverMySQLDirect or getSQLDriverMySQL?

- the socket leak appears :
* on a simple connect/disconnect
* on a simple select request
* on a update request
* on a insert request

- I have tested with :
* Mysql 4.0.21-Max on Linux
* Mysql 3.23-53 - Max on Windows
* Mysql 4.1.14-nt-max on Windows
And the leak appears for each.
Precision : I use InnoDB database.

[Antaeus]

- What do you call direct mode ? can you explain ?
isn't it getSQLDriverMySQLDirect or getSQLDriverMySQL?

The getSQLDriverMySQLDirect function represents direct mode, getSQLDriverMySQL - client mode.
In direct mode client MySQL library is not used.

* Mysql 3.23-53 - Max on Windows
* Mysql 4.1.14-nt-max on Windows

Does it mean that you connect to MySQL for Windows from Linux?

[leroyflo]

The getSQLDriverMySQLDirect function represents direct mode, getSQLDriverMySQL - client mode.
In direct mode client MySQL library is not used.

Yes , this in Direct Mode that the leak appear, not in client mode.

I only change my config file for DirectMode for using libsqlmda.so.3.10 instead of libsqlmda.so.2.60.

oes it mean that you connect to MySQL for Windows from Linux?

Yes :
I try first on 3.23 on windows from my Linux program.
Then, when I identified the problem, I test on Mysql 4.0 on the same Linux computer where execute my program.
And then for precision I test on an another Windows computer with 4.1 Mysql always with my program under Linux computer.

[Antaeus]

We can not reproduce the problem. Please perform the following tests:
1) Try to reproduce the problem with the BlobPictures demo of DbxMda.
2) Try to reproduce the problem connecting from another host.
3) Can you see the lost connections executing the SHOW PROCESSLIST command on the MySQL server to which you connect. If you see them, what status do they have?

[leroyflo]

1) I try your demo but it doesn't correspond to my problem : it doesn't disconnect and reconnect, if y add 2 buttons with code :

Code: Select all

procedure TfmMain.Button1Click(Sender: TObject);
begin
SQLConnection.Close;
end;

Code: Select all

procedure TfmMain.Button2Click(Sender: TObject);
begin
SQLConnection.Open;
end;

I can reproduce the socket leak.

2) I try my program on another host, always RedHat7.2, with an Mysql 3.23-53 max Linux Database, and I reproduce the problem.

3) I can't see lost connections with show processlist command.

-------------------------------------

I try a simple application program, and I reproduce my leak :

Code: Select all

program Leak;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  SqlExpr,
  Classes;

var tmpSQLCOnnec : TSQLConnection;
    tmpErrorString : TStringList;
    i:integer;
begin
  { TODO -oUser -cConsole Main : Insert code here }
  try
  try

      //1 try
      tmpSQLCOnnec := TSQLConnection.Create(nil);
      tmpErrorString := TStringList.create;
      tmpSQLconnec.ConnectionName := 'MySQL Direct (Core Lab)';
      tmpSQLCOnnec.DriverName := 'MySQL Direct (Core Lab)';
      tmpSQLCOnnec.GetDriverFunc := 'getSQLDriverMySQLDirect';
      tmpSQLCOnnec.VendorLib := 'not used';
      tmpSQLCOnnec.LoginPrompt := true;
      tmpSQLCOnnec.KeepConnection := true;
      tmpSQLCOnnec.LibraryName := './libsqlmda.so.2.60';

      tmpSQLCOnnec.Params.Values['HostName']:='localhost';
      tmpSQLCOnnec.Params.Values['Database'] :='perti313';
      tmpSQLCOnnec.Params.Values['User_Name'] :='root' ;

      tmpSQLCOnnec.Open;

      tmpSQLCOnnec.Close;
      tmpSQLCOnnec.Free;
  except on e: exception do
      tmpErrorString.Add(e.ClassName + ':'+e.message);
  end;
  sleep(10000); //here you have time to make : ll /proc/processid/fd/
  try
  //2eme try
      tmpSQLCOnnec := TSQLConnection.Create(nil);
      tmpSQLconnec.ConnectionName := 'MySQL Direct (Core Lab)';
      tmpSQLCOnnec.DriverName := 'MySQL Direct (Core Lab)';
      tmpSQLCOnnec.GetDriverFunc := 'getSQLDriverMySQLDirect';
      tmpSQLCOnnec.VendorLib := 'not used';
      tmpSQLCOnnec.LoginPrompt := true;
      tmpSQLCOnnec.KeepConnection := true;
      tmpSQLCOnnec.LibraryName := './libsqlmda.so.2.60';

      tmpSQLCOnnec.Params.Values['HostName']:='localhost';
      tmpSQLCOnnec.Params.Values['Database'] :='perti313';
      tmpSQLCOnnec.Params.Values['User_Name'] :='root' ;

      tmpSQLCOnnec.Open;
      tmpSQLCOnnec.Execute('Select * from UserAccount', nil);
      tmpSQLCOnnec.Close;
      tmpSQLCOnnec.Free;

  except on e: exception do
      tmpErrorString.Add(e.ClassName + ':'+e.message);
  end;
  sleep(10000); //here you have time to make : ll /proc/processid/fd/

  try
  //3rd try a loop of connect/disconnect
      i:=0;
      tmpSQLCOnnec := TSQLConnection.Create(nil);
      tmpSQLconnec.ConnectionName := 'MySQL Direct (Core Lab)';
      tmpSQLCOnnec.DriverName := 'MySQL Direct (Core Lab)';
      tmpSQLCOnnec.GetDriverFunc := 'getSQLDriverMySQLDirect';
      tmpSQLCOnnec.VendorLib := 'not used';
      tmpSQLCOnnec.LoginPrompt := true;
      tmpSQLCOnnec.KeepConnection := true;
      tmpSQLCOnnec.LibraryName := './libsqlmda.so.2.60';

      tmpSQLCOnnec.Params.Values['HostName']:='localhost';
      tmpSQLCOnnec.Params.Values['Database'] :='perti313';
      tmpSQLCOnnec.Params.Values['User_Name'] :='root' ;

  while (i < 100) do begin
      tmpSQLCOnnec.Open;
      tmpSQLCOnnec.Close;
      i:=i+1;
  end;
    tmpSQLCOnnec.Free;
  except on e: exception do
      tmpErrorString.Add(e.ClassName + ':'+e.message);
  end;
  sleep(30000); //here you have time to make : ll /proc/processid/fd/

  finally
       tmpErrorString.SaveToFile('./error.log');
       tmpErrorString.Free;
  end;
end.

[Antaeus]

Thank you very much for your help. We have reproduced this problem and fixed it. This fix will be included in the next build of DbxMda.

[leroyflo]

Thanks to you for your efficiently and for the future build of DbxMda.