MySQL Bugs: #32461: mysql_real_connect() hangs on connect attempt to absent server on Windows Vista

Bug #32461	mysql_real_connect() hangs on connect attempt to absent server on Windows Vista
Submitted:	16 Nov 2007 20:57	Modified:	9 Dec 2007 16:18
Reporter:	Annabel Lennox	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S1 (Critical)
Version:	5.0.37	OS:	Windows (Vista)
Assigned to:		CPU Architecture:	Any

Description:
When calling mysql_real_connect() from our client program, if TCP/IP is used to connect to the server, the connect attempt will hang if the server is not running or not accepting connections (e.g. if it is still starting up).   Client and server are both running on the same machine on Windows Vista.  Problem does not reproduce on other Windows versions.  Problem also does not reproduce if using named pipes instead of TCP/IP.

How to repeat:
1. Do not start a server- connection is OK if the server is running or has finished starting up.  The problem is the hang with a failure to connect.

2. In the client program, call mysql_real_connect() with these parameters:

mysql_real_connect(MYSQL *mysql, NULL, const char *user, const char *passwd, NULL, unsigned int port_num, NULL, 0);

where the variables are filled with valid login parameters for the TCP/IP connection if a mysqld.exe server was listening on port_num.  

3. The client program will hang from a few minutes to indefinitely.

Thank you for the bug report. Could you please provide a code test case
I wasn't able to repeat on Windows Vista Ultimate the below test code:

Microsoft Windows [versão 6.0.6000]
Copyright (c) 2006 Microsoft Corporation. Todos os direitos reservados.

c:\dev>c:\temp\bug32461
Error: Can't connect to MySQL server on 'localhost' (10061)

c:\dev>

#include <my_global.h>
#include <m_string.h>
#include "mysql.h"

#define DB_HOST			"localhost"
#define DB_USER			"root"
#define DB_PASSW		""
#define DB_NAME			"test"
#define DB_PORT			3306
#define DB_UNIX_SOCKET	NULL

void main( void )
{
  MYSQL mysql;
  
  mysql_init(&mysql);
  
   
  if (!mysql_real_connect(&mysql,NULL,DB_USER,DB_PASSW,NULL,DB_PORT,NULL,0))
  {
    printf("Error: %s\n",mysql_error(&mysql));
    return;
  }
  else
	printf("Connected to the server: %s\n",mysql_get_server_info(&mysql));

  mysql_close(&mysql);
}

Thanks in advance.

I was able to reproduce the problem using your test code sample from the previous comment.  The call to mysql_real_connect() does not return, but instead hangs.  We are using Windows Vista Business, rather than Ultimate.  Is Ultimate necessary for mysql?  Also, I am building with Visual Studio 2005 (SP1).

Thank you for the feedback. I don't think the Vista version does difference,
anyway I will ask to my co-worker to test on her Windows Vista too.

I also ran the test adding a call to time() before and after the call to mysql_real_connect(), printf'ing the timestamp.  The call to mysql_real_connect took 30 minutes to return.

I have some more information that might help.  I've been trying to code a workaround for this, and the following may help in looking at why the function doesn't return right away with a failure to connect.  Here is my workaround:

Instead of first calling mysql_real_connect() to test whether the db server 
is running and can be connected to, do this sequence:

1. Open a Windows socket.
2. Set it to nonblocking.
3. Call connect() on the socket; it will return with WSAEWOULDBLOCK.
4. Call select() on the socket for readability with a 5 second timeout.
5. If the select() returns without an error or timeout, then call 
mysql_real_connect().

The code is as follows.  It is called each time I need to test whether the server is running, before I connect.  This is done the first time checking to see if we need to start the server, and also in a timed loop after we have started the server until the server accepts connections.

/***Start code sample***/

SOCKET sc; //Socket for testing
SOCKADDR_IN sca;
int i=1;
struct timeval tv;
fd_set read_fd;
tv.tv_sec = 5;  //Five second timeout
tv.tv_usec = 0;	

sc = socket(AF_INET, SOCK_STREAM, IPPROTO_TCP);
if (sc == INVALID_SOCKET) {
  printf("Error creating socket for testing db server port: %d",  
      WSAGetLastError());
  return 0;
}
if(ioctlsocket(sc, FIONBIO, (u_long FAR*)&i) != 0) { //set nonblocking
  printf("Could not set db server test socket nonblocking");			
  closesocket(sc);
  return 0;
}
sca.sin_family = AF_INET;
sca.sin_addr.s_addr = ntohl(0x7F000001);	/* 127.0.0.1 in hex */
sca.sin_port = htons((uint16)3306);
printf("Calling nonblocking connect() for db server port test");
if (connect(sc, (LPSOCKADDR)&sca, sizeof(struct sockaddr)) == SOCKET_ERROR) {
  if(WSAGetLastError() != WSAEWOULDBLOCK) {
    printf("Could not connect to db server port; error");			
    closesocket(sc);
    return 0;
  }
}

//now call select() with timeout
printf("Testing db server port select()");
memset(&read_fd, 0, sizeof(fd_set));
FD_SET(sc, &read_fd);
i = select(sc, &read_fd, NULL, NULL, &tv);
if(i <= 0) {
  //A timeout in this case means server is not running or responding.
  printf("db server port select() returned %s", i ? "error" : "timeout");		
  closesocket(sc);
  return 0;
}
closesocket(sc);
//After this point, call mysql_real_connect() because we got a response...

/***End of code sample***/

The most important part is setting the socket to nonblocking, otherwise the 
connect() can hang intermittently, from a few minutes to indefinitely, if the server is not available.  I am wondering if this is also happening within mysql_real_connect().

Thank you for the report.

I can not repeat described behavior with version 5.0.46 also. So I close the report as "Can't repeat"

Please!!! Do not close this issue:
I am experiencing the same behaviour in Linux!:
here is my code samples:
short dbms data  :
namespace db
{

 struct arDBData{
     std::string name, host, user, pass, db;
 };

 arDBData* db_data;

 static std::map<std::string, arDBData*> arDBDictionary;

 void ArknSQL::InitDBMS(){
    std::cerr << __PRETTY_FUNCTION__ << std::endl;
     arDBData* dt;
     dt = new arDBData;
     dt->name = "arCMS";
     dt->host = "localhost"; //"172.16.3.30 hangs!
     dt->user = "arCMS";
     dt->pass = {the_password}
     dt->db = "arCMS";
     arDBDictionary["arCMS"] = dt;

     dt = new arDBData;
     dt->name = "arInv";
     dt->host = "172.15.3.30"; // not used yet
     dt->user = "arInv";
     dt->pass = {the password}
     dt->db = "arInv";
    
     arDBDictionary["arInv"] = dt;
 }
 
// ------------- continue ------------
config code:
[...]

db_data = arDBDictionary[_dbmsName];
    _connected = false;
    if(! mysql_init(&_my) ){
        (*this) << new SQLError(&_my, "db::Init");
        return 0;
    }
[..]

connection code:
[...]
std::cerr << __PRETTY_FUNCTION__ << "Connecting with params:" << std::endl;
    std::cerr << "host:" << db_data->host << " user: " << db_data->user << std::endl;
    if ( !mysql_real_connect ( &_my, db_data->host.c_str(), db_data->user.c_str(), db_data->pass.c_str(), db_data->db.c_str(),0,0l,0 ) )
    {
        std::cerr << "failed connexion..." << std::endl;
        ( *this ) << new SQLError ( &_my, "Connecting to MySQL" );
        QueryError_( LastError() );
        return 0;
    }
    std::cerr << "successfull connexion" << std::endl;
    _connected = true;
    return 1;

If I  use ip address instead of "localhost" it hangs ( nothing to do with OS though ).

And I tried to connect from cndline and the connection is working - so it has todo with libmysqlclient it self ??
------------------------------------------------------------------

server 
mysqld  Ver 5.1.37-1ubuntu5 for debian-linux-gnu on i486 ((Ubuntu))
my.cnf::BindAddress = "172.16.3.30" -- ok

Thank you and sorry for my poor english and code...

Serge Lussier,

thank you for the feedback.

Could you please try with current version 5.1.43 available from http://dev.mysql.com/downloads?

The workaround way is to set timeout for mysql before mysql_real_connection(), which is tested based on mysql-5.1.47 on Linux:

unsigned int time_out=1; 
mysql_options(mysql_db,MYSQL_OPT_CONNECT_TIMEOUT,(char*)&time_out); 

With IP unaccessible/accessible loops, the mysql_real_connection never block on _recv for very long time anymore, while connection would be returned from server side with-in 1sec if server is down.

From some sense, it's still an open bug of engine side. It still could be repeated very high frequency tested by dbt2-0.4 as client for mysql-5.1.47.

FYI the bug is still there in Debian Lenny:
 ii  libmysqlclient15-dev                 5.0.51a-24+lenny5          MySQL database development files

I'm fetching unstable branch now, tried all options both for READ and CONNECT timeout and also tried setting mysql.options directly.

I tried this on Fedora Linux and getting the same problem.
But by setting timeout option using mysql_options(&mysql,MYSQL_OPT_CONNECT_TIMEOUT,(char*)&time_out)
after mysql_init(&mysql) and before mysql_real_connect(&mysql,NULL,DB_USER,DB_PASSW,NULL,DB_PORT,NULL,0)
I am bale get a timeout error.