Database timeout - Klaros Test Management - Forum - Klaros Test Management
Database timeout
Thomas Reimer, modified 13 Years ago.
Database timeout
Youngling Posts: 9 Join Date: 11/11/10 Recent Posts
Hi,
I recognized a problem concerning database connection handling. After some hours of Klaros inactivity at night I always get the following exceptions at first login in the next morning:
I already added these settings to the file 'hibernate.properties':
...but that doesn't seem to help. After this exception occurs the connection is established again and after getting two internal errors in Klaros I can continue working normally.
So my question: Is there any opportunity to avoid running into database connection timeout?
Regards,
Thomas
I recognized a problem concerning database connection handling. After some hours of Klaros inactivity at night I always get the following exceptions at first login in the next morning:
08:24:46,927 ERROR JDBCTransaction:95 - JDBC begin failed | |
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 53.510.109 milliseconds ago. The last packet sent successfully to the server was 53.510.110 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:2562) | |
at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4956) | |
at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:331) | |
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:317) | |
at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:91) | |
at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1463) | |
at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:558) | |
at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:374) | |
at org.jboss.seam.ioc.spring.SpringTransaction.begin(SpringTransaction.java:67) | |
at org.jboss.seam.jsf.SeamPhaseListener.begin(SeamPhaseListener.java:593) | |
at org.jboss.seam.jsf.SeamPhaseListener.begin(SeamPhaseListener.java:583) | |
at org.jboss.seam.jsf.SeamPhaseListener.handleTransactionsBeforePhase(SeamPhaseListener.java:327) | |
at org.jboss.seam.jsf.SeamPhaseListener.beforeServletPhase(SeamPhaseListener.java:144) | |
at org.jboss.seam.jsf.SeamPhaseListener.beforePhase(SeamPhaseListener.java:118) | |
at com.sun.faces.lifecycle.Phase.handleBeforePhase(Phase.java:214) | |
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:96) | |
at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:139) | |
at de.verit.klaros.web.beans.application.lifecycle.LifecycleImpl.render(LifecycleImpl.java:96) | |
at com.icesoft.faces.webapp.http.core.JsfLifecycleExecutor.apply(JsfLifecycleExecutor.java:19) | |
at com.icesoft.faces.context.View$2$1.respond(View.java:48) | |
at com.icesoft.faces.webapp.http.servlet.ServletRequestResponse.respondWith(ServletRequestResponse.java:201) | |
at com.icesoft.faces.context.View$2.serve(View.java:77) | |
at com.icesoft.faces.context.View.servePage(View.java:149) | |
at com.icesoft.faces.webapp.http.core.MultiViewServer.service(MultiViewServer.java:67) | |
at com.icesoft.faces.webapp.http.common.ServerProxy.service(ServerProxy.java:11) | |
at com.icesoft.faces.webapp.http.servlet.MainSessionBoundServlet$4.service(MainSessionBoundServlet.java:149) | |
at com.icesoft.faces.webapp.http.common.standard.PathDispatcherServer.service(PathDispatcherServer.java:24) | |
at com.icesoft.faces.webapp.http.servlet.BasicAdaptingServlet.service(BasicAdaptingServlet.java:16) | |
at com.icesoft.faces.webapp.http.servlet.PathDispatcher.service(PathDispatcher.java:23) | |
at com.icesoft.faces.webapp.http.servlet.SessionDispatcher.service(SessionDispatcher.java:53) | |
at com.icesoft.faces.webapp.http.servlet.PathDispatcher.service(PathDispatcher.java:23) | |
at com.icesoft.faces.webapp.http.servlet.MainServlet.service(MainServlet.java:131) | |
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) | |
at de.verit.klaros.web.servlet.ExceptionHandlingPersistentFacesServlet.service(ExceptionHandlingPersistentFacesServlet.java:51) | |
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) | |
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) | |
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) | |
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) | |
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128) | |
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) | |
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) | |
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293) | |
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856) | |
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583) | |
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454) | |
at java.lang.Thread.run(Thread.java:662) | |
Caused by: java.net.SocketException: Broken pipe | |
at java.net.SocketOutputStream.socketWrite0(Native Method) | |
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92) | |
at java.net.SocketOutputStream.write(SocketOutputStream.java:136) | |
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65) | |
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123) | |
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3302) | |
... 48 more | |
08:24:48,708 ERROR Exceptions:86 - handled and logged exception | |
java.lang.IllegalStateException: Could not start transaction | |
at org.jboss.seam.jsf.SeamPhaseListener.begin(SeamPhaseListener.java:598) | |
at org.jboss.seam.jsf.SeamPhaseListener.begin(SeamPhaseListener.java:583) | |
at org.jboss.seam.jsf.SeamPhaseListener.handleTransactionsBeforePhase(SeamPhaseListener.java:327) | |
at org.jboss.seam.jsf.SeamPhaseListener.beforeServletPhase(SeamPhaseListener.java:144) | |
at org.jboss.seam.jsf.SeamPhaseListener.beforePhase(SeamPhaseListener.java:118) | |
at com.sun.faces.lifecycle.Phase.handleBeforePhase(Phase.java:214) | |
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:96) | |
at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:139) | |
at de.verit.klaros.web.beans.application.lifecycle.LifecycleImpl.render(LifecycleImpl.java:96) | |
at com.icesoft.faces.webapp.http.core.JsfLifecycleExecutor.apply(JsfLifecycleExecutor.java:19) | |
at com.icesoft.faces.context.View$2$1.respond(View.java:48) | |
at com.icesoft.faces.webapp.http.servlet.ServletRequestResponse.respondWith(ServletRequestResponse.java:201) | |
at com.icesoft.faces.context.View$2.serve(View.java:77) | |
at com.icesoft.faces.context.View.servePage(View.java:149) | |
at com.icesoft.faces.webapp.http.core.MultiViewServer.service(MultiViewServer.java:67) | |
at com.icesoft.faces.webapp.http.common.ServerProxy.service(ServerProxy.java:11) | |
at com.icesoft.faces.webapp.http.servlet.MainSessionBoundServlet$4.service(MainSessionBoundServlet.java:149) | |
at com.icesoft.faces.webapp.http.common.standard.PathDispatcherServer.service(PathDispatcherServer.java:24) | |
at com.icesoft.faces.webapp.http.servlet.BasicAdaptingServlet.service(BasicAdaptingServlet.java:16) | |
at com.icesoft.faces.webapp.http.servlet.PathDispatcher.service(PathDispatcher.java:23) | |
at com.icesoft.faces.webapp.http.servlet.SessionDispatcher.service(SessionDispatcher.java:53) | |
at com.icesoft.faces.webapp.http.servlet.PathDispatcher.service(PathDispatcher.java:23) | |
at com.icesoft.faces.webapp.http.servlet.MainServlet.service(MainServlet.java:131) | |
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) | |
at de.verit.klaros.web.servlet.ExceptionHandlingPersistentFacesServlet.service(ExceptionHandlingPersistentFacesServlet.java:51) | |
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) | |
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) | |
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) | |
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) | |
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128) | |
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) | |
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) | |
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293) | |
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856) | |
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583) | |
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454) | |
at java.lang.Thread.run(Thread.java:662) | |
Caused by: org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.TransactionException: JDBC begin failed: | |
at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:599) | |
at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:374) | |
at org.jboss.seam.ioc.spring.SpringTransaction.begin(SpringTransaction.java:67) | |
at org.jboss.seam.jsf.SeamPhaseListener.begin(SeamPhaseListener.java:593) | |
... 36 more | |
Caused by: org.hibernate.TransactionException: JDBC begin failed: | |
at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:96) | |
at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1463) | |
at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:558) | |
... 39 more | |
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 53.510.109 milliseconds ago. The last packet sent successfully to the server was 53.510.110 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:2562) | |
at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4956) | |
at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:331) | |
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:317) | |
at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:91) | |
... 41 more | |
Caused by: java.net.SocketException: Broken pipe | |
at java.net.SocketOutputStream.socketWrite0(Native Method) | |
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92) | |
at java.net.SocketOutputStream.write(SocketOutputStream.java:136) | |
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65) | |
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123) | |
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3302) | |
... 48 more |
I already added these settings to the file 'hibernate.properties':
... | |
hibernate.connection.url=jdbc:mysql://a.b.c.d:3306/klaros_db?autoReconnect=true | |
hibernate.connection.autoReconnect=true | |
hibernate.connection.autoReconnectForPools=true | |
... |
...but that doesn't seem to help. After this exception occurs the connection is established again and after getting two internal errors in Klaros I can continue working normally.
So my question: Is there any opportunity to avoid running into database connection timeout?
Regards,
Thomas
Torsten Stolpmann, modified 13 Years ago.
RE: Database timeout (Answer)
Jedi Council Member Posts: 755 Join Date: 2/12/09 Recent Posts
Hi Thomas,
this problem cannot be solved at the client (Klaros) side. MySQL servers have the nasty habit to invalidate client sessions after a configurable timeout (default 8 hours).
To remedy this, you have to configure a bigger timeout in your MySQL Server instance (/etc/mysql.ini) like shown in the attached screenshot. The value shown amounts to 7 days. You have to restart mysql and Klaros afterwards.
Thanks for bringing this issue up again, as this issue has not yet been answered in the forum.
Regards,
Torsten
this problem cannot be solved at the client (Klaros) side. MySQL servers have the nasty habit to invalidate client sessions after a configurable timeout (default 8 hours).
To remedy this, you have to configure a bigger timeout in your MySQL Server instance (/etc/mysql.ini) like shown in the attached screenshot. The value shown amounts to 7 days. You have to restart mysql and Klaros afterwards.
Thanks for bringing this issue up again, as this issue has not yet been answered in the forum.
Regards,
Torsten
Attachments:
Thomas Reimer, modified 13 Years ago.
RE: Database timeout
Youngling Posts: 9 Join Date: 11/11/10 Recent Posts
Hi Torsten,
thanks for your quick reply. My problem is that I cannot change the timeout setting of mysql since the server is a public one with many clients. Due to this I wondered whether it would be possible to insert a little function into Klaros which sends "keepalive requests" - let's say every 30 minutes - to the database server to keep the session open all the time.
What do you think? Could that be a possible solution for the timeout problem?
Regards,
Thomas
thanks for your quick reply. My problem is that I cannot change the timeout setting of mysql since the server is a public one with many clients. Due to this I wondered whether it would be possible to insert a little function into Klaros which sends "keepalive requests" - let's say every 30 minutes - to the database server to keep the session open all the time.
What do you think? Could that be a possible solution for the timeout problem?
Regards,
Thomas
Torsten Stolpmann, modified 13 Years ago.
RE: Database timeout (Answer)
Jedi Council Member Posts: 755 Join Date: 2/12/09 Recent Posts
Hi Thomas,
I see your pain. Yet I fear this may not be sufficient to solve this completely since database sessions are pooled inside Klaros (actually Hibernate is doing this), so individual sessions may still time out if they are pooled and unused long enough.
Nonetheless I opened an improvement issue (KLAROS-1075 - Send keepalive database requests periodically) to track progress on this.
In the meantime you may consider switching to PostgreSQL.
Thanks for your input!
Regards,
Torsten
I see your pain. Yet I fear this may not be sufficient to solve this completely since database sessions are pooled inside Klaros (actually Hibernate is doing this), so individual sessions may still time out if they are pooled and unused long enough.
Nonetheless I opened an improvement issue (KLAROS-1075 - Send keepalive database requests periodically) to track progress on this.
In the meantime you may consider switching to PostgreSQL.
Thanks for your input!
Regards,
Torsten
Torsten Stolpmann, modified 13 Years ago.
RE: Database timeout
Jedi Council Member Posts: 755 Join Date: 2/12/09 Recent Posts
Hi Thomas,
we enabled a proper connection pooling in the upcoming 3.2.2 release. This should finally solve your session timeout problems.
Regards,
Torsten
we enabled a proper connection pooling in the upcoming 3.2.2 release. This should finally solve your session timeout problems.
Regards,
Torsten
Thomas Reimer, modified 13 Years ago.
RE: Database timeout
Youngling Posts: 9 Join Date: 11/11/10 Recent Posts
Hi Torsten,
now the connection handling works much better. We experienced no timeouts since update to release 3.2.2 anymore. Thanks for the quick solving!
Regards,
Thomas
now the connection handling works much better. We experienced no timeouts since update to release 3.2.2 anymore. Thanks for the quick solving!
Regards,
Thomas