Bug #1121 mysql_ping() *ducks*
Submitted: 22 Aug 2003 9:40 Modified: 27 Aug 2003 19:39
Reporter: Antoniu-George SAVU Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:3.23.56 OS:Linux (Linux, kernel 2.4.21)
Assigned to: CPU Architecture:Any

[22 Aug 2003 9:40] Antoniu-George SAVU
Description:
   If mysql_ping() is used in a thread while the mySQL server is no longer available, the whole process will core due to the fact that the thread execution is *limited*.

   See the example file mysql_ping.c, with explanations how to repeat. I've done the same test with the latest version of mySQL, 4.0-stable, 4.1-alpha, 5.0-rc .

How to repeat:
---8<--- Cut here ---
/* file: mysql_ping.c */

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

#define MYSQL_HOST      "127.0.0.1"
#define MYSQL_PORT      3306
#define MYSQL_USER      "test"
#define MYSQL_PASS      "test"
#define MYSQL_DB        "test"

int main(void) {
  long            rc;
  MYSQL           db;

  unsigned char mySQLConnected;
  unsigned int counter;

  /* Init & initial connect */
  (MYSQL*)rc=mysql_init(&db);
  (MYSQL*)rc=mysql_real_connect(&db,MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB, MYSQL_PORT, NULL, 0);
  if (!rc) {
    fprintf(stderr, "Fatal (mySQL) - cannot connect to mySQL server [%s] with user [%s] and password [%s] to access database [%s]\n",
      	    MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB);
            return 0;
  }

  if ( mysql_select_db(&db, MYSQL_DB) != 0) {
    fprintf(stderr, "Fatal (mySQL) - cannot use database [%s]", MYSQL_DB);
    return 0;
  }

  counter = 0;
  mySQLConnected = 1;

  while (1) {
    if (mysql_ping(&db) !=0) {
      if (!mySQLConnected) {
        fprintf(stderr, "%d mySQL server still disconnected.\n", counter);
      } else {
        fprintf(stderr, "%d mySQL server went away.\n", counter);
        mySQLConnected = 0;
      }
    } else {
      if (!mySQLConnected) {
        fprintf(stderr, "%d mySQL reconnected.\n", counter);
        mySQLConnected = 1;
      } else {
        fprintf(stderr, "%d mySQL connected.\n", counter);
      }
    }
    sleep(1);
    counter++;
  }

  /* close connection */
  mysql_close(&db);

  return 1;
}

---8<-----

Compile and link with:

$ gcc -O3 -Wall -lmysqlclient -o mysql_ping mysql_ping.c

   Run the binary and after a while, cut the connection to the mySQL server even by shutting down the mySQL server, adding a OUTPUT/INPUT REJECT/DROP iptables entry, delete route to mySQL server, etc. As suggested in the official documentation, mysql_ping() should return an error if the mySQL server is no longer available but mysql_ping() actually locks the current process and does not return if it cannot rebuild the connection.

Suggested fix:
  The normal behaviour should be as described in the official documentation. If the server is no longer available, mysql_ping() should try to rebuild the connection and if it fails, should return an error. 

  There is an error code that should be nice to have it, something that inform the process if the connection to the server was not available and mysql_ping() rebuild it.
[22 Aug 2003 10:10] Antoniu-George SAVU
Err, I've made a small error. 

The mysql_ping() function will return if the mySQL is shutdown but it will lock if there is a network problem (firewall, routing, ...) . My appologies ;)
[27 Aug 2003 19:39] Boyd Gerber
As per your comment and that I could not duplicate the problem I am closing this bug.  Server shutdown did not produce error.