MySQL DB stops responding after some time:

The OpenShift forums have been retired.
You can still read and search them, but for help, please post a question on Stack Overflow.

Hi there, i have a problem with my application's DB. i created a DIY container, pulled there an Apache tomcat installation from Git and deployed my project. then i created a cartage of MySQL on the same application container and copied there my tables. all works fine for about 8 hours and then MySQL DB stops responding. i have to: "rhc app restart" it in order to make it work again. does any one encounter this problem? Thanks for your help.

Best is to check for any error messages in the log files. It may shed some light on what led to failure. Here are instructions on how to check for logs:

https://www.openshift.com/faq/how-to-troubleshoot-application-issues-using-logs

After the restart of my application container the app is running fine, for now. I will update with logs if as soon as it stops working again. Thanks Sumana.

Well the problem is back, and here is the log from catalina.out:

==> diy-0.1/logs/catalina.out <== Dec 11, 2012 6:25:21 AM org.apache.catalina.core.StandardWrapperValve invoke SEVERE: Servlet.service() for servlet [jsp] in context with path [/coupons] thre w exception [org.hibernate.exception.JDBCConnectionException: could not execute query] with root cause java.io.EOFException: Can not read response from server. Expected to read 4 byte s, read 0 bytes before connection was unexpectedly lost. at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3041) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3491) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3480) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4021) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2677) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2134) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2300) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208) at org.hibernate.loader.Loader.getResultSet(Loader.java:1953) at org.hibernate.loader.Loader.doQuery(Loader.java:802) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274) at org.hibernate.loader.Loader.doList(Loader.java:2542) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276) at org.hibernate.loader.Loader.list(Loader.java:2271) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:459) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:365) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) at com.eadesign.hibernate.HibernateCouponsDAO.findallCoupons(HibernateCouponsDAO.java:248) at com.eadesign.Assist.couponsToXmlDoc(Assist.java:36) at org.apache.jsp.Client.ajaxserverside_jsp._jspService(ajaxserverside_jsp.java:93) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) at javax.servlet.http.HttpServlet.service(HttpServlet.java:722) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432) at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390) at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334) at javax.servlet.http.HttpServlet.service(HttpServlet.java:722) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:225) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)

at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1001) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:585) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) at java.lang.Thread.run(Thread.java:722)

does anyone knows of this problem and how to fix it? Thanks again.

One suggestion, outside of what @ramr suggested (have your driver ping mysql), is to change the timeout settings. You can do so by accessing your mysql gear, and modifying: ~/mysql-5.1/etc/my.cnf

Here's a thread that shows what/how to modify it: http://dba.stackexchange.com/questions/1558/how-long-is-too-long-for-mysql-connections-to-sleep

HTHs;
~Nam

Hi Nam Duong, Thanks for your help. I don't know how to set up my driver to ping mysql. I did configured the timeouts using those two commands:

SET GLOBAL interactive_timeout = 180
SET GLOBAL wait_timeout = 180

and restarted the server, hope this will help. will update soon.

Sry for the misunderstanding. See mysql docs for details on those variables: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_wait_timeout
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_interactive_timeout

You'll need to set it higher than the default 8 hrs.

HTHs;
~Nam

So if my application is not really that active and has a query or two a week, should i set it for a week length time? Thanks for the quick help.

@emil.adz, the connection will timeout on inactivity -- so you will probably need to use something like a connection pool
see: http://people.apache.org/~fhanik/jdbc-pool/jdbc-pool.html for more details.

Or alternatively, you could probably use the autoReconnect option for the MySQL JDBC connector.
See: http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html

HTH

Hi ramr, Thanks again for your help. i am not familiar with connection pool, i will read about it.

now for the autoReconnect option. this will be my next try in case the timeout configuration wont help me. so basically what i should do is in the hibernate.cfg.xml file i have to set:

jdbc:mysql://localhost:3306/test?autoReconnect=true

as my connection url, of course the host will be my Openshift machine, but this is the way it's done?

Well, just to update you guys, changing the interactive_timeout and the wait_timeout parameter to high values (1 day) didn't helped me, neither the autoReconnect option for MySQL JDBC. i have add the c3p0 connection pool mechanism to my project to see maybe it will solve my problem. will update again in few days.

Thanks to all of you for your help.

Hi everyone, Looks like the c3p0 connection pool solved my problem. I didn't experience this error since then. Many thanks to you all for your help.

Thank you for the confirmation!

If you have some spare cycles, it would be a great help to the community if you added some code samples. Thanks for your time in advanced.

Actually I have written 3 guides regarding my last experiences with OpenShift, that can be found in my blog:

http://blog-emildesign.rhcloud.com.

The posts are:

Guide: Introduction to Openshift hosting Apache Tomcat.

Guide: WordPress hosting on OpenShift.

Guide: c3p0 – JDBC3 Connection and Statement Pooling.

I will be glad if this helps someone other then my self.

Thank again for your help, Nam Duong.

@emil.adz, 8 hours is the default inactivity timeout in mysql.
You can run something like show variables like "%timeout" in mysql to verify
what the values for your env are (wait/interactive timeout values are in seconds).

Looks like your driver/client bits needs to be configured to send some
sorta heartbeat/keepalive message to keep the connection from idling.

HTH

Thank ramr,

this was the result from phpMyAdmin:

connect_timeout
10
delayed_insert_timeout
300
innodb_lock_wait_timeout
50
innodb_rollback_on_timeout
OFF
interactive_timeout
28800
net_read_timeout
30
net_write_timeout
60
slave_net_timeout
3600
table_lock_wait_timeout
50
wait_timeout
28800

My client is a simple web application that allows user to see stores based on their location. the data in the DB is those stores. the application of course sits on the same container. can i do something locally on the server to keep the connection from idling?
setting the interactive_timeout and the wait_timeout to low values will fix my problem? do you know of any articles regarding this matter?

Thanks in advance.