Delphi - TSQLQuery leaving a proccess on MySQL even after been freed

Question

I am using DBExpress in Delphi 2007 to connect to a MySQL5 database server on the net.

It all works OK, until I try and upload a large amount of data. I am trying to insert 8000+ records into the database, one at a time in a loop, in the loop I pass the TSQLConection object to a function along with the data to be inserted.

The function creates a TSQLQuery object and runs the insert query, before freeing the TSQLQuery. when I run it on large sets of data, I get a messages saying there the MySQL server has to many connections. Looking in the process list for the MySQL server I see this.

+---------+------+-------------------------------+--------+---------+------+--------+-----------------------+
| Id      | User | Host                          | db     | Command | Time | State  | Info                  |
+---------+------+-------------------------------+--------+---------+------+--------+-----------------------+
| 2962500 | name | myispdomain.co.uk:27812       | data   | Sleep   |    3 |        | [NULL]                |
+---------+------+-------------------------------+--------+---------+------+--------+-----------------------+

There is one entriy for every TSQLQuery object I have created, and if I step through the code I can see a new one go in when I run ExecSQL(). I am calling FreeAndNil on the TSQLQuery and have tried calling Close before freeing it.

My MySQL connection settings are as follows

   ConnectionName := 'MySQLConnection';
   DriverName     := 'MySQL';
   GetDriverFunc  := 'getSQLDriverMYSQL';
   KeepConnection := TRUE;
   LibraryName    := 'dbxmys30.dll';
   LoadParamsOnConnect := False ;
   LoginPrompt := FALSE;
   Name := 'mySQLConnection';
   VendorLib := 'LIBMYSQL.DLL';
   TableScope := [tsTable,tsView];

    Params.Add('DriverName=MySQL');
    Params.Add('HostName=www.sample.com');
    Params.Add('Database=data'); 
    Params.Add('User_Name=myuser'); 
    Params.Add('Password=mypassword'); 
    Params.Add('BlobSize=-1');
    Params.Add('ErrorResourceFile=');
    Params.Add('LocaleCode=0000');
    Params.Add('Compressed=False');
    Params.Add('Encrypted=True');

If I set KeepConnection to False the problem goes away, but the time to run the queries goes up.

Is there a way to get around this?

Answer 1

have you tried wrapping your inserts into a transaction? that should only require one connection and should fix your speed issues.

-don

Answer 2

The obvious thing to do is refactor to use a single TSQLQuery and pass that in. If you create that with parameters, you can make it all much more efficient than making a whole new query for each data item.

Answer 3

Don't re-create your connection over and over again, nor your query. Use parameters for the query; open the connection once, populate the params of the query, execute it, close the query (but not the connection), populate the query params again, and execute it again.

Something like this (using Advantage Database Server, but the concept is the same):

// Both Create() calls should be followed by try..finally to ensure they're 
// cleaned up after. Omitted for brevity.
Conn := TAdsConnection.Create(nil);
// Configure connection parameters here
Conn.Open;

Qry := TAdsQuery.Create(nil);
Qry.AdsConnection := Conn;
Qry.SQL.Add('INSERT INTO SOMETABLE (COL1, COL2, COL3)');
Qry.SQL.Add('VALUES (:COL1, :COL2, :COL3)');
while not OtherTable.Eof do
begin
  Qry.ParamByName('COL1').AsInteger := OtherTable.FieldByName('COL1').AsInteger;
  Qry.ParamByName('COL2').AsString := OtherTable.FieldByName('COL2').AsString;
  Qry.ParamByName('COL3').AsDateTime := OtherTable.FieldByName('COL3').AsDateTime;
  Qry.ExecSQL;
  Qry.Close;
  OtherTable.Next;
end;
// Free query
Conn.Close;
// Free connection.

Answer 4

you could build a bigger insert, something like :

Qry := TAdsQuery.Create(nil);
Qry.AdsConnection := Conn;
String qry = "INSERT INTO SOMETABLE (COL1, COL2, COL3)";
String values = "";

while not OtherTable.Eof do
begin
  values += "('"  + OtherTable.FieldByName('COL1').AsString + 
            "','" + OtherTable.FieldByName('COL1').AsString + 
            "','" + OtherTable.FieldByName('COL1').AsString + 
            "'),";
  OtherTable.Next;
end;

//to remove last ',' and add query terminator ";"
values = values.SubString(0, values.Length()-1) + ";"; 

qry = qry + values; //build the bulk insert

Qry.Add(qry);
Qry.ExecSQL;
//Qry.Close() no need of this, exec executes the command and leave, theres nothing to close
// Free query
Conn.Close;

the insert statement should look like this: INSERT INTO (col1, col2, col3) values ('val1','val2','val3'), ('val1','val2','val3'), ('val1','val2','val3'), ..., ('val1','val2','val3');

With this, as Ken suggestion, you use only one connection but your app works faster since you insert more data in one query.

cons: -the data should be error free, since its more difficult to determine which set of data is causing problems since you are inserting hundreds of sets at a time. -The component doesn't work fine with large querys, so its better to split the data into chunks and process the inserts with for example 500rows at a time.

Im currently using this approach with inserts of 1000 rows (with 4 to 8 cols of data) without problems, i test it with 5000 rows, but throw an exception of 'lost connection during query' so i left it in 1000. I think tunning the server you could increase the data transfer, assuming the component is not the problem, in my case 1000 its enough.

At last, im using builder c++ not delphi, so the code i post may have errors

-Jose

Answer 5

ConnectionName := 'MySQLConnection';
DriverName     := 'MySQL';
GetDriverFunc  := 'getSQLDriverMYSQL';
**KeepConnection := TRUE;**    //   <---------- this setting is persisting the database connection, change to false if you want to disconnect 
LibraryName    := 'dbxmys30.dll';
LoadParamsOnConnect := False ;
LoginPrompt := FALSE;
Name := 'mySQLConnection';
VendorLib := 'LIBMYSQL.DLL';
TableScope := [tsTable,tsView];

It is not a good practice to disconnect reconnect repetitively, if your application is to query things more than once. The preferred way is to create a connection when it is first needed, and keep it open until it is no longer needed.

The command--->sleep indicates that you are connected to the database but no query (TSQLQuery etc.) is running currently. In fact if you're not creating a high load operation most likely you'll never see anything else but 'sleep'.

Answer 6

Hi Re0sless ! where you specified the mysql port ? i can't see it