MySQL server has gone away Error while Selecting rows from a table

Question

Using a C program with Mysql C API connected to a database and the initiation was successful but tried to do SELECT ,INSERT operations it throws an Error " MySQL server has gone away" on my VPS Server but the same works fine locally.

The VPS Server Mysql Server version is : 5.0.81-community MySQL Community Edition (GPL) . wait_timeout value: 28800

Local Machine Mysql Server version is : 5.0.77 Source distribution.

But accessing the same database using Php it works fine without any problem (locally and vps server) only happens while calling using the Mysql C API.

int fetch_information_from_tbl()
{
        MYSQL_RES *result;
        MYSQL_ROW row;
        char query_def1[100];
        unsigned int num_fields;
        int tblid;

       /***EDITED FOR MORE INFORMATION START ***/
       // Tried to ping and set reconnection still the same error
       /* try a ping */
       if (mysql_ping(mysql)) {
           fprintf(stderr, "Cannot ping database: Error: %s\n",mysql_error(mysql));
            }
        my_bool reconnect = 0;
       if(mysql_options(mysql, MYSQL_OPT_RECONNECT, &reconnect)){
           fprintf(stderr, "Mysql Option Error: %s\n",mysql_error(mysql));
        }
       /***EDITED FOR MORE INFORMATION  ENDS***/

        sprintf(query_def1, "SELECT tblid FROM test_table");
        if (mysql_query(mysql, query_def1)) {
        fprintf(stderr, "%s\n", mysql_error(mysql));
        exit(1);
        }

        result = mysql_store_result(mysql);
        num_fields = mysql_num_fields(result);
        row = mysql_fetch_row(result);
        if (row) 
        {
                tblid = atoi(row[0]);
                printf("Fetched Table ID is: %d\n",tblid)
        }

}

Is it a right way to execute a mysql query like this or suggest any better way to do this? And what would be the case for this error which arises while doing SELECT OPERATION ? Thanks in advance.

Answer 1

You need to use mysql_ping to check whether your connection is still on. Please check mysql idle time out property of your mysql server. If there is no activity between the client and the server for that particular period, the server closes the connection. You can either change that or use mysql_ping to revive your connection.

In case of php, I guess it connects to the server for every query and disconnects the same.