Description:
My company appears to track TCP connections and drop them in an idling period less than 2 hours. On seeing unexpected keep-alive packets going to ports of dropped connections, trackers may send reset packets to each side, according to a post by Gordon Burditt in the thread Lost connection to Mysql server during query of comp.databases.mysql Jan 7, 2010,
https://groups.google.com/group/comp.databases.mysql/browse_thread/thread/97739432d9574cba...
===========================================================================
Some background on TCP keep-alive parameters in Windows. Servers and clients enabling a "keep alive" option of their TCP connections thus request their TCP stack to send a packet to the other party after a period of silence. In Windows, a registry value KeepAliveTime (2 hours) controls this period. The stack will repeat keep-alive request every KeepAliveInterval (1 second) until receiving response.
http://support.microsoft.com/kb/314053
===========================================================================
Since MySQL server and client configuration pays attention to network timeouts, it should pay attention to default keep-alive values and, probably, suggest a work-around against zealous connection trackers.
How to repeat:
\cygwin\bin\date +"%Y-%m-%d %H:%M:%S" & "\Program Files\MySQL\MySQL Server 5.0\bin\mysql.exe" -uXXX -pYYY -hSERVER -e "select sleep( 7500 )" & \cygwin\bin\date +"%Y-%m-%d %H:%M:%S"
2011-07-11 19:21:21
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
2011-07-11 21:32:17
Suggested fix:
According to the newsgroup thread, one can work around connection tracking policies by setting the KeepAliveTime parameter of the active network interface of either the server or the client to a lower value such as decimal 300000 (milliseconds) which corresponds to 5 minutes.
The Windows installer of MySQL server, client and the configuration editor of MySQL workbench administrator should suggest this modification to the user.
Instead of or in addition to the registry change, the MySQL connectivity library should stop relying on the OS default value for the TCP keep-alive option. It should take a cue from a new my.ini configuration option and new SET variable. The description of the option should mention possibility of connection trackers disrupting idle TCP connections in less than 2 hours and suggest a shorter default value such as 5 minutes.
© 2024, Oracle Corporation and/or its affiliates