Lost connection to MySQL server during query

[solata]

Hi!

I've read all previous posts on setting timeout variables on server.

I made test project and put on form MyConnection and Button click I create simple LMyQuery. Server interactive_timeout and wait_timeout are set to 10 sec.

Button click executes well till 10 sec. Ater 10 sec and 1st click I get lost connection error. MyConnection.Connected is still True. The next click is ok.

Code: Select all

procedure TForm1.BitBtn3Click(Sender: TObject);
var
  LMyQuery: TMyQuery;
  LQuery: string;
begin
  LMyQuery := TMyQuery.Create(nil);
  try
    LMyQuery.Connection := MyConnection1;
    LQuery := 'select * from table1';
    LMyQuery.SQL.Text := LQuery;
    LMyQuery.Open;
    ShowMessage('OK');
  finally
    LMyQuery.Close;
    FreeAndNil(LMyQuery);
  end;
end;

Is this bug? How can I resolve this? I have ported huge project to MyDAC and have stability problems because of Lost connection errors.

BTW. Setting timeout to huge number is not acceptable solution and MyConnection1.Ping does not help.

[GEswin]

It depends most of the size of the table you're opening. If it's huge and takes more than 10 seconds to load, then you get the timeout, only solution is increasing timeout. But most of the cases it's better to select down to just what you need.

Another issue is also if you're using big blobs (blobs filled with big images etc) , then i recommend to set:

Code: Select all

max_allowed_packet = 16M

in my.cnf (or my.ini if working with mysql windows).

Also if your server is over a WAN set compression to true.

If had no problems with big projects and huge databases after finetuning a little bit code and server.

You can try also direct := False (using mysql libmysql.dll library).

Regards

[Guest]

Thank you for great advices. I will try MyConnection1.Ping with Timer, so Connection wont get timeout.

What about Auto cloning connections? Is it in MyDAC also present?

[GEswin]

Hi,

I think you mix some things, if you're querying a table that takes long time, more than connection timeout, then ping won't help. Ping is interesting if you're for example displaying data in a grid and user is idle, then perform a ping from time to time just to keep some minimal traffic over the wan/lan, but really, it hasn't any sense, i never needed/used it. I have even a small app that works over GPRS connection, and in this case i perform ping before doing some jobs, just to make sure connection is still availabe (GPRS in spain is so bad that network connection drops very often or network connection is connected but no data transmited).

Another point, if you're pinging with a timer while a query is running i think you'll run in some problems. Use ping before opening a table for example, and if connection is dead, reconnected... but i say again, it's very strange this case, and i only found this needed on grps connections where network transfer is really very very bad.

Regards

[Paul]

MyDAC can reconnect if connection was lost during the short time. It happens if you donot use transactions and do not prepare commands explicitly. In this case you will not receive error message in user interface. This may be a reason of your described problem with MyConnection.Connected=True. We will check this.
If you want to set timeouts using MyDAC you can use TMyConnection.ConnectionTimeout and TMyCommand.CommandTimeout properties.

[solata]

Server timeout is supperior to TMyConnection.ConnectionTimeout and TMyCommand.CommandTimeout, so not helpfull in my case.

I wonder why connection timeout isnt reset after MyQuery1.Open (or Execute)? Only MyConnection.Ping resets timer on server. Sholudnt TMyQuery execute Ping by itself?

Why this doesnt work:

Code: Select all

try
  MyConnection1.Ping;
except
  MyConnection1.Close;
  MyConnection1.Open;
end;

When I get EMySQLConnection exception (related with timeout) program never executes the except block. Any idea?

[solata]

Problem solved!

In our project we used sqlconnection and sqlquery in threads. It works ok. But CrLab components arent tread safe, are they?

Soo we dont use threads with CrLab components and all works ok.

Thanks GEswin and Paul.

[GEswin]

Solata, mydac compos like most of other DB engines i've seen are not thread-safe sharing the same connection.

If you're using threads, then be sure that connection isn't used more than once at the same time, or have each thread it's own connection (I use this second solution, i have some small appz with threads and each one has it's own connection that connects/disconnects upon need).

Regards

[GEswin]

For CoreLab, I would add some parts of this thread to the FAQ

[solata]

Sorry or for mislead. The problem still exists. MyQuery and Ping brakes down if executed after timeout.

Code: Select all

try
  MyConnection1.Ping;
except
  MyConnection1.Close;
  MyConnection1.Open;
end;

Never gets to except block.

[Ikar]

To solve the problem at the same thread with TMyConnection and TMyQuery create TTimer with interval less than wait_timeout. Call MyConnection.Ping from Timer.OnTimer handler.

[solata]

Yes this is one solution, but sholdnt components have this integrated?

It looks that components arent avare when server closes connection.

[GEswin]

Why server closes connection ?? Small timeout from server side perhaps ?

[solata]

Yes, because of wait_timeout or interactive_timeout. I think components should reconnect automatic, like DBX did.

[Ikar]

> Yes this is one solution, but sholdnt components have this integrated?

We will consider this ability for future MyDAC versions.

A situation with small wait_timeout happens quite seldom.

For networks with often connection breaks (GPRS etc) checking for network connection before passing a query doesn't solve the problem as between Ping and execution of the main query the connection can be lost.