testonborrow mysql_DBCP连接池TestOnBorrow的坑

生产环境连接池TestOnBorrow设置为false，导致有时获取的连接不可用。分析如下：

TestOnBorrow=false时，由于不检测池里连接的可用性，于是假如连接池中的连接被数据库关闭了，应用通过连接池getConnection时，都可能获取到这些不可用的连接，且这些连接如果不被其他线程回收的话，它们不会被连接池被废除，也不会重新被创建，占用了连接池的名额。

The last packet successfully received from the server was 19,956 milliseconds ago. The last packet sent successfully to the server was 32 milliseconds ago.

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1571)

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:3143)

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

... 8 more

当TestOnBorrow=true时，有两种情况：

① 集群某实例宕掉时，如果连接刚好不处于通信阶段，tcp连接正处于CLOSE_WAIT状态或已关闭，当应用通过连接池getConnection时，在borrow时会检测连接，由于连接已关闭，于是报了如下报错，并重新建立新连接，此时的新连接到集群的其他实例上了。后面能正常通信。

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

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:3143)

at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:597)

... 21 more

② 集群某实例宕掉时，如果连接刚好处于通信阶段，由于客户端无法立即感知服务端已断连接，它可能会报如下错误，等待服务端的响应超时报错。当应用通过连接池getConnection时，在borrow时会检测连接，由于连接已关闭，于是报了如下报错，并重新建立新连接，此时的新连接到集群的其他实例上了。后面能正常通信。

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 10,538 milliseconds ago. The last packet sent successfully to the server was 10,306 milliseconds ago.

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

========广告时间========

鄙人的新书《Tomcat内核设计剖析》已经在京东销售了，有需要的朋友可以到 https://item.jd.com/12185360.html 进行预定。感谢各位朋友。

=========================

欢迎关注：