Heim > Datenbank > MySQL-Tutorial > DBCP Mysql报错!Communications link failure due to underlyin

DBCP Mysql报错!Communications link failure due to underlyin

WBOY
Freigeben: 2016-06-07 15:01:58
Original
1316 Leute haben es durchsucht

最近的一个项目在Hibernate使用C3P0的连接池,数据库为Mysql。开发测试没有问题,在运行中每个一段长的空闲时间就出现异常: java 代码 org.hibernate.exception.JDBCConnectionException: could not execute query at org.hibernate.exception.SQLStateConve

最近的一个项目在Hibernate使用C3P0的连接池,数据库为Mysql。开发测试没有问题,在运行中每个一段长的空闲时间就出现异常:

java 代码

  1. org.hibernate.exception.JDBCConnectionException: could not execute query
  2. at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:74)
  3. at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
  4. .......
  5. Caused by: com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error:
  6. ** BEGIN NESTED EXCEPTION **
  7. com.mysql.jdbc.CommunicationsException
  8. MESSAGE: Communications link failure due to underlying exception:
  9. ** BEGIN NESTED EXCEPTION **
  10. java.net.SocketException
  11. MESSAGE: Broken pipe
  12. STACKTRACE:
  13. java.net.SocketException: Broken pipe
  14. at java.net.SocketOutputStream.socketWrite0(Native Method)
  15. ......
  16. ** END NESTED EXCEPTION **

查看了Mysql的文档,以及Connector/J的文档以及在线说明发现,出现这种异常的原因是:

Mysql服务器默认的“wait_timeout”是8小时,也就是说一个connection空闲超过8个小时,Mysql将自动断开该connection。这就是问题的所在,在C3P0 pools中的connections如果空闲超过8小时,Mysql将其断开,而C3P0并不知道该connection已经失效,如果这时有Client请求connection,C3P0将该失效的Connection提供给Client,将会造成上面的异常。

解决的方法有3种:

  1. 增加wait_timeout的时间。
  2. 减少Connection pools中connection的lifetime。
  3. 测试Connection pools中connection的有效性。

当然最好的办法是同时综合使用上述3种方法,下面就DBCP和C3P0分别做一说明,假设wait_timeout为默认的8小时

DBCP增加以下配置信息:

  1. //set to 'SELECT 1'
  2. validationQuery = "SELECT 1"
  3. //set to 'true'
  4. testWhileIdle = "true"
  5. //some positive integer
  6. timeBetweenEvictionRunsMillis = 3600000
  7. //set to something smaller than 'wait_timeout'
  8. minEvictableIdleTimeMillis = 18000000
  9. //if you don't mind a hit for every getConnection(), set to "true"
  10. testOnBorrow = "true"

C3P0增加以下配置信息:

  1. //获取connnection时测试是否有效
  2. testConnectionOnCheckin = true
  3. //自动测试的table名称
  4. automaticTestTable=C3P0TestTable
  5.  //set to something much less than wait_timeout, prevents connections from going stale
  6. idleConnectionTestPeriod = 18000
  7. //set to something slightly less than wait_timeout, preventing 'stale' connections from being handed out
  8. maxIdleTime = 25000
  9. //if you can take the performance 'hit', set to "true"
  10. testConnectionOnCheckout = true

更多的配置信息大家可以查看C3P0文档,Connector/J文档,以及DBCP的文档。

Verwandte Etiketten:
Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage