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

Bug #5449	Client hangs if connection to remote server is dropped
Submitted:	7 Sep 2004 12:47	Modified:	21 Aug 2006 17:33
Reporter:	[ name withheld ]	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S1 (Critical)
Version:	3.23.58,4.1.5	OS:	Linux (Fedora Core)
Assigned to:		CPU Architecture:	Any

Description:
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

Suggested fix:
Make timeouts work?
Put read/write timeouts on sockets?

Hi,

I'm having problems repeating this as you can see here:

/usr/local/mysql-3.23.58-pc-linux-i686/bin/mysqladmin --port=3999 ping -h 192.168.1.21
/usr/local/mysql-3.23.58-pc-linux-i686/bin/mysqladmin: connect to server at '192.168.1.21' failed
error: 'Can't connect to MySQL server on '192.168.1.21' (113)'
Check that mysqld is running on 192.168.1.21 and that the port is 3999.
You can check this by doing 'telnet 192.168.1.21 3999'

Is there anything that I may be missing here?  I was unable to repeat it using a unix
domain socket as well.  When specifying a bad socket file it immediately returns.

Best Regards

The problem appears when you are already connected to the database and cable is being disconnected.

Here is an example of a program that connects to the server, opens the database and then it is going to query (mysql_query) or ping(mysql_ping). If there is a link, then it should output "+", otherwise "-".

Steps to repeat bug:
1. Change settings for your host, database, and table (if you are going to check mysql_query also)
2. Compile, Run
You should see characters "+" on the screen
3. Disconnect physical cable from network card (same affect with 'ifdown eth0')
You should see characters "-" on the screen, but this does not happen - BUG!
4. Connect physical cable back
You should see characters "+" on the screen again in a while (it may take more than one minute), since connection is back

#include <unistd.h>
#include <stdio.h>
#include <stdlib.h>
#include <mysql/mysql.h>

// put your host and database settings
#define HOST "192.168.1.121"
#define DATABASE "mydatabase"

#define VER_QUERY 1

MYSQL *mysql;
MYSQL_RES *res;

int uid;
char clause[1024];
char uname[5];
char to[5]="\0\0\0\0";

int main()
{
    printf("init\n");
    mysql = mysql_init(NULL);
    printf("connect\n");
    if (!(mysql_real_connect(mysql,HOST,"root","password",DATABASE,0,0,CLIENT_SSL)))
	exit(1);

    printf("while\n");
    while(1){
#ifdef VER_QUERY
	if (!mysql_query(mysql,"SELECT * FROM settings;")){
	    res = mysql_use_result(mysql);
	    mysql_free_result(res);
	    printf("+");
	}else {
	    printf("[%d %s]\n", mysql_errno(mysql), mysql_error(mysql));
	    printf("-");
	}
#else
// use ping only
	if(mysql_ping(mysql)) printf("-");
	else printf("+");
#endif
	fflush(stdout);
	usleep(100);
    }

    mysql_close(mysql);
}

I was able to verify this using the provided test app and disconnecting the ethernet cable on the 
server.

client machine:
Darwin silverbullet 7.5.0 Darwin Kernel Version 7.5.0: Thu Aug  5 19:26:16 PDT 2004; root:xnu/
xnu-517.7.21.obj~3/RELEASE_PPC  Power Macintosh powerpc

server machines:
Linux booty 2.4.21 #12 SMP Thu Aug 14 00:49:40 EDT 2003 i686 i686 i386 GNU/Linux
Windows 2000 SMP

The client machine was running 4.1.5-gamma, the Linux server was running 4.1.5 and the 
windows 2000 server was running 4.0.20.

verified too on linux redhat 7.3

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27637

see also the thread in internals that starts from http://lists.mysql.com/internals/26739
BUG#4143, BUG#11774

This bug is a duplicate of bug#9678.