From: Rick Gutleber
Date: December 19 2008 11:41pm
Subject: reconnecting to MySQL after a server reboot
List-Archive: http://lists.mysql.com/plusplus/8290
Message-Id: <494C3110.4000609@above.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Plus-plussers:

I don't know if this is really a MySQL++ question or a MySQL question.  
I believe there should be something I can do through MySQL++ to correct 
this, but I cannot find anything with Google. 

The architecture of our project is that we have a "master" server, which 
runs MySQL and a whole bunch of "poller" servers which gather data, and 
which also run MySQL.

The software on the master server periodically connects to MySQL on all 
the poller machines and aggregates the data out of their databases to 
its own master database where the data is used.  (There is redundancy 
among the pollers, but that is completely independent of this problem.)

However, there is another problem that's occurred a handful of times 
over the last year.  For some reason, unknown to me, something caused an 
unscheduled reboot on one of the pollers.  MySQL and our software 
restart just fine and everything is swell, EXCEPT:

The software running on the master server can no longer connect to the 
MySQL database on that poller.  Restarting the app fixes the problem, 
but that shouldn't be necessary.   However, I can't understand what 
would be different about establishing a connection after a reboot, and 
what restarting the app could be fixing.

I have the MySQL++ usage wrapped in a class called DBConnection.  Upon 
app start-up, I create a DBConnection object for database access.  
DBConnection provides methods for store( ), exec( ), and a number of 
higher-level application-specific queries that return SSQLS objects or 
collections of SSQLS objects and other things useful for the application. 

When a database operation is required, anything in DBConnection first 
calls verify connection which creates a mysqlpp::Connection if necessary 
and connects it to the database, if needed.  If something throws an 
exception, I set a flag to reconnect the next time.  Reconnecting means 
deleting the mysqlpp::Connection and creating a new one.    This works 
well and has been running in production for the better part of a year 
with almost no problems.

However, when the poller has been rebooted, reconnecting always fails 
with MySQL error 111 ("Connection refused") or 113 ("No route to 
host").  It won't work until I start the app over again.  It's like 
there's some kind of network connection that gets broken, but doesn't 
get fixed until the app is restarted (even though the database 
connections are being destroyed and recreated).

My question is this:  Apparently there is some state that remains even 
if I completely recreate the mysqlpp::Connection that is not preserved 
if the app is restarted, even though I believe the exact same thing 
happens (in terms of calls to mysqlpp) whether it's trying to reconnect 
or the app is starting up.

Does anyone know if there is something I can do to correctly 
"reinitialize" things so the app can reconnect without being restarted.

I included the verifyConnection( ) source code, although I doubt the 
problem lies here:

//**********************************************************************
//
//  DBConnection::verifyConnection
//
//  We can't issue event records because if there's an error, it means
//  we aren't connected.
//
//**********************************************************************

bool DBConnection::verifyConnection( ) {
    TimeVal now( TimeVal::Now );

    // if we have a connection, check to see if it's "stale"
    if ( con && ( now >= connectionExpires ) ) {
        logMDebug1( ) << "verifyConnection( ):  connection expired at " 
<< connectionExpires.getAsString( ) <<
                std::endl;

        delete con;
        con = NULL;
    }

    // check to see if a reconnect has been requested
    if ( reconnect ) {
        logMDebug1( ) << "verifyConnection( ):  reconnecting..." << 
std::endl;

        delete con;
con = NULL;
    }

    // finally, check the connection and if it's OK, we're cool
    if ( isConnected( ) ) {
        connectionExpires = now + getStaleConnectionThreshold( );

//          logMDebug1( ) << "verifyConnection( ):  setting connection 
expiry at " <<
//                  connectionExpires.getAsString( ) << std::endl;

        return true;
    }

    try {
        if ( !con ) {
            con = new mysqlpp::Connection( true );  // 'true' means 
throw exceptions on errors
        }

        logMDebug2( ) << "host:  " << getHostName( ) << ", name:  " << 
getDBName( ) << ", user:  " <<
                getUserName( ) << std::endl;

        bool rc = con->connect( getDBName( ).c_str( ), getHostName( 
).c_str( ), getUserName( ).c_str( ),
                                getPassword( ).c_str( ) );

        if ( !rc ) {
            logMError( ) << "verifyConnection( ):  con->connect( ) " <<
                    "failed but no exception was thrown! (host:  " << 
getHostName( ) << ")" << std::endl;

            return false;
        }

        if ( !con->connected( ) ) {
            logMError( ) << "verifyConnection( ):  con->connected( ) " <<
                    "failed but no exception was thrown! (host:  " << 
getHostName( ) << ")" << std::endl;

            return false;
        }

        connectionExpires = now + getStaleConnectionThreshold( );

//          logMDebug1( ) << "verifyConnection( ):  setting connection 
expiry at " <<
//                  connectionExpires.getAsString( ) << std::endl;

        logMDebug2( ) << "MySQL info:  " << con->ipc_info( ) << std::endl;
    } catch ( mysqlpp::BadQuery & er ) {
        error = er.what( );

        logMError( ) << "MySQL BadQuery Connection error:  '" << error 
<< "' (host:  " <<
                getHostName( ) << ")" << std::endl;

        reconnect = true;

        return false;
    } catch ( const mysqlpp::Exception & er ) {
        error = er.what( );

        logMError( ) << "MySQL Connection error:  '" << error << "' 
(host:  " <<
                getHostName( ) << ")" << std::endl;

        return false;
    }

    reconnect = false;

    return true;
}