Resolving Connection Invalidations in MySQL Read/Write Splitting with Druid and ReplicationConnection

Problem Background

Several services experienced connection invalidation errors when using Druid as the datasource combined with MySQL's ReplicationConnection for read/write splitting. The logs showed that idle connections were closed by the MySQL server, leading to errors such as "The last packet successfully received from server was ... milliseconds ago".

Main Configuration

JDBC URL:

jdbc:mysql:replication://master_host:port,slave_host:port/database_name

Druid settings: testWhileIdle=true and timeBetweenEvictionRunsMillis=6000L (checks idle connections after 1 minute).

if (testWhileIdle) {
    final DruidConnectionHolder holder = poolableConnection.holder;
    long currentTimeMillis = System.currentTimeMillis();
    long lastActiveTimeMillis = holder.lastActiveTimeMillis;
    long lastExecTimeMillis = holder.lastExecTimeMillis;
    long lastKeepTimeMillis = holder.lastKeepTimeMillis;
    if (checkExecuteTime && lastExecTimeMillis != lastActiveTimeMillis) {
        lastActiveTimeMillis = lastExecTimeMillis;
    }
    if (lastKeepTimeMillis > lastActiveTimeMillis) {
        lastActiveTimeMillis = lastKeepTimeMillis;
    }
    long idleMillis = currentTimeMillis - lastActiveTimeMillis;
    long timeBetweenEvictionRunsMillis = this.timeBetweenEvictionRunsMillis;
    if (timeBetweenEvictionRunsMillis <= 0) {
        timeBetweenEvictionRunsMillis = DEFAULT_TIME_BETWEEN_EVICTION_RUNS_MILLIS;
    }
    if (idleMillis >= timeBetweenEvictionRunsMillis || idleMillis < 0) {
        boolean validate = testConnectionInternal(poolableConnection.holder, poolableConnection.conn);
        if (!validate) {
            if (LOG.isDebugEnabled()) {
                LOG.debug("skip not validate connection.");
            }
            discardConnection(poolableConnection.holder);
            continue;
        }
    }
}

Root Cause

When Druid checks connections, it only validates the master connection inside the ReplicationConnection proxy. Slave connections that remain idle are not pinged, so they become stale and cause the observed errors.

Analysis of MySQL‑JDBC ReplicationConnection

The JDBC URL with the replication scheme creates a ReplicationConnection proxy. The first host is treated as master; subsequent hosts are slaves, and load balancing among slaves is random.

public static ReplicationConnection createProxyInstance(List<String> masterHostList, Properties masterProperties, List<String> slaveHostList, Properties slaveProperties) throws SQLException {
    ReplicationConnectionProxy connProxy = new ReplicationConnectionProxy(masterHostList, masterProperties, slaveHostList, slaveProperties);
    return (ReplicationConnection) java.lang.reflect.Proxy.newProxyInstance(ReplicationConnection.class.getClassLoader(), INTERFACES_TO_PROXY, connProxy);
}

The proxy holds both masterConnection and slavesConnection. Switching between them is done via the readOnly flag.

Druid's MySQL Connection Checker

Druid uses MySqlValidConnectionChecker which, by default, employs the MySQL driver's pingInternal method to validate connections.

public boolean isValidConnection(Connection conn, String validateQuery, int validationQueryTimeout) throws Exception {
    if (conn.isClosed()) {
        return false;
    }
    if (usePingMethod) {
        if (conn instanceof DruidPooledConnection) {
            conn = ((DruidPooledConnection) conn).getConnection();
        }
        if (conn instanceof ConnectionProxy) {
            conn = ((ConnectionProxy) conn).getRawObject();
        }
        if (clazz.isAssignableFrom(conn.getClass())) {
            if (validationQueryTimeout <= 0) {
                validationQueryTimeout = DEFAULT_VALIDATION_QUERY_TIMEOUT;
            }
            try {
                ping.invoke(conn, true, validationQueryTimeout * 1000);
            } catch (InvocationTargetException e) {
                Throwable cause = e.getCause();
                if (cause instanceof SQLException) {
                    throw (SQLException) cause;
                }
                throw e;
            }
            return true;
        }
    }
    // fallback to validation query
    String query = (validateQuery == null || validateQuery.isEmpty()) ? DEFAULT_VALIDATION_QUERY : validateQuery;
    Statement stmt = null;
    ResultSet rs = null;
    try {
        stmt = conn.createStatement();
        if (validationQueryTimeout > 0) {
            stmt.setQueryTimeout(validationQueryTimeout);
        }
        rs = stmt.executeQuery(query);
        return true;
    } finally {
        JdbcUtils.close(rs);
        JdbcUtils.close(stmt);
    }
}

Because the checker only pings the master connection, idle slave connections remain unchecked and become invalid.

Proposed Solution

Extend MySqlValidConnectionChecker to invoke ReplicationConnection.ping(), which pings both master and slave connections.

public class MySqlReplicationCompatibleValidConnectionChecker extends MySqlValidConnectionChecker {
    @Override
    public boolean isValidConnection(Connection conn, String validateQuery, int validationQueryTimeout) throws Exception {
        if (conn.isClosed()) {
            return false;
        }
        if (conn instanceof DruidPooledConnection) {
            conn = ((DruidPooledConnection) conn).getConnection();
        }
        if (conn instanceof ConnectionProxy) {
            conn = ((ConnectionProxy) conn).getRawObject();
        }
        if (conn instanceof ReplicationConnection) {
            try {
                ((ReplicationConnection) conn).ping();
                LOG.info("validate connection success: connection=" + conn.toString());
                return true;
            } catch (SQLException e) {
                LOG.error("validate connection error: connection=" + conn.toString(), e);
                throw e;
            }
        }
        return super.isValidConnection(conn, validateQuery, validationQueryTimeout);
    }
}

Configure Druid to use this custom checker, optionally setting loadBalancePingTimeout on the JDBC URL for timeout control.

Other Considerations

Updating the MySQL‑JDBC driver version or the Druid version alone does not resolve the issue. Adjusting the read/write splitting implementation or extending the driver are possible but involve higher effort.

For more practical experiences from ZhiZhi Company, follow the public account linked below.