mysql数据库的客户端连接在闲置8个小时(28800秒)之后会自动断开,在java项目中,如果你使用了连接池,不管是c3p0,还是阿里的数据源DRUID,亦或是dbcp,还是其他连接池,都会有这个问题,听别人一般称作“mysql 8小时问题”。

具体现象:比如你项目使用的是c3p0,配置好了连接池之后,在项目启动了,你就离开了,项目没有任何人使用,在8个小时之后,再去点击操作mysql数据库的操作(比如,查询一个学生列表),就会报错:

org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.

........省略部分堆栈报错信息

 

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.

........省略部分堆栈报错信息

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

 

The last packet successfully received from the server was 53,692,968 milliseconds ago.  The last packet sent successfully to the server was 53,692,978 milliseconds ago.

........省略部分堆栈报错信息

Caused by: java.net.SocketException: Connection reset by peer: socket write error

at java.net.SocketOutputStream.socketWrite0(Native Method)

at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:111)

at java.net.SocketOutputStream.write(SocketOutputStream.java:155)

at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)

at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)

at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3634)

... 95 common frames omitted

这时候需要在c3p0的配置里加上如下两句:

<property name="idleConnectionTestPeriod" value="28000" />
<property name="maxIdleTime" value="28000" />

maxIdleTime=28000表示c3p0里面的连接在28000秒(<28800秒)后自动销毁,重新创建新的连接,这样就避免了你在从连接池获取连接的时候拿到已经被mysql单方面销毁的连接。

完整的c3p0配置参考:

	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="driverClass" value="${c3p0.driverClass}" />
		<property name="jdbcUrl" value="${c3p0.jdbcUrl}" />
		<property name="user" value="${c3p0.user}" />
		<property name="password" value="${c3p0.password}" />
		<property name="maxPoolSize" value="${c3p0.maxPoolSize}" />
		<property name="minPoolSize" value="${c3p0.minPoolSize}" />
		<property name="initialPoolSize" value="${c3p0.initialPoolSize}" />
		<property name="acquireIncrement" value="${c3p0.acquireIncrement}" />
		<!-- mysql默认28800s/60/60=8h后单方面关闭空闲连接,c3p0默认经过600万秒才释放 -->
		<property name="idleConnectionTestPeriod" value="28000" />
		<property name="maxIdleTime" value="28000" />
	</bean>

 

Logo

腾讯云面向开发者汇聚海量精品云计算使用和开发经验,营造开放的云计算技术生态圈。

更多推荐