mysql ping 阻塞_MySQL获取连接ping命令验证连接是否有效时阻塞

MySQL: 5.5.28-enterprise

mysql-connector-java: 5.1.34

Druid: 1.0.14

应用服务器经A10到MySQL服务器。

Druid连接配置: 自定义的多数据源配置文件，标签和druid支持的属性一致

CORE_DB

com.mysql.jdbc.Driver

jdbc:mysql://xxxxxxxxxxxxxx?useUnicode=true&characterEncoding=utf8

xxxxxx

xxxxxx

3

true

SELECT 'x'

60000

STAT_DB

com.mysql.jdbc.Driver

jdbc:mysql://xxxxxxxxxxxxxx?useUnicode=true&characterEncoding=utf8

xxxxxx

xxxxxx

3

true

SELECT 'x'

60000

现象：如图jstack信息，该线程从21号17点阻塞到现在。

java.lang.Thread.State: RUNNABLE

at java.net.SocketInputStream.socketRead0(Native Method)

at java.net.SocketInputStream.read(SocketInputStream.java:129)

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)

- locked <0x00000007ac9cbc78> (a com.mysql.jdbc.util.ReadAheadInputStream)

at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2911)

at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3337)

at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3327)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)

at com.mysql.jdbc.ConnectionImpl.pingInternal(ConnectionImpl.java:3971)

at com.mysql.jdbc.ConnectionImpl.ping(ConnectionImpl.java:3951)

at sun.reflect.GeneratedMethodAccessor54.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at com.alibaba.druid.pool.vendor.MySqlValidConnectionChecker.isValidConnection(MySqlValidConnectionChecker.java:98)

at com.alibaba.druid.pool.DruidAbstractDataSource.testConnectionInternal(DruidAbstractDataSource.java:1252)

at com.alibaba.druid.pool.DruidDataSource.getConnectionDirect(DruidDataSource.java:1000)

at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:940)

at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:930)

at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:102)

读到com.mysql.jdbc.ConnectionImpl的源码：得知timeoutMillis=0，若在失效的连接(Druid认为有效，但实际被A10中断)上执行ping检查，将一直阻塞。

public void ping() throws SQLException {

pingInternal(true, 0);

}

public void pingInternal(boolean checkForClosedConnection, int timeoutMillis) throws SQLException {

if (checkForClosedConnection) {

checkClosed();

}

long pingMillisLifetime = getSelfDestructOnPingSecondsLifetime();

int pingMaxOperations = getSelfDestructOnPingMaxOperations();

if ((pingMillisLifetime > 0 && (System.currentTimeMillis() - this.connectionCreationTimeMillis) > pingMillisLifetime)

|| (pingMaxOperations > 0 && pingMaxOperations <= this.io.getCommandCount())) {

close();

throw SQLError.createSQLException(Messages.getString("Connection.exceededConnectionLifetime"), SQLError.SQL_STATE_COMMUNICATION_LINK_FAILURE,

getExceptionInterceptor());

}

// Need MySQL-3.22.1, but who uses anything older!?

this.io.sendCommand(MysqlDefs.PING, null, null, false, null, timeoutMillis);

}

网上找到一个问题相同，且场景类似的帖子：http://www.cnblogs.com/zhukunrong/p/4525955.html

怀疑是A10连接时间设置太短，过早中断应用服务器与MySQL服务器之间维持的长连接。

询问网管得知A10设置120秒中断连接。

准备修改配置如下：添加获取连接最大等待时间

CORE_DB

com.mysql.jdbc.Driver

jdbc:mysql://xxxxxxxxxxxxxx?useUnicode=true&characterEncoding=utf8

xxxxxx

xxxxxx

3

5000

true

SELECT 'x'

60000

STAT_DB

com.mysql.jdbc.Driver

jdbc:mysql://xxxxxxxxxxxxxx?useUnicode=true&characterEncoding=utf8

xxxxxx

xxxxxx

3

5000

true

SELECT 'x'

60000

观察一段时间再回复，有没解决问题。