Communications link failure when execute a procedure in mysql

Question

I hava a web server with mysql, some configuration belows:

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

This exception came out first time was about 2013-12-13 02:33, then I do somethings:
1, add autoReconnect=true to mysql url;
2, add <property name="houseKeepingTestSql" value="select CURRENT_DATE"></property> to ProxoolDataSource configuratin.

I thought the bug was fixed, but I am wrong, this exception appeared again, and almost at the same time(2013-12-16 02:33).

this is stack trace:

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]

And always the procedure querprize() throw this exception, others query seems normal. could you help me to fix this?
Thanks ahead.

Edit
I have fix this problem, At 2:33 am, mysql on this server was backing up data, so much I/O operation was running , and the maxActiveConnectionTime in my project was just 120000 miliseconds. The Proxool datasource closed current connection before the procedure finished and returned, that why the Socket closed exception happened in this situation. So my solution is set the maxActiveConnectionTime much longger.

Answer 1

Having a look at this

it would seem that in addition to the autoReconnect=true url appendage, the following properies would help:

if you use DBCP as connection pool in Spring, add the following entries to your datasource definition

<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>

if you use c3p0 as connection pool in Spring, add the following entries to your datasource definition

<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>