C3P0之连接失效

最近使用C3P0时，稳定一段时间后，会出现从连接池获取的connection不可用的错误，错误栈如下

java.sql.SQLException: Could not retrieve transaction read-only status from server
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:878)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:874)
        at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3556)
        at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3524)
        at com.mysql.jdbc.StatementImpl.executeBatchInternal(StatementImpl.java:977)
        at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:970)
        at com.mchange.v2.c3p0.impl.NewProxyStatement.executeBatch(NewProxyStatement.java:743)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 6,503,493 milliseconds ago.  The last packet sent successfully to the server was 1 milliseconds ago.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)

Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3014)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3472)
        ... 16 common frames omitted

栈顶的信息是Could not retrieve transaction read-only status from server，实际上root cause是java.io.EOFException，数据库连接已经闲置了10分钟。
进一步读代码，发现getConnection()时，C3P0默认是不检查连接状态的。除非设置了testConnectionOnCheckout，每次获取连接时，都检查一下连接的状态。经如下修改，上面的问题就不会复现

ComboPooledDataSource ds = new ComboPooledDataSource();
ds.setTestConnectionOnCheckout(true);

关于testConnectionOnCheckout源码的相关部分在C3P0PooledConnectionPool.refurbishResourceOnCheckout()方法中：

if (testConnectionOnCheckout) {
            if (C3P0PooledConnectionPool.logger.isLoggable(MLevel.FINER)) {
              this.finerLoggingTestPooledConnection(resc, "CHECKOUT");
            } else {
              this.testPooledConnection(resc);
            }
}

那C3P0是如何检查连接状态的呢？是在DefaultConnectionTester.activeCheckConnectionNoQuery()中向数据库发送一个getTables请求，部分代码如下

ResultSet rs = null;
try { 
  rs = c.getMetaData().getTables( null, null, 
                            "PROBABLYNOT", 
                            new String[] {"TABLE"} );
  return CONNECTION_IS_OKAY;
}

但如果每次获取Connection都要testConnection显然是低效的。
C3P0提供了另一种方式，就是在connection check in时检查连接是否有效，并且之后定期地检查链接是否有效。只要通过设置testConnectionOnCheckin为true，设置idleConnectionTestPeriod为20秒即可。