mysqlpp connect method timeout - how can I control it?

Question

I'm trying to control the connect method timeout, but I didn't find the appropriate mean.

Just to be clear, I'm not talking about the Idle connection timeout(ConnectTimeoutOption).

The scenario I need to deal with is a database gone away, and my server has to cope with that. My current handling of things is that I'm pinging the server, and If I notice that the ping failed, I'm suspending the queries for 100 seconds. After that I'm trying to reestablish the connection. The problem is that if the database is still dead, than the connect method takes about 20 seconds to answer (can be simulated by just pulling the network cable), which is way too much for me.

Answer 1

This should work for you

#include <mysql++.h>
#include <cstdio>

int main()
{
   mysqlpp::Connection conn;
   conn.set_option(new mysqlpp::ReconnectOption(true));
   conn.set_option(new mysqlpp::ConnectTimeoutOption(5));

   const std::string db="mysql_cpp_data";
   const std::string query_text="SELECT count(*) as total FROM stock";
   conn.connect(db.c_str(), "somehost", "user", "pass");

   try
   {
      mysqlpp::Query query=conn.query();
      query << query_text;
      mysqlpp::StoreQueryResult res=query.store();
      std::cout << "Has " << (*res.begin())[0] << " rows\n";
   }
   catch(const mysqlpp::BadQuery &e)
   {
      std::cout << "EXCEPTION: " << e.what() << std::endl;
   }
   std::cout << "Make database go away now and press a key\n";
   getchar();

   try
   {
      mysqlpp::Query query=conn.query();
      query << query_text;
      mysqlpp::StoreQueryResult res=query.store();
      std::cout << "Has " << (*res.begin())[0] << " rows\n";
   }
   catch(const mysqlpp::BadQuery &e)
   {
      std::cout << "EXCEPTION: " << e.what() << std::endl;
      std::cout << "Make database come back now and press a key\n";
      getchar();
      while(!conn.ping())
      {
         sleep(1);
         std::cout << "Waiting for DB to come back\n";
      }
      if(!conn.select_db(db))
      {
         std::cout << "Failed to change DB\n";
      }
   }

   try
   {
      mysqlpp::Query query=conn.query();
      query=conn.query();
      query << query_text;
      mysqlpp::StoreQueryResult res=query.store();
      std::cout << "Has " << (*res.begin())[0] << " rows\n";
   }
   catch(const mysqlpp::BadQuery &e)
   {
      std::cout << "EXCEPTION: " << e.what() << " " << e.errnum() << std::endl;
   }

   return 0;
}

Answer 2

try this out.

    #include <iostream>
    #include <iomanip>
    #include <cstdio>
    #include <cmdline.h>
    #include <mysql++.h>

    #define DBS "library"
    #define USR "root"
    #define PAS "rootsman"
    using namespace std;
    using namespace mysqlpp;

    int main(int argc, char *argv[]) {
        //for true cgi but in this case it works, kind of baffling!
        mysqlpp::examples::CommandLine cmdline(argc, argv, USR, PAS);
        if (!cmdline) return 1;

        mysqlpp::Connection conn(true);
        conn.set_option(new mysqlpp::ReconnectOption(true));
        conn.set_option(new mysqlpp::ConnectTimeoutOption(5));
        conn.connect(DBS, cmdline.server(), cmdline.user(), cmdline.pass());

        try {
            mysqlpp::String sql("select firstname from person");
            mysqlpp::Query query = conn.query(sql);
            mysqlpp::StoreQueryResult res = query.store();

            mysqlpp::StoreQueryResult::const_iterator it;
            int count = 1;
            for (it = res.begin(); it != res.end(); ++it) {
                mysqlpp::Row row = *it;
                cout << count << "\t" << row[0] << endl;
                ++count;
            }
        } catch (const mysqlpp::BadQuery& bq) {
            cerr << "query error: " << bq.what() << endl;
            return -1;
        }
        cout << "\nmake database fly away now by pressing a key>" << endl;
        getchar();

        try {
            mysqlpp::Query query = conn.query();
            mysqlpp::String sql("select count(*) as total from person");
            query << sql;
            mysqlpp::StoreQueryResult res = query.store();
            cout << "has " << (*res.begin())[0] << " rows" << endl;

        } catch (mysqlpp::BadQuery& e) {
            cerr << "\n bad query 2>" << e.what() << endl;
            cout << "\nmake database fly back now by pressing enter>" << endl;

            while (!conn.ping()) {
                //sleep(1);
                cout << "\nwaiting for database to fly back>" << endl;
            }
            if (!conn.select_db(DBS)) {
                cerr << "\nfailed to reconnect" << endl;
            }
        }

        try {
            mysqlpp::Query query = conn.query();
            //this is how my relation and its attributes looks like
            String sql("insert into person(firstname,lastname,gender,love,angry,"
            "forgiving) values('joy57/qxx','crimson','male','high','medium','high');");
            query << sql;
            query.execute();
            cerr << "\n **inserted successfully**\n" << endl;
        } catch (mysqlpp::BadQuery& e) {
            cerr << "bad query 3>" << e.what() << e.errnum() << endl;
            return -1;
        }

        cerr << "Jesus helps me when i stumble and fall" << endl;
        return 0;
    }