[javaariel]

Hi everybody:

I am newbie using hibernate framework. I have made a web application that uses hibernate to insert, update and delete entities in a mysql database. I generated the map xml files and the dao classes and objects entities with MyEclipse 6.0 that work very well, but I’m having a problem:

When I deployed the web application to the tomcat 6.0(using MyEclipse too) and start the tomcat service, everything work fine the inserts delete and retrieve work very well, but after one day when I’m going to use the aplication I get this error from the application: “Could not execute query” and of course nathing works then when I restart the tomcat 6.0 service everything work fine again.
I don’t know why is this happening to the aplication ??? Could you help me please to find how to fix this ?
I hope you can help me.
Greetings
Ariel

[Loyal Water]

Ariel,
Are any errors being logged under <workspace>/.metadata/.log file ?

[javaariel]

I’m not sure what you mean but the application is not working from the workspace, the application is deployed in the tomcat 6. Here I wrote the stacktrace.
I hope you can help me.
This is the stack trace

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)
at org.hibernate.loader.Loader.doList(Loader.java:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:308)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:153)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1106)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at cu.co.cenatav.hibernate.model.TblUsersDAO.findByProperty(TblUsersDAO.java:84)
at cu.co.cenatav.services.ServiceUserImpl.getUserByUsername(ServiceUserImpl.java:59)
at cu.co.cenatav.struts.action.LoginAction.execute(LoginAction.java:60)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
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:228)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:216)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:634)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:445)
at java.lang.Thread.run(Thread.java:619)
Caused by: com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.io.EOFException

STACKTRACE:

java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1956)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2368)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2867)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3255)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1293)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1428)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:308)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:153)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1106)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at cu.co.cenatav.hibernate.model.TblUsersDAO.findByProperty(TblUsersDAO.java:84)
at cu.co.cenatav.services.ServiceUserImpl.getUserByUsername(ServiceUserImpl.java:59)
at cu.co.cenatav.struts.action.LoginAction.execute(LoginAction.java:60)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
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:228)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:216)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:634)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:445)
at java.lang.Thread.run(Thread.java:619)

** END NESTED EXCEPTION **

Last packet sent to the server was 1703 ms ago.
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2579)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2867)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3255)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1293)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1428)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
… 28 more
could not execute query

[Loyal Water]

I came across this post on a mysql forum. This should help you sort your issue out :-

Did you read this?
http://dev.mysql.com/doc/connector/j/en/cj-faq.html#id2787172

MySQL closes connections after 8 hours of inactivity.
And even if you set the autoreconnect=true, you should be catching
SQLExceptions in your application and dealing with them.

But you also need to becareful for using autoReconnect function,
because autoReconnect will be depcreated in the future.

For detail, see the Mark Matthews comment of this.
http://bugs.mysql.com/bug.php?id=5020

[javaariel]

Hi support-nipun, thank you for your answer, I think that is my problem
I have seen the post http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-troubleshooting.html#qandaitem-24-4-5-3-4 It is explained that mysql conector/J closes conections after 8 hour of inactivity but I am newbie with hibernate and a myeclipse so I don’t know how to deal with this problem using hibernate, could you help me ? Could you explain me how do I reconnect with hibernate, sorry I think these are a very basics questions but I am learning.
This is the piece of code I use in my aplication with the dao class:

    public TblUsers getUserByUsername(String username) throws Exception {
        List l = daoUser.findByProperty("user", username);
        daoUser.getSession().close();
        if (l!=null)
            if (l.size()!=0)
                return (TblUsers) l.get(0);
        return null;
    }

I hope you can help me.
Greetings
Ariel

[javaariel]

Could Anyboody help me please ? How Can I deal with the problem I have explained before and hibernate ?
I hope somebody help me.
Greetings

[frankc01a]

Create a thread with a timer (say 1 hour in between) that does a harmless query against the database.

Brute force, I know, but until you hear something else….

[Loyal Water]

javaariel,
Im sorry I have never tried this before. You should cross post your query on the mysql forums as well. Maybe some user on our forum could help you out.

[Mahan Hooshmand Rad]

I have the same issue here and it’s really annoying.

[Mahan Hooshmand Rad]

it is related to the autoReconnect parameter of the mysql connector/J. if idle for more than 8 hours it will be disconnected. please post if any solution found

[Mahan Hooshmand Rad]

http://bugs.mysql.com/bug.php?id=1202

[Steve Prior]

Minor correction, it’s not 8 hours of inactivity – it’s 8 hours no matter what. You shouldn’t be keeping a database session open permanently, just not good practice. In my case I set up the database connection in Tomcat using a Filter:

public class MySessionFilter implements Filter {

public void init(FilterConfig cfg) throws ServletException {
}

public void doFilter(ServletRequest req, ServletResponse res, FilterChain chain) throws ServletException, IOException {
//set up hibernate session
//my actual code isn’t shown here because it is specific to some libraries I wrote
chain.doFilter(req, res);
//close session

}

public void destroy(){

}
}

and installed in your web.xml with:

<filter>
<filter-name>databaseFilter</filter-name>
<filter-class>MySessionFilter</filter-class>
</filter>

<filter-mapping>
<filter-name>databaseFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>

Another way would be to set up the database session and stick it in your webapp’s session bean, but I haven’t tried that yet.

Hope this helps
Steve

[Mahan Hooshmand Rad]

The problem can be solved using datasource.

[Author]

Posts