最近的一个项目在Hibernate使用C3P0的连接池,数据库为Mysql。开发测试没有问题,在运行中每个一段长的空闲时间就出现异常:
java 代码
- org.hibernate.exception.JDBCConnectionException: could not execute query
- at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:74)
- at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
- .......
- Caused by: com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error:
-
-
- ** BEGIN NESTED EXCEPTION **
-
- com.mysql.jdbc.CommunicationsException
- MESSAGE: Communications link failure due to underlying exception:
-
- ** BEGIN NESTED EXCEPTION **
-
- java.net.SocketException
- MESSAGE: Broken pipe
-
- STACKTRACE:
-
- java.net.SocketException: Broken pipe
- at java.net.SocketOutputStream.socketWrite0(Native Method)
- ......
- ** 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种:
- 增加wait_timeout的时间。
- 减少Connection pools中connection的lifetime。
- 测试Connection pools中connection的有效性。
当然最好的办法是同时综合使用上述3种方法,下面就DBCP和C3P0分别做一说明,假设wait_timeout为默认的8小时
DBCP增加以下配置信息:
-
- validationQuery = "SELECT 1"
-
- testWhileIdle = "true"
-
- timeBetweenEvictionRunsMillis = 3600000
-
- minEvictableIdleTimeMillis = 18000000
-
- testOnBorrow = "true"
C3P0增加以下配置信息:
- testConnectionOnCheckin = true
- //自动测试的table名称
- automaticTestTable=C3P0TestTable
-
- idleConnectionTestPeriod = 18000
-
- maxIdleTime = 25000
-
- testConnectionOnCheckout = true
更多的配置信息大家可以查看C3P0文档,Connector/J文档,以及DBCP的文档。
分享到:
- 2006-12-11 13:56
- 浏览 30783
- 评论(4)
- 论坛回复 / 浏览 (3 / 25615)
- 查看更多
相关推荐
com.mysql.jdbc.CommunicationsException Communications link failure due to underlying exception:
如果单独写一个类,并编译执行访问数据库,正常,但是用web应用来调这个类连接数据库,就会出现错误:com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: ...
The book is in two parts: the underlying structure of a computer, and programming in a high level language and programming methodology. To understand the computer, the authors introduce the LC-3 and ...
new ʻvisualizationsʼ are an attempt to explain the underlying information with a powerful visual impact. They take complex ideas and distil them into beautiful graphics revealing the ...
It includes methodologies and results related to link-level and system-level evaluations of LTE-Advanced. It provides understanding and insight into the advanced underlying technologies in LTE-...
His careful presentation of the underlying theories of fluids is still timely and applicable, even in these days of almost limitless computer power. This reissue ensures that a new generation of ...
An Introduction to Mathematical Cryptography provides an introduction to public key cryptography and underlying mathematics that is required for the subject. Each of the eight chapters expands on a ...
A Practical Introduction to Machine Learning Concepts for Actuaries Alan Chalk, FIA, MSc, and Conan McMurtrie MSc Abstract Motivation. Supervised Learning - building predictive models based on past ...
Each section starts with a brief description of the statistic that is covered and important underlying assumptions, which help students select appropriate statistics. Each section describes how to ...
After detection, the tracking of the multilevel patch features is closely coupled to the underlying extended Kalman filter (EKF) by directly using the intensity errors as innovation term during the ...
Visualizing data in plots and figures exposes the underlying patterns in the data and provides insights. Good visualizations also help you communicate your data to others, and are useful to data ...
an underlying two-dimensional cluster feature, we propose a two-dimensional sparse Bayesian learning method without a priori knowledge of two-dimensional angular spread patterns. It essentially ...
It is aimed at executives who would like to use machine learning in their business and want to understand the underlying mechanics, and for anyone else who wants to understand more about the ...
A Brief Review of ChatGPT: Its Value and the Underlying GPT TechnologyA Brief Review of ChatGPT: Its Value and the Underlying GPT TechnologyA Brief Review of ChatGPT: Its Value and the Underlying GPT ...
This textbook enables readers to design digital systems using the modern HDL approach while ensuring they have a solid foundation of knowledge of the underlying hardware and theory of their designs....
Need to learn how to wrap your head around Git, but don't need a lot of hand holding? Grab this book if you're new to Git, not to the world of programming. Git tasks displayed on two-page spreads ...
This textbook enables readers to design digital systems using the modern HDL approach while ensuring they have a solid foundation of knowledge of the underlying hardware and theory of their designs....
This textbook enables readers to design digital systems using the modern HDL approach while ensuring they have a solid foundation of knowledge of the underlying hardware and theory of their designs....
Building upon Mark Sobell's A Practical Guide to the UNIX System, this book provides step-by-step tutorials for OS X users eager to get the most from the underlying UNIX system. Peter Seebach and ...
in mineral processing industry due to the inherently chaotic nature of the underlying microscopic phenomena and the lack of sufficiently accurate process measurements. Digital image processing is a ...