Lost connection to MySQL server during query

Lost connection to MySQL server during query

Postby solata » Sat 13 Aug 2005 11:22

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.
solata
 
Posts: 12
Joined: Tue 12 Jul 2005 07:29

Postby GEswin » Sat 13 Aug 2005 18:25

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
GEswin
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

Postby Guest » Sun 14 Aug 2005 15:31

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?
Guest
 

Postby GEswin » Sun 14 Aug 2005 23:31

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
GEswin
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

Postby Paul » Mon 15 Aug 2005 10:09

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.
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Postby solata » Tue 16 Aug 2005 07:16

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
 
Posts: 12
Joined: Tue 12 Jul 2005 07:29

Postby solata » Tue 16 Aug 2005 13:05

Problem solved! 8)

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

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

Thanks GEswin and Paul.
solata
 
Posts: 12
Joined: Tue 12 Jul 2005 07:29

Postby GEswin » Tue 16 Aug 2005 14:25

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
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

Postby GEswin » Wed 17 Aug 2005 14:24

For CoreLab, I would add some parts of this thread to the FAQ :wink:
GEswin
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

Postby solata » Thu 18 Aug 2005 08:48

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.
solata
 
Posts: 12
Joined: Tue 12 Jul 2005 07:29

Postby Ikar » Mon 22 Aug 2005 08:16

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.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Postby solata » Mon 22 Aug 2005 09:17

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

It looks that components arent avare when server closes connection.
solata
 
Posts: 12
Joined: Tue 12 Jul 2005 07:29

Postby GEswin » Mon 22 Aug 2005 10:24

Why server closes connection ?? Small timeout from server side perhaps ?
GEswin
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

Postby solata » Mon 22 Aug 2005 11:14

Yes, because of wait_timeout or interactive_timeout. I think components should reconnect automatic, like DBX did.
solata
 
Posts: 12
Joined: Tue 12 Jul 2005 07:29

Postby Ikar » Mon 22 Aug 2005 11:38

> 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.
Ikar
 
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Next

Return to MySQL Data Access Components