The last packet successfully received from the server was 30,250,599 milliseconds ago.
The last packet sent successfully to the server was 30,250,600 milliseconds ago.
is longer than the server configured value of 'wait_timeout'.
You should consider either expiring and/or testing connection validity before use in your
application, increasing the server configured values for client timeouts, or using the
Connector/J connection property 'autoReconnect=true' to avoid this problem.
Mysql服务器默认的“wait_timeout”是8小时,也就是说一个connection空闲超过8个小时,Mysql将自动断开该connection。这就是问题的所在,在C3P0 pools中的connections如果空闲超过8小时,Mysql将其断开,而C3P0并不知道该connection已经失效,如果这时有Client请求connection,C3P0将该失效的Connection提供给Client,将会造成上面的异常。
解决办法如下
<!-- 获取数据源 -->
<bean id="ds" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="${driverClass}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
<!-- 空闲时是否进行验证,检查对象是否有效 -->
<property name="testWhileIdle" value="true"/>
<!-- 验证连接是否成功,至少需要返回一行结果 -->
<property name="validationQuery" value="select 1 from login"/>
<!-- 每timeBetweenEvictionRunsMillis毫秒检查一次连接池中空闲的连接,
把空闲时间超过minEvictableIdleTimeMillis毫秒的连接断开,直到连接池中的连接数到minIdle为止. -->
<property name="timeBetweenEvictionRunsMillis" value="3600000"/>
<property name="minEvictableIdleTimeMillis" value="3600000"/>
<!-- 设置为true,程序在从连接池获取前通过validationQuery检验连接是否可用,若已经失效就取另一个 -->
<property name="testOnBorrow" value="true"/>
</bean>