G-WAN and persistent MySQL connexion

Question

me again with a little question about G-WAN and MySQL.

This script below works fine ... My only problem is when MySQL went down. G-WAN script crash and G-WAN as well.

What is the nest way to keep a persistent MySQL connexion and handle MySQL downtime?

typedef struct { 
   MYSQL *conn;
} data_t;

int main(int argc, char *argv[])
{
  u64 start = getus();
  data_t **data = (data_t**)get_env(argv, US_SERVER_DATA);
  xbuf_t *reply = get_reply(argv);

  if(!data[0]) // first time: persistent pointer is uninitialized
  {
    data[0] = (data_t*)calloc(1, sizeof(data_t));
    if(!data[0])
        return 500; // out of memory
    data[0]->conn = (MYSQL *)mysql_init(data[0]->conn);

    if(! data[0]->conn) {
      xbuf_xcat(reply, "MySQL Error");
      return(200);
    }

    if(! mysql_real_connect(data[0]->conn, "localhost", "root", "willow", "test", NULL, NULL, 0)) {
      return 500;
    }
    xbuf_cat(reply, "initialized data<br>");
  }

  // Do what we want here ...

score 1 · Answer 1 · answered Apr 29 '13 at 12:09

Here my working script :

#pragma link "/usr/lib64/mysql/libmysqlclient.so"
#pragma include "/usr/include/mysql"

#include <mysql.h>
#include <string.h>
#include "gwan.h" // G-WAN exported functions

//static MYSQL *conn = NULL;

typedef struct { 
   MYSQL *conn;
} data_t;

int main(int argc, char *argv[])
{
  u64 start = getus();
  data_t **data = (data_t**)get_env(argv, US_SERVER_DATA);
  xbuf_t *reply = get_reply(argv);
  my_bool  my_true = true;

  if(!data[0]) // first time: persistent pointer is uninitialized
  {
    data[0] = (data_t*)calloc(1, sizeof(data_t));
    if(!data[0])
        return 500; // out of memory
    data[0]->conn = (MYSQL *)mysql_init(data[0]->conn);
    mysql_options(data[0]->conn, MYSQL_OPT_RECONNECT, &my_true);
    if(! data[0]->conn) {
      xbuf_xcat(reply, "MySQL Error");
      return(200);
    }

    if(! mysql_real_connect(data[0]->conn, "localhost", "root", "willow", "test", 0, NULL, 0)) {
      return 500;
    }
    xbuf_cat(reply, "initialized data<br>");
  }

  if(mysql_ping(data[0]->conn) > 0) return 500; // Prevent G-WAN to crash

  mysql_query(data[0]->conn, "DELETE FROM example");

  for(int i =1; i< 10; i++) {
    char sql[1024];
    s_snprintf(sql, 1023, "INSERT INTO example (id, name, age) VALUES(%d, 'Olivier', 33)", i);
    mysql_query(data[0]->conn, sql);
  }

  int count = 0;
  // Query Database
  mysql_query(data[0]->conn, "SELECT id, name, age FROM example");
  MYSQL_RES *result = mysql_store_result(data[0]->conn);
  MYSQL_ROW *row;
  while ((row = mysql_fetch_row(result))) { 
    count++;
    xbuf_xcat(reply, ": %s : %s : %s", row[0], row[1], row[2]); 
    xbuf_xcat(reply, "<br/>");
  }
  mysql_free_result(result);

  xbuf_xcat(reply, "<br>%llUmicro seconds : %d<br/>", (getus()-start), mysql_field_count(data[0]->conn));
  xbuf_xcat(reply, "MySQL Client Version: %d", mysql_get_client_version());


  return 200;
}

So now even when mysql is down, G-WAN is still alive, and the script works again when mysql is UP. Hope it will help someone else.

score 0 · Answer 2 · answered Apr 29 '13 at 06:38

0

when MySQL went down. G-WAN script crash and G-WAN as well

Since you only publish the code that creates the persistent connection to MySQL (and not the code that uses MySQL later), there's no way to see how your code manages to crash G-WAN.

But it is most likely that this is the MySQL driver library that is crashing when you are trying to use an invalid socket.

The solution here is obviously to test the validity of the socket (its connected state) via something like:

ioctl(fd,FIONREAD,&bytes_available);

...before you pass it to the MySQL client library.

answered Apr 29 '13 at 06:38

Gil

3,279
1
15
25

Actually with or without additional code, it will simply fail... It don't belongs to another sql queries ... – solisoft Apr 29 '13 at 06:56
The MySQL connection is a mere fd variable in G-WAN who could not care less about its value - until it is used. So, if it is not used, as you assert, there is just NO WAY for G-WAN to be affected by a MySQL disconnect. – Gil May 03 '13 at 09:36

score 0 · Accepted Answer · answered Apr 29 '13 at 10:20

0

It be great if You do a bit of research before asking a question...

See -> http://dev.mysql.com/doc/refman/5.5/en/mysql-ping.html

answered Apr 29 '13 at 10:20

deepinit

66
3

G-WAN and persistent MySQL connexion

3 Answers3

Linked