Automatic recovery of DB outtages [EXPERIMENTAL]

[wwadge]

Hi,

I've just uploaded an *experimental* release of BoneCP that adds a replay feature.

This means that the connection pool will save all method calls from begin transaction till commit/rollback/close and if the database or network goes down for any reason (or a Lock Wait timeout / deadlock is detected), a new connection is fetched and the whole transaction played back again automatically without the application caring about it.

If you want to try it out:
Source: http://github.com/wwadge/bonecp/tree/replayLog
Binary: http://jolbox.com/bonecp/downloads/maven/com/jolbox/bonecp/0.6.5-replaylog-SNAPSHOT/

New config options:
config.setTransactionRecoveryEnabled(...) must be set to true to enable the feature
config.setAcquireRetryAttempts(...) number of times to keep trying to connect in case of a failure (default 5)
config.setAcquireRetryDelay(...) number of milliseconds to wait between each attempt (default 7 sec)


Basically:
1. Grab the code
2. Switch on transactionRecoveryEnabled
3. Play around as usual while killing off and restarting database connections or the entire database.

I would appreciate some feedback on how it goes in your environment

Regards,
Wallace

[aruld]

Hi Wallace,

This is an incredible feature for a connection pool. I tested this with MySQL and it worked like a champ.

I had one question regarding how this feature would work with a database shutdown hook. I had registered a connection hook and implemented onConnectionException callback. But, it retried for the first time and failed to replay the transaction as the hook also triggered at the same time.

Not sure both of these are supported. Just wanted to check it with you.

Keep up the great work...

Cheers,
Arul

--
Blog: http://aruld.info
Follow: http://twitter.com/aruld
Network: http://www.linkedin.com/in/aruld

[wwadge]

Both should be supported -- if not there's a bug somewhere.

Can you give me an indication of what you included in the connection hook?

[wwadge]

BTW is with version 0.6.6-rc1?

[aruld]

Yes it is with version 0.6.6-rc1.

Here is my test code:

Code: Select all

import com.jolbox.bonecp.BoneCP;
import com.jolbox.bonecp.BoneCPConfig;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class PoolTest {
    public static void main(String[] args) throws Exception {
        BoneCPConfig config = new BoneCPConfig();    // create a new configuration object
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mysql");    // set the JDBC url
        config.setUsername("root");            // set the username
        config.setPassword("secret");                // set the password
        config.setMaxConnectionsPerPartition(10);
        config.setMinConnectionsPerPartition(5);
        config.setConnectionHook(new DatabaseShutdownHook());
        config.setPoolName("TestPool");
        config.setLogStatementsEnabled(true);
        config.setTransactionRecoveryEnabled(true);
//        config.setAcquireRetryAttempts();//default 5
//        config.setAcquireRetryDelay();// default 7secs

        BoneCP connectionPool = new BoneCP(config);     // setup the connection pool
        Connection connection;
        connection = connectionPool.getConnection();     // fetch a connection

        PreparedStatement stmt = connection.prepareStatement("select User from user");
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            String name = rs.getString("User");
            System.out.println("User Name : " + name);
        }

        Thread.sleep(15000);

        stmt = connection.prepareStatement("select User from user");
        rs = stmt.executeQuery();
        while (rs.next()) {
            String name = rs.getString("User");
            System.out.println("User Name : " + name);
        }

        connection.close();                // close the connection
        connectionPool.shutdown();            // close the connection pool
    }
}


Code: Select all

import com.jolbox.bonecp.ConnectionHandle;
import com.jolbox.bonecp.hooks.AbstractConnectionHook;

public class DatabaseShutdownHook extends AbstractConnectionHook {
    @Override
    public boolean onConnectionException(ConnectionHandle connection, String state, Throwable t) {       
        System.out.println("Database down..");
        return super.onConnectionException(connection, state, t);
    }
}


[wwadge]

Does it only occur when you have the connection hook in place?

[aruld]

Correct. If I comment out this line, it works fine.
config.setConnectionHook(new DatabaseShutdownHook());

