MySQL Bugs: #65658: Client hangs if connection to remote server is dropped

Bug #65658	Client hangs if connection to remote server is dropped
Submitted:	18 Jun 2012 14:52	Modified:	4 Mar 2013 14:02
Reporter:	hello okay	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S2 (Serious)
Version:	5.0.45, 5.5.26	OS:	Linux (OS: RHEL)
Assigned to:		CPU Architecture:	Any

Description:
Same to Bug #5449, but it still exist in version 5.0.45.

Can you tell me how to solve this problem and whether the bug had been fixed, thanks!

When database is open, libmysql does not handle the lost of physical connection properly.

Functions (examples: mysql_query(), mysql_ping()) will hang and won't return any result until connection is restored.

Different timeouts do not help to resolve the problem.

How to repeat:
1. Connect client to the server.
2. Select database.
3. Disconnect the server from the LAN by disconnecting cable or issuing command 'ifdown'.
4. Functions mysql_ping(), mysql_query() hang
5. Connect the server back
6. Functions mysql_ping(), mysql_query() returns

Please, check if the problem is still repeatable with recent versions, 5.0.91+ (even better, with actively supported versions, 5.1.63 or 5.5.25). Send also complete C code to demonstrate the problem.

My question is why the efficiency is very low, because this problem had been posed with Bug #5449(which occur in 2006).

Now i attach with my test code.

First compile and run code.

during call Sleep(20), now i disconnect cable, after 20 seconds pass, the code will hang at mysql_ping will hang, if i connect the server back, the code will execute again. 

#include "mysql.h"
#include <stdio.h>

MYSQL DbObj;//handle
MYSQL_RES *pRes;//results
MYSQL_ROW sqlrow;//rows

int main()
{
    char tmp[255];
    mysql_init(&DbObj);

    if (!mysql_real_connect(&DbObj, "127.0.0.1", "root", "", "mysql", 0, NULL, 0)) 
    {
	return 0;
    }
       
    sleep(20);
    
    if (mysql_ping(&DbObj) != 0)
    {
        printf("lost connection\r\n");
        return -1;	
    }

    sprintf(tmp, "select * from user");
    int res = mysql_query(&DbObj, tmp);
    pRes = mysql_use_result(&DbObj); 
    
    if ((sqlrow = mysql_fetch_row(pRes)) == NULL)
    {
    	printf("Failed.\n");
    }
    else
    {
        mysql_free_result(pRes);
	printf("OK.\n");
    }

    mysql_close(&DbObj);
    	
    return 0;
}

Thank you for the feedback.

Verified as described.

Looks similar, although not identical to Bug #62070. How-to repeat instructions in Bug #62070 as well, just change php -r to program provided by reporter. When connection re-establishes, program continues to work (in contrast with Bug #62070 which still hangs).

After analyzing/reproducing this bug I have found that client is not completely hung even under Linux. In my tests on my Debian VM mysql_ping() returns after ~930 seconds of waiting. After finding this fact I asked a question: what is default read timeout? There is MYSQL_OPT_READ_TIMEOUT option described at http://dev.mysql.com/doc/refman/5.6/en/mysql-options.html. But description does not directly say about default value. Instead there is following phrase:

You can set the value so that a lost connection can be detected earlier than the TCP/IP Close_Wait_Timeout value of 10 minutes.

Thus if MYSQL_OPT_READ_TIMEOUT is not set then read timeout is "TCP/IP Close_Wait_Timeout value of 10 minutes". Actually on my Debian VM this value is about  15.5 minutes and on Windows 7 this value is about 18.5 seconds.

Further investigation showed that mysql_init(&mysql) initializes mysql->options.read_timeout with 0 which means "infinite" wait. One could say that if MYSQL_OPT_READ_TIMEOUT is not set then the timeout is determined by OS (the socket layer).

Thus to solve this "hung" problem it is enough to add mysql_options(&DbObj,MYSQL_OPT_READ_TIMEOUT,&opt_read_timeout) between mysql_init() and mysql_real_connect() calls.

Closing as 'not a bug' because there are no hung at all. What looks as hung is actually a 10 or 15 minutes long default timeout.