MySQL Bugs: #20286: Client hang on Error 2013 => Parameter "KeepAliveTime"

Bug #20286	Client hang on Error 2013 => Parameter "KeepAliveTime"
Submitted:	6 Jun 2006 8:56	Modified:	4 Jul 2006 12:06
Reporter:	Bertrand Lepoittevin	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	ALL	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
Hi,

Some clients will receive (after few seconds, or few minutes) an error 2013 whereas the server is not restarted and there is no network pb,
mainly after Select with one 64K blob and few concurrent connections (more than 100), but sometimes with update queries too.

Tables MyISAM are not fragmented (optimized), and implementation is good.

I tried to customize server parameters that could bypass this error, with no success.

Configuration :
- Clients V4.1.16
- Server V4.1.16, 4 processors, 2Gb Memory
  max Connections=1000
  net read / write timeout = 30/60 (Default config)
  Query cache activated

How to repeat:
This error is quite hard to repeat since it seems this occur with a few concurrent connection and concurrent queries.

Additionnaly, what are the other mysql daemon variables / parameters to adjust that could avoid this error ?

Thank you for a problem report. Please, send you my.ini files content. Is there anything unusual in the error log? 

Can you try to use newer versions, 4.1.20, and check is it also demonstrate similar behaviour?

MY.INI parameter file

Attachment: my.ini (application/octet-stream, text), 9.23 KiB.

Thanx for your Quick Reply.

My.ini file is attached, default variables i modify are on the #BLP Section.

Clients 4.1.16 are servers (2500) that connect to our central production database.
Update process (4.1.20) will be planified, but i did not see any bug corrections concerning my pb in the 4.1.20 History file.

error log .ERR is empty and no significant errors in the slow_query.log.

BTW, i have some some clients that encountered Error 2013 when executing "Insert" queries.

Mysql Error Log

Attachment: mysqlserver.err (text/plain), 18.84 KiB.

MySql Client Variables

Attachment: variables.csv (application/octet-stream, text), 4.85 KiB.

I modify variable "log_warnings" from value "1" to "2" to verbose warnings, and i've noticed some things :
- It seems that all 2013 erros are logged by the server inside the ERROR Log
(errs "Got an error reading communication packets" reported in attached file "mysqlserver.err")
- Error 2013 happens especially on WAN (ADSL)
- Some rare 2013 errors happens during connection process => the client is receiving the error immediately.
- Others 2013 client errors during queries (select, insert, delete) make the client pending the query during around 2 hours.

So, i've got some questions :
- Is there any variable (see attached file "Variables.csv") that could explain the 2 hours error delay ?
- How avoid or reduce this pending delay at the client from the aborted connection by the server (MySql Connection Ping Methot, ....) ?

Hi again,

After many tests, i confirm that the pb happens described as below :

- MySql Server detects client pb during query and log error into its log
(Error  "Aborted connection...host: `XXX.XXX.XXX.XXX' (Got an error reading communication packets)) 
- The 2 APis (mysql_real_query() and mysql_store_result()) hang during more than 2 hours (Around 2h and 10 mn) before it returns.

How can u explain this high timer ?

Hi,

Can you confirm this is a bug, that its still under analysis, cause i dont have any news since 2 weeks ?

Did you succeed in repeating this one ?

Hi

I Succeed in repeating this bug as described as below :

- From the client, i choose a small query which returns all rows (2000) of a table composed of 8 columns
- MySql_Real_Connect()
- Mysql_Real_Query()
- Launch API mysql_store_result(), few seconds after and before the end of API : unplug ethernet cable of the client, wait for 1mn (simulate the deconnection), and replug cable
(The client keeps the same IP Address)

=> MySql Server logged the error as "Aborted Connection [...] Got an error reading communication packets"
=> The Client will hang during the famous timeout (around 7900 sec.)

The same problem happens with newest client 4.1.20 (Server 4.1.16 unchanged)

Can you confirm me in repeating this bug ?

Problem copied in bug 20286
(http://bugs.mysql.com/bug.php?id=20286)

(Config)
Client & Server with Win2000 SP4
Client 4.1.16 or 4.1.20, Server 4.1.16
Client and Server on LAN Ethernet 100 Mbits, with a router 128 Kb to slow down the Bandwidth.

FORUM POST :
http://forums.mysql.com/read.php?21,99503,99870#msg-99870

The problems come with the TCP parameters "KeepAliveTime" which is set to 7200000 ms by default (2 hours) in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters.

Setting this parameter to a small value allow the TCP/IP Stack to refresh all the TCP sessions, and then the mysql client "knows" quicker the tcp session is KO, and returns 2013 error.

The best way could be to set a "KeepAliveTimer" parameter at the mysql connection, is this parameter can be added in the next futures relases of Mysql ?

So, it is not a bug in MySQL code. As for this your idea:

"The best way could be to set a "KeepAliveTimer" parameter at the mysql
connection, is this parameter can be added in the next futures relases of Mysql"

I think, it is up to the system administrator to decide what TCP/IP parameters to set and what values to use. So, I do not think MySQL's software should force these for him.

Adding "KeepAlive" parameter (in seconds) on the client side to allow it to refresh TCP Connection state.

By Default, KeepAliveTime TCP Parameter inside Windows (2000, XP, ...) is  set inside the registry to 7200 seconds
(HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\KeepAliveTime)

When a network problem happens during Mysql API (Mysql_real_query(), mysql_store_result() ), server aborts connection, but client hangs during the default TCP KeepAliveTime parameter set inside the Registry value "KeepAliveTime".

Many others professional applications can set a "KeepAliveTime" parameter, so MySql could implement it easily.

===================================================
How to repeat the pb :
- From the client, i choose a query which returns all rows (2000) of a table composed of 8 columns 
- MySql_Real_Connect() 
- Mysql_Real_Query() 
- Launch API mysql_store_result(), few seconds after and before the end of this API : unplug ethernet cable of the client, wait for 1mn (simulate the deconnection), and replug cable 
=> MySql Server logged the error as "Aborted Connection [...] Got an error reading communication packets" 
=> The Client will hang during the famous timeout (more than 7200 sec.) 

The same problem happens with newest client 4.1.20, and V5.0.22
Client & Server : Win2000 Service Pack 4