java - 在使用c3p0连接池配置的Mysql主从复制中偶尔会遇到到slave的通信链路故障

Question

我使用 Mysql 复制驱动程序和 c3p0 连接池配置了一个主从复制。有时在从属中面临以下连接失败问题。在当前设置中，有一个主站和一个从站。

   org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: org.hibernate.TransactionException: JDBC begin transaction failed:
            at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:431)
            at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
            at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:427)
            at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:276)
            at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
            at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
            at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
            at com.sun.proxy.$Proxy264.get(Unknown Source)
    /* 
    getSomeDataFromSlave()
    */      java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
            at java.util.concurrent.FutureTask.run(FutureTask.java:262)
            at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
            at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
            at java.lang.Thread.run(Thread.java:745)
    Caused by: javax.persistence.PersistenceException: org.hibernate.TransactionException: JDBC begin transaction failed:
            at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1763)
            at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)
            at org.hibernate.jpa.spi.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:1771)
            at org.hibernate.jpa.internal.TransactionImpl.begin(TransactionImpl.java:64)
            at org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction(HibernateJpaDialect.java:170)
            at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:380)
            ... 16 more
    Caused by: org.hibernate.TransactionException: JDBC begin transaction failed:
            at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:76)
            at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:162)
            at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1435)
            at org.hibernate.jpa.internal.TransactionImpl.begin(TransactionImpl.java:61)
            ... 18 more
    Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failureThe last packet successfully received from the server was 5,804 milliseconds ago.  The last packet sent successfully to the server was 3,206 milliseconds ago.
            at sun.reflect.GeneratedConstructorAccessor897.newInstance(Unknown Source)
            at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
            at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
            at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
            at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:981)
            at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3465)
            at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3365)
            at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3805)
            at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
            at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
            at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2547)
            at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4874)
            at com.mysql.jdbc.MultiHostMySQLConnection.setAutoCommit(MultiHostMySQLConnection.java:2064)
            at sun.reflect.GeneratedMethodAccessor367.invoke(Unknown Source)
            at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
            at java.lang.reflect.Method.invoke(Method.java:606)
            at com.mysql.jdbc.LoadBalancedConnectionProxy.invokeMore(LoadBalancedConnectionProxy.java:484)
            at com.mysql.jdbc.MultiHostConnectionProxy.invoke(MultiHostConnectionProxy.java:452)
            at com.sun.proxy.$Proxy232.setAutoCommit(Unknown Source)
            at com.mysql.jdbc.MultiHostMySQLConnection.setAutoCommit(MultiHostMySQLConnection.java:2064)
            at sun.reflect.GeneratedMethodAccessor367.invoke(Unknown Source)
            at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
            at java.lang.reflect.Method.invoke(Method.java:606)
            at com.mysql.jdbc.ReplicationConnectionProxy.invokeMore(ReplicationConnectionProxy.java:293)
            at com.mysql.jdbc.MultiHostConnectionProxy.invoke(MultiHostConnectionProxy.java:452)
            at com.sun.proxy.$Proxy233.setAutoCommit(Unknown Source)
            at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:881)
            at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:72)
            ... 21 more
    Caused by: java.net.SocketException: Connection reset
            at java.net.SocketInputStream.read(SocketInputStream.java:196)
            at java.net.SocketInputStream.read(SocketInputStream.java:122)
            at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:100)
            at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:143)
            at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:173)
            at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2954)
            at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3375)
            ... 43 more

目前的配置如下:

**c3p0 properties:**

db.maxPoolSize=20
db.minPoolSize=10
db.maxConnectionIdleTimeInSec=300
db.idleConnectionTestPeriodInSec=300
db.testConnectionOnCheckin=true
db.testConnectionOnCheckout=true
db.connectionTestQuery=select 1

** DB config **

jdbc.driverClassName=com.mysql.jdbc.ReplicationDriver
jdbc.url=jdbc:mysql:replication://url1,url2/schema

**I have done some c3p0 finer logging following are some traces**
.....
FINER] MBean: com.mchange.v2.c3p0:type=PooledDataSource[z8kflt9j9jerlpms8xe0|8ac49e] registered.

2016-09-13 12:39:51 [localhost-startStop-1] INFO o.s.o.j.LocalContainerEntityManagerFactoryBean -

Building JPA container EntityManagerFactory for persistence unit 'default'

[FINEST] incremented pending_acquires: 1

[FINEST] incremented pending_acquires: 2

[FINEST] incremented pending_acquires: 3

[FINER] com.mchange.v2.resourcepool.BasicResourcePool@37ca3e27 config: [start -> 3; min -> 3; max -> 10; inc -> 3; num_acq_attempts -> 30; acq_attempt_delay -> 1000; check_idle_resources_delay -> 60000; mox_resource_age -> 0; max_idle_time -> 100000; excess_max_idle_time -> 0; destroy_unreturned_resc_time -> 10000; expiration_enforcement_delay -> 2500; break_on_acquisition_failure -> false; debug_store_checkout_exceptions -> true]