-Arul

[wwadge]

Ok, that helps a lot. One final question (sorry!) to help me track it down: are any log messages displayed upon retrying and upon not retrying the second time? Does it work if you return false in your hook instead?
Cheers,
Wallace

[aruld]

Hi Wallace,

If I return false, I get the below error:

Code: Select all

User Name : loandb
User Name : root
User Name : loandb
User Name : root
Database down..
Database down..
Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 19,725 milliseconds ago.  The last packet sent successfully to the server was 47 milliseconds ago.
   at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
   at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
   at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
   at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
   at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
   at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1122)
   at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3056)
   at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2942)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3485)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1960)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114)
   at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2696)
   at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2105)
   at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2264)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at com.jolbox.bonecp.MemorizeTransactionProxy.runWithPossibleProxySwap(MemorizeTransactionProxy.java:237)
   at com.jolbox.bonecp.MemorizeTransactionProxy.invoke(MemorizeTransactionProxy.java:172)
   at $Proxy5.executeQuery(Unknown Source)
   at com.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:161)
   at bonecp.samples.PoolTest.main(PoolTest.java:39)


If I return true, then I get the following exception (atleast it tries to recover once and then fails after hook execution):

Code: Select all

User Name : loandb
User Name : root
User Name : loandb
User Name : root
Database down..
Jun 11, 2010 2:08:33 PM com.jolbox.bonecp.MemorizeTransactionProxy invoke
SEVERE: Connection failed. Attempting to recover transaction on Thread #1
Database down..
Database down..
Exception in thread "main" java.sql.SQLException: Could not recover transaction. Original exception follows.com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 19,286 milliseconds ago.  The last packet sent successfully to the server was 12 milliseconds ago.
   at com.jolbox.bonecp.MemorizeTransactionProxy.invoke(MemorizeTransactionProxy.java:203)
   at $Proxy5.executeQuery(Unknown Source)
   at com.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:161)
   at bonecp.samples.PoolTest.main(PoolTest.java:39)

If I comment out the hook, then it works. Here is the output:

Code: Select all

User Name : loandb
User Name : root
User Name : loandb
User Name : root
Jun 11, 2010 2:11:05 PM com.jolbox.bonecp.MemorizeTransactionProxy invoke
SEVERE: Connection failed. Attempting to recover transaction on Thread #1
Jun 11, 2010 2:11:07 PM com.jolbox.bonecp.ConnectionHandle obtainInternalConnection
SEVERE: Failed to acquire connection. Sleeping for 7000ms. Attempts left: 5
Jun 11, 2010 2:11:14 PM com.jolbox.bonecp.ConnectionHandle obtainInternalConnection
INFO: Successfully re-established connection to DB
Jun 11, 2010 2:11:14 PM com.jolbox.bonecp.MemorizeTransactionProxy invoke
SEVERE: Recovery succeeded on Thread #1
User Name : loandb
User Name : root
User Name : loandb
User Name : root
Jun 11, 2010 2:11:18 PM com.jolbox.bonecp.BoneCP shutdown
INFO: Shutting down connection pool...
Jun 11, 2010 2:11:18 PM com.jolbox.bonecp.BoneCP shutdown
INFO: Connection pool has been shutdown.


HTH,
Arul

[aruld]

Ok, I did some debugging and figured out the retry won't be attempted when you set a hook, unless you return true from onAcquireFail(). Line # 319 in MemorizeTransactionProxy has this logic. By default, onAcquireFail() returns false causing this to fail. I fixed my hook and it works now. Not sure if this is the right way to fix this problem. Here is my updated hook code:

Code: Select all

public class DatabaseShutdownHook extends AbstractConnectionHook {
    @Override
    public boolean onConnectionException(ConnectionHandle connection, String state, Throwable t) {       
        System.out.println("Database down..");
        return super.onConnectionException(connection, state, t);
    }

    @Override
    public boolean onAcquireFail(Throwable e) {
        return true;
    }
}