Keeping connection alive - mysql

Question

I have set up my delphi application that I created with a button named Connect. Once I click it it executes the following code:

begin
    someConnection.Connected:=true;
    somecomenziDataSet.Active:=true;
end;

Similarly I have a button for Disconnect purposes which does the same thing but with =false;

My problem is the database I'm connecting to is hosted on a shared hosting account and the mysql server has wait_timeout variable set to 60 seconds, interactive_timeout is set to 30 seconds. Naturally, this disconnects me if I don't use my app for 60 seconds.

Is there any way to keep that connection alive?

Hosting company won't change the setting so i'm stuck with it.

I'm using RAD Studio 10 Seattle, dbexpress components, TSQLConnection and my database is mysql

Please do let me know in a comment if I left any necessary info out, thanks!

@kobik Could you provide a code example for this? I did come across mysql_ping in googling this and I thought about adding that to a TTimer but haven't yet found a code example on how to do this, make it an answer if you want so I can accept, I'd appreciate it, thanks! — t1f, Nov 15 '16 at 18:20
Maybe @Kobik is thinking of doing a "SELECT 1" (which involves little server activity or network traffic). — MartynA, Nov 15 '16 at 18:29
@MartynA - wouldn't that interfere with the select statement i already have set up in my dataset? — t1f, Nov 15 '16 at 18:34
It needn't - you could use a separate query component to do it. — MartynA, Nov 15 '16 at 18:35
You can have numerous queries sharing the same connection. Just create another query for the sole purpose of periodic activity. — Jerry Dodge, Nov 15 '16 at 18:36
@MartynA, yes was basically what I had in mind. in ADO/SQL Server you could simply do: `TADOConnection.Execute(' ')` (space as command). maybe MySQL has similar. with `TSQLConnection` you could also do `TSQLConnection.Exceute` with the query you suggested. — kobik, Nov 15 '16 at 19:04
I think a `TSQLConnection.Exceute('DO 0', nil)` in a timer will do the job. — kobik, Nov 15 '16 at 19:20
@kobik Indeed, just found the property description - it keeps a connection active when no dataset is connected/active, that's about it. Thanks for the answers :) — t1f, Nov 15 '16 at 19:22

score 8 · Accepted Answer · edited Jan 08 '20 at 02:21

8

You can use a TTimer to periodically "ping" the database (e.g. interval of 10-20 seconds given a 60 sec timeout):

procedure TMyDataModule.ConnectionPingTimer(Sender: TObject);
begin
  if not MySQLConnection.InTransaction then
    MySQLConnection.Execute('DO 0', nil); // or "SELECT 1" or whatever is cheapest
end;

edited Jan 08 '20 at 02:21

t1f

3,021
3
31
61

answered Nov 15 '16 at 19:48

kobik

21,001
4
61
121

Thanks, it works - just tested it. Is there any difference between DO 1 and DO 0 ? I found a nice discussion that uses DO 1 as an example and argues it's better (marginally faster) than a select 1 or a mysql_ping - any thoughts? – t1f Nov 15 '16 at 21:12
2

no difference between `DO 0` and `DO 1` which evaluates an expression without returning a value as opposed to `SELECT 0` or `SELECT 1` which return a result set. the differences in network traffic however are neglectable. see also: http://stackoverflow.com/q/2546868/937125 – kobik Nov 16 '16 at 07:36
I see, yup, that's what I was reading, ok .. thanks for the answer : ) – t1f Nov 16 '16 at 09:52
1

Typo in `Exceute` – Peter Chaula Jun 14 '19 at 10:37

Keeping connection alive - mysql

1 Answers1