How to stop database sleeping
I am getting an exception when calling database code after a period of inactivity
java.sql.SQLNonTransientConnectionException: Could not read resultset: Connection reset at org.mariadb.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:136) at org.mariadb.jdbc.internal.SQLExceptionMapper.throwException(SQLExceptionMapper.java:106) at org.mariadb.jdbc.MySQLStatement.executeQueryEpilog(MySQLStatement.java:264) at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:288) at org.mariadb.jdbc.MySQLStatement.executeQuery(MySQLStatement.java:302) at org.mariadb.jdbc.MySQLStatement.executeQuery(MySQLStatement.java:361) at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208) at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208) at com.vimba.database.DBFactory.attemptLoginWithTempPasswordDetails(DBFactory.java:181) at com.vimba.database.DBFactory.authenticate(DBFactory.java:131) at com.vimba.service.ExposedFunctions.login(ExposedFunctions.java:88) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:483) at com.sun.xml.ws.api.server.InstanceResolver$1.invoke(InstanceResolver.java:210) at com.sun.xml.ws.server.InvokerTube$2.invoke(InvokerTube.java:132) at com.sun.xml.ws.server.sei.EndpointMethodHandler.invoke(EndpointMethodHandler.java:241) at com.sun.xml.ws.server.sei.SEIInvokerTube.processRequest(SEIInvokerTube.java:74) at com.sun.xml.ws.api.pipe.Fiber.doRun(Fiber.java:559) at com.sun.xml.ws.api.pipe.Fiber._doRun(Fiber.java:518) at com.sun.xml.ws.api.pipe.Fiber.doRun(Fiber.java:503) at com.sun.xml.ws.api.pipe.Fiber.runSync(Fiber.java:400) at com.sun.xml.ws.server.WSEndpointImpl$2.process(WSEndpointImpl.java:226) at com.sun.xml.ws.transport.http.HttpAdapter$HttpToolkit.handle(HttpAdapter.java:375) at com.sun.xml.ws.transport.http.HttpAdapter.handle(HttpAdapter.java:175) at com.sun.xml.ws.transport.http.servlet.ServletAdapter.handle(ServletAdapter.java:134) at com.sun.xml.ws.transport.http.servlet.WSServletDelegate.doPost(WSServletDelegate.java:159) at com.sun.xml.ws.transport.http.servlet.WSServlet.doPost(WSServlet.java:49) at javax.servlet.http.HttpServlet.service(HttpServlet.java:754) at javax.servlet.http.HttpServlet.service(HttpServlet.java:847) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:295) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:149) at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:169) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:145) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:97) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:102) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:336) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:653) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:920) at java.lang.Thread.run(Thread.java:745) Caused by: org.mariadb.jdbc.internal.common.QueryException: Could not read resultset: Connection reset at org.mariadb.jdbc.internal.mysql.MySQLProtocol.getResult(MySQLProtocol.java:926) at org.mariadb.jdbc.internal.mysql.MySQLProtocol.executeQuery(MySQLProtocol.java:991) at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:281) ... 40 more Caused by: java.net.SocketException: Connection reset
I have tried to change the /etc/my.cnf to add a wait_timeout of a week but it has not made a difference;
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock
- Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0
- Settings user and group are ignored when systemd is used.
- If you need to run mysqld under a different user or group,
- customize your systemd unit file for mysqld/mariadb according to the
- instructions in http://fedoraproject.org/wiki/Systemd
- Currently, there are mariadb and community-mysql packages in Fedora.
- This particular config file is included in respective RPMs of both of them,
- so the following settings are general and will be also used by both of them.
- Otherwise the RPMs would be in conflict.
- Settings for particular implementations like MariaDB are then
- defined in appropriate sections; for MariaDB server in [mariadb] section in
- /etc/my.cnf.d/server.cnf (part of mariadb-server).
- It doesn't matter that we set these settings only for [mysqld] here,
- because they will be read and used in mysqld_safe as well. log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
[mysqld_safe]
- include all files from the config directory
- !includedir /etc/my.cnf.d
- one week timeout to see if exception goes wait_timeout=604800
event_scheduler=on /etc/my.cnf (END)
Can someone suggest how to stop this please?
Answer Answered by Steel Brain in this comment.
You might want to ping the database periodically. after lets say every 30 seconds. a single query like `select 1` to keep the connection alive works well.