[INFO] Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> z8kflt9j9jerlpms8xe0|8ac49e, debugUnreturnedConnectionStackTraces -> true, description -> null, driverClass -> com.mysql.jdbc.ReplicationDriver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> z8kflt9j9jerlpms8xe0|8ac49e, idleConnectionTestPeriod -> 60, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql:replication://url1,url2/schema, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 100, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> select 1, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 10, usesTraditionalReflectiveProxies -> false ]

[FINER] acquire test -- pool size: 0; target_pool_size: 3; desired target? 1

[FINE] awaitAvailable(): [unknown]

[
....

我的假设是池中的从属连接从 Mysql 端关闭，但在池中它仍未更新且未标记为不活动。假设它是一个 Activity 的连接应用程序试图从奴隶获取并失败。知道这里可能有什么问题吗？连接池是否在使用之前不测试从属连接并定期刷新连接是否有问题？

为 connectionTest 尝试了自定义连接类，但没有成功。连接测试类如下所示

公共(public)类 QueryReplicationConnectionTester 扩展了 DefaultConnectionTester{

private static final long serialVersionUID = -3450145378350470297L;

/**
 * during testing we need to make sure, that not only master
 * but also the slave connection is used. Therefore we need to set
 * the connection to "readonly" to make sure, that the slave 
 * connection is used.
 * 
 * CAUTION: this will only work for ONE SLAVE ENVIRONMENT, since
 * this does not make sure all slaves are checked.
 */
@Override
public int activeCheckConnection(Connection connection, String arg1, Throwable[] arg2) {

    // Initially set to ok
    int status = CONNECTION_IS_OKAY;

    try {

        // remember state and 
        boolean autoCommit = connection.getAutoCommit();
        boolean readOnly = connection.isReadOnly();

        // switch to slave and check slave
        connection.setReadOnly(true);
        connection.setAutoCommit(false);
        status = super.activeCheckConnection(connection, arg1, arg2);

        // if slave is fine, lets check the master
        if ( status == CONNECTION_IS_OKAY ){
            connection.setReadOnly(false);
            connection.setAutoCommit(autoCommit);
            status = super.activeCheckConnection(connection, arg1, arg2);
        }

        connection.setAutoCommit(autoCommit);
        connection.setReadOnly(readOnly);

    } catch (SQLException e) {
        status = CONNECTION_IS_INVALID;
    }

    // return final state
    return status;
}

还检查了 Mysql 日志。我可以看到 preferredtestquery(select 1) 被触发到 master 但由于某种原因它没有被触发到 slave。

Answer 1

如果问题如您所想，那么一个简单的解决方法可能是设置 Connection property readFromMasterWhenNoSlaves。然后你可以使用 c3p0 的内置 DefaultConnectionTester，只要 master 可用，Connections 就会工作。如果 master 宕机，那么 Connection 测试将失败，客户端甚至无法从 slave 获取连接，直到 master 恢复正常。但是，除非您对 Connections 的所有使用都是只读的，否则这可能就是您想要的行为。如果 master 宕机并且 c3p0 释放连接到你的应用程序，它没有办法知道这些连接是否将用于只读目的，所以它应该考虑那些连接断开。在这种情况下，您可以通过复制获得一些负载分配，但如果主服务器出现故障，您将无法“故障回复”到从服务器。不过，您应该在从服务器宕机时故障回复到主服务器。

如果您的应用程序对连接的所有使用都是只读的，您可以编写 ConnectionCustomizer在其 onAcquire(...) 方法中调用 setReadOnly(true)。 c3p0 将跟踪 setReadOnly(...) 的覆盖，并确保客户端看到您设置的值，即使在 checkin / checkout 周期之后也是如此。然后，大概连接默认情况下是从站。如果您设置了 readFromMasterWhenNoSlaves，那么您的应用程序应该在从服务器不可用时正确地故障回复到主服务器。请注意，如果您的客户端对 Connections 的使用是完全只读的，则您的客户端永远不应设置 setReadOnly(false)。

但是，您的客户端更有可能不是普遍只读的，因此您应该使用普通的连接测试，而不是先设置只读，但要设置 readFromMasterWhenNoSlaves。当主服务器宕机时，连接将失效，这是应该的，但应该在从服务器出现问题时继续存在。

我不确定为什么您没有看到针对从设备的连接测试，但您可以尝试使用 setAutoCommit(true) 而不是 setAutoCommit(false) 用于在您的自定义 ConnectionTester 中针对从站的测试。但我认为最终您不会想要使用此连接测试器，默认的连接测试器就足够了。

您可能还想将连接属性 autoReconnect 设置为 true。

注意:我没有使用过 MySQL 的 ReplicationDriver，这全是快速阅读 docs 后的猜测。 .