TCP keep Alive

TCP keep Alive

Postby flafleur » Mon 24 Feb 2014 14:00

I need more explanation on TCP keep alive. I want to know if DevArt class Devart.Data.MySql.MySqlConnection support TCP keep alive. If so, how to use it?
Here is my problem with a long query on remote host with a direct IP connection. I try to do a “checksum table …” on a large table but this query takes more than 1 hour to run. The query is completed on the server but the client never receives the result. It seems somewhere on the network the link between the server and client is cut but client and server are not knowing it. I’m not managing the network between the client and server but it seems somewhere on it, it has a router or NAT that cut the link after 1 hour. But when I run it directly on server or on a computer on the same switch on the network, I didn’t get this issue.

To solve this, I add a parameter to Windows’ registries “KeepAliveTime”. This solve my problem and the client receive the result. But I’m not sure is the best way. Regarding what I read on the registry entry “KeepAliveTime”, I read we should not need to change this. The client application should manage this. In my case I think MySqlConnection should manage it and send a packet (null packet?) to ensure the connection will not be cut by any router or NAT.

Could you help me to understand how to manage TCP keep alive with Devart dotConnect for MySQL (6.80)?

Thanks,
François
flafleur
 
Posts: 6
Joined: Mon 08 Nov 2010 16:29

Re: TCP keep Alive

Postby Pinturiccio » Fri 28 Feb 2014 13:14

You have also sent us the letter where you have written:
flafleur wrote:I need to use tcp keepalive option that already exist with MySQL Connector/Net within the connection string variable “Keepalive=900;”

When you use “Keepalive=900;” with MySQL Connector/Net, does it solve the issue?

flafleur wrote:Does dotConnect for MySQL have an option like that or a workaround?

Try using PingInterval property of MySqlConnection object and set it value to 900. For more information, please refer to http://www.devart.com/dotconnect/mysql/docs/?Devart.Data.MySql~Devart.Data.MySql.MySqlConnection~PingInterval.html
Pinturiccio
Devart Team
 
Posts: 1862
Joined: Wed 02 Nov 2011 09:44

Re: TCP keep Alive

Postby flafleur » Fri 28 Feb 2014 14:50

When I use MySQL Connector/Net with « keepalive=900 » that solve the issue, but I don’t want to switch to MySQL Connector/Net.

Regarding the “pinginterval” property of MySQLConnection object, I did try it and this not resolve the issue. The ping seems to work only on an unused connection, a connection that not waiting for a reply.
I did some monitoring on the packet sent by the ping and here is the result.

Devart.Data.MySql.MySqlConnection oCon = new Devart.Data.MySql.MySqlConnection("server=hostserver;uid=user;Port=3306;"pwd=password;database=db;");

//Ask for a ping each 15 seconds.
oCon.PingInterval = 15;
oCon.Open();

//Do waiting the open connection with a sleep. Packets are exchanged between client/server each 15 sec.
System.Threading.Thread.Sleep(60000);

//Long query. For this test, I use a sleep but my issue is with a checksum table but its same result.
Devart.Data.MySql.MySqlCommand oCmd = new Devart.Data.MySql.MySqlCommand("select sleep(150000);", oCon);

//Command is long so the commandtimeout is set to no timeout
oCmd.CommandTimeout = 0;

//while this command is executing, no packet are shared between client and server. It seems the ping interval doesn’t work when the client is waiting a reply from the server. But with "keepalive=900" from MySQL Connector/Net packet are still share between client/server
Devart.Data.MySql.MySqlDataReader oDR = oCmd.ExecuteReader(CommandBehavior.CloseConnection);


In conclusion the "pinginterval" doesn't solve my issue and I want to know if this is normal in the context this situation.

thanks,

François Lafleur
flafleur
 
Posts: 6
Joined: Mon 08 Nov 2010 16:29

Re: TCP keep Alive

Postby Pinturiccio » Fri 28 Feb 2014 16:10

We have answered you via e-mail.
Pinturiccio
Devart Team
 
Posts: 1862
Joined: Wed 02 Nov 2011 09:44

Re: TCP keep Alive

Postby Pinturiccio » Thu 27 Mar 2014 15:06

We have added 'keep alive' parameter to the connection string, that enables sending TCP keep-alive packets and specifies the interval at which they are sent. New build of dotConnect for MySQL 8.3.125 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/mysql/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=29242
Pinturiccio
Devart Team
 
Posts: 1862
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for MySQL