java - 在mysql中执行过程时通信链接失败

Question

我有一个带有 mysql 的 web 服务器，一些配置如下:

mysql: mysql  Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (x86_64) using readline 5.1
spring: 3.05
mysql-driver:5.1.13

第一次出现这个异常是关于2013-12-13 02:33 ，然后我做一些事情:
1、添加autoReconnect=true到 mysql url；
2、添加<property name="houseKeepingTestSql" value="select CURRENT_DATE"></property>到 ProxoolDataSource 配置。

我以为bug修复了，但是我错了，这个异常又出现了，而且几乎是同一时间(2013-12-16 02:33)。

这是堆栈跟踪:

The last packet successfully received from the server was 131,609 milliseconds ago.  The last packet sent successfully to the server was 2 milliseconds ago.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.6.0_41]
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) ~[na:1.6.0_41]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) ~[na:1.6.0_41]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513) ~[na:1.6.0_41]
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:409) ~[mysql-connector-java-5.1.13-bin.jar:na]
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1118) ~[mysql-connector-java-5.1.13-bin.jar:na]
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3055) ~[mysql-connector-java-5.1.13-bin.jar:na]
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2941) ~[mysql-connector-java-5.1.13-bin.jar:na]
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489) ~[mysql-connector-java-5.1.13-bin.jar:na]
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959) ~[mysql-connector-java-5.1.13-bin.jar:na]
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113) ~[mysql-connector-java-5.1.13-bin.jar:na]
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568) ~[mysql-connector-java-5.1.13-bin.jar:na]
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113) ~[mysql-connector-java-5.1.13-bin.jar:na]
        at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1364) ~[mysql-connector-java-5.1.13-bin.jar:na]
        at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:879) ~[mysql-connector-java-5.1.13-bin.jar:na]
        at sun.reflect.GeneratedMethodAccessor121.invoke(Unknown Source) ~[na:na]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) ~[na:1.6.0_41]
        at java.lang.reflect.Method.invoke(Method.java:597) ~[na:1.6.0_41]
        at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100) ~[proxool-0.9.1.jar:na]
        at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57) ~[proxool-0.9.1.jar:na]
        at $java.sql.Wrapper$$EnhancerByProxool$$df6af44.execute(<generated>) ~[proxool-cglib.jar:na]
        at sun.reflect.GeneratedMethodAccessor74.invoke(Unknown Source) ~[na:na]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) ~[na:1.6.0_41]
        at java.lang.reflect.Method.invoke(Method.java:597) ~[na:1.6.0_41]
        at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:58) ~[mybatis-3.1.1.jar:3.1.1]
        at com.sun.proxy.$Proxy39.execute(Unknown Source) ~[na:na]
        at org.apache.ibatis.executor.statement.CallableStatementHandler.query(CallableStatementHandler.java:63) ~[mybatis-3.1.1.jar:3.1.1]
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:70) ~[mybatis-3.1.1.jar:3.1.1]
        at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:57) ~[mybatis-3.1.1.jar:3.1.1]
        at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267) ~[mybatis-3.1.1.jar:3.1.1]
        at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:141) ~[mybatis-3.1.1.jar:3.1.1]
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105) ~[mybatis-3.1.1.jar:3.1.1]
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81) ~[mybatis-3.1.1.jar:3.1.1]
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:101) ~[mybatis-3.1.1.jar:3.1.1]
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:95) ~[mybatis-3.1.1.jar:3.1.1]
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:59) ~[mybatis-3.1.1.jar:3.1.1]
        at sun.reflect.GeneratedMethodAccessor91.invoke(Unknown Source) ~[na:na]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) ~[na:1.6.0_41]
        at java.lang.reflect.Method.invoke(Method.java:597) ~[na:1.6.0_41]
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:355) ~[mybatis-spring-1.1.1.jar:1.1.1]
        ... 28 common frames omitted
Caused by: java.net.SocketException: Socket closed
        at java.net.SocketInputStream.socketRead0(Native Method) ~[na:1.6.0_41]
        at java.net.SocketInputStream.read(SocketInputStream.java:129) ~[na:1.6.0_41]
        at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114) ~[mysql-connector-java-5.1.13-bin.jar:na]
        at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161) ~[mysql-connector-java-5.1.13-bin.jar:na]
        at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189) ~[mysql-connector-java-5.1.13-bin.jar:na]
        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2499) ~[mysql-connector-java-5.1.13-bin.jar:na]

而且总是程序querprize()抛出此异常，其他查询似乎正常。你能帮我解决这个问题吗？
先谢谢了。

编辑
我已经解决了这个问题，凌晨2点33分，这台服务器上的mysql正在备份数据，这么多I/O操作正在运行，并且 maxActiveConnectionTime在我的项目中只有 120000 毫秒。Proxool 数据源在过程完成并返回之前关闭了当前连接，这就是为什么 Socket closed在这种情况下发生异常。所以我的解决方案是设置 maxActiveConnectionTime更长。

Answer 1

看看this

似乎除了 autoReconnect=true url 附件之外，以下属性也会有所帮助:

如果您在 Spring 中使用 DBCP 作为连接池，请将以下条目添加到您的数据源定义中

<bean id="datasource" destroy-method="close">
<property name="driverClassName” value="${.jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<property name="validationQuery" value="SELECT 1" />  <--- This
<property name="testOnBorrow" value="true" />         <---- and this
</bean>

如果您在 Spring 中使用 c3p0 作为连接池，请将以下条目添加到您的数据源定义中

<bean id=”pooledDataSource” class=”com.mchange.v2.c3p0.ComboPooledDataSource” destroy-method=”close”>

<property name=”driverClass” value=”${driver}”></property>
<property name=”jdbcUrl” value=”${url}” />
<property name=”user” value=”${username}” />
<property name=”password” value=”${password}” />
<property name=”initialPoolSize” value=”20″ />
<property name=”minPoolSize” value=”10″ />
<property name=”maxPoolSize” value=”50″ />
<property name=”checkoutTimeout” value=”1000″ />
<property name=”maxStatements” value=”500″ />
<property name=”testConnectionOnCheckin” value=”false” />   <---- this
<property name=”testConnectionOnCheckout” value=”true” />   <-    and this
<property name=”maxIdleTime” value=”1800″ />
<property name=”idleConnectionTestPeriod” value=”1000″ />
</bean>