com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed
I built an application and deployed locally ... and it was working perfectly. I deployed it on a remote server and started getting the exception mentioned in the subject line. It's not because of any firewall issues.
I changed my hibernate.xml to connect via my IP address rather then localhost and now I get the same timeouts on my locally deployed application. I get this error when I keep the application running for more than one day.
I am not performing any operations after committing transactions or closing sessions myself. I am using the following properties in hibernate.cfg.xml
<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://myremotehost:3306/akp</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password">root</property>
<property name="hibernate.show_sql">false</property>
<property name="hibernate.current_session_context_class">thread</property>
<property name="hibernate.query.factory_class">org.hibernate.hql.ast.ASTQueryTranslatorFactory</property>
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed by the driver.
Detailed:
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed by the driver.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1193)
at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1180)
at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4137)
at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4103)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:423)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547)
at org.hibernate.loader.Loader.doQuery(Loader.java:673)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 36 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 34,247,052 milliseconds ago. The last packet sent successfully to the server was 34,247,052 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.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1118)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3321)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1940)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2275)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at com.xyz.abc.DAO.GenericHibernateDAO.findByField(GenericHibernateDAO.java:119)
at com.xyz.abc.DAO.JobDAO.getJobsByLdap(JobDAO.java:115)
at com.xyz.abc.business.Jcr.getMyruns(Jcr.java:272)
at com.xyz.abc.business.abcService.getMyruns(abcService.java:54)
at sun.reflect.GeneratedMethodAccessor139.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.axis2.rpc.receivers.RPCUtil.invokeServiceClass(RPCUtil.java开发者_开发百科:194)
at org.apache.axis2.rpc.receivers.RPCMessageReceiver.invokeBusinessLogic(RPCMessageReceiver.java:102)
at org.apache.axis2.receivers.AbstractInOutMessageReceiver.invokeBusinessLogic(AbstractInOutMessageReceiver.java:40)
at org.apache.axis2.receivers.AbstractMessageReceiver.receive(AbstractMessageReceiver.java:114)
at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:173)
at org.apache.axis2.transport.http.HTTPTransportUtils.processHTTPPostRequest(HTTPTransportUtils.java:173)
at org.apache.axis2.transport.http.AxisServlet.doPost(AxisServlet.java:142)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:203)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:108)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:379)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:242)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:259)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:237)
... 4 more
Caused by: java.net.SocketException: Software caused connection abort: socket write error
Does anyone have any ideas what might cause this behavior?
EDIT: Now am using folloing in my hibernate.cfg.xml file.Is it correct?
<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/xyz</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password">root</property>
<property name="hibernate.show_sql">false</property>
<property name="hibernate.current_session_context_class">thread</property>
<property name="hibernate.query.factory_class">org.hibernate.hql.ast.ASTQueryTranslatorFactory</property>
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">20</property>
<!-- <property name="hibernate.c3p0.max_size">1800</property>-->
<property name="hibernate.c3p0.max_statements">50</property>
<property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
<property name="c3p0.max_statements">0</property>
<property name="c3p0.maxIdleTimeExcessConnections">3600</property>
<property name="c3p0.idleConnectionTestPeriod">3600</property>
<property name="c3p0.maxIdleTime">3600</property>
As @swanliu pointed out it is due to a bad connection.
However before adjusting the server timing and client timeout , I would first try and use a better connection pooling strategy.
Connection Pooling
Hibernate itself admits that its connection pooling strategy is minimal
Hibernate's own connection pooling algorithm is, however, quite rudimentary. It is intended to help you get started and is not intended for use in a production system, or even for performance testing. You should use a third party pool for best performance and stability. Just replace the hibernate.connection.pool_size property with connection pool specific settings. This will turn off Hibernate's internal pool. For example, you might like to use c3p0.
As stated in Reference : http://docs.jboss.org/hibernate/core/3.3/reference/en/html/session-configuration.html
I personally use C3P0. however there are other alternatives available including DBCP.
Check out
- http://www.mchange.com/projects/c3p0/index.html
- http://commons.apache.org/dbcp/
Below is a minimal configuration of C3P0 used in my application:
<property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
<property name="c3p0.acquire_increment">1</property>
<property name="c3p0.idle_test_period">100</property> <!-- seconds -->
<property name="c3p0.max_size">100</property>
<property name="c3p0.max_statements">0</property>
<property name="c3p0.min_size">10</property>
<property name="c3p0.timeout">1800</property> <!-- seconds -->
By default, pools will never expire Connections. If you wish Connections to be expired over time in order to maintain "freshness", set maxIdleTime and/or maxConnectionAge. maxIdleTime defines how many seconds a Connection should be permitted to go unused before being culled from the pool. maxConnectionAge forces the pool to cull any Connections that were acquired from the database more than the set number of seconds in the past.
As stated in Reference : http://www.mchange.com/projects/c3p0/index.html#managing_pool_size
Edit:
I updated the configuration file (Reference), as I had just copy pasted the one for my project earlier.
The timeout should ideally solve the problem, If that doesn't work for you there is an expensive solution which I think you could have a look at:
Create a file “c3p0.properties” which must be in the root of the classpath (i.e. no way to override it for particular parts of the application). (Reference)
# c3p0.properties
c3p0.testConnectionOnCheckout=true
With this configuration each connection is tested before being used. It however might affect the performance of the site.
MySQL implicitly closed the database connection because the connection has been inactive for too long (34,247,052 milliseconds ≈ 9.5 hours).
If your program then fetches a bad connection from the connection-pool that causes the MySQLNonTransientConnectionException: No operations allowed after connection closed.
MySQL suggests:
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=trueto avoid this problem.
If you don't want use connection pool (you sure, that your app has only one connection), you can do this - if connection falls you must establish new one - call method .openSession() instead .getCurrentSession()
For example:
SessionFactory sf = null;
// get session factory
// ...
//
Session session = null;
try {
session = sessionFactory.getCurrentSession();
} catch (HibernateException ex) {
session = sessionFactory.openSession();
}
If you use Mysql, you can set autoReconnect property:
<property name="hibernate.connection.url">jdbc:mysql://127.0.0.1/database?autoReconnect=true</property>
I hope this helps.
First Replace the MySQL dependency as given below
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.44</version> </dependency>An error showing "Authentication plugin 'caching_sha2_password'" will appear. Run this command:
mysql -u root -p ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Please make sure you are using latest jdbc connector as per the mysql. I was facing this problem and when I replaced my old jdbc connector with the latest one, the problem was solved.
You can download latest jdbc driver from https://dev.mysql.com/downloads/connector/j/
Select Operating System as Platform Independent. It will show you two options. One as tar and one as zip. Download the zip and extract it to get the jar file and replace it with your old connector.
This is not only for hibernate framework, it can be used with any platform which requires a jdbc connector.
This is due to using obsolete mysql-connection-java version, your MySQl is updated but not your MySQL jdbc Driver, you can update your connection jar from the official site Official MySQL Connector site. Good Luck.
加载中,请稍侯......
精彩评论