[coder694]

Hello,
before I go on further with debugging, may somebody can answer the question more quickly. I made a small test program as a proof-of-concept reading a list of data from a mysql5 database and use it in a flex datagrid.
I am using MyEclipse 6.5GA, and use the inbuild hibernate support (3.2, with annotations).
Everything works fine, but when the server work for 24hours (overnight), the app says ‘could not execute query’ instead of giving the data.
The Server is Windows Serve 2008, Tomcat 6.0.14 and MySql5.
As soon the app in Tomcat is restarted, everything works fine again.
Any Idea?
Thanks in advance

[Loyal Water]

It seems like some Tomcat configuration issue. This issue has been reported earlier as well:-
https://www.genuitec.com/forums/topic/help-quot-could-not-execute-query-quot/&start=0&postdays=0&postorder=asc&highlight=

[coder694]

Hello Nipun,
thanks for your reply, but in the other thread there isn’t a real solution. Even when I get my data only once, the error after a day occur. So it have to be something different. I am using Java 5, maybe thats a problem?
Any further ideas?

[coder694]

I have a stacktrace now (see below). And with the error-message

MESSAGE: The last packet successfully received from the server was40698 seconds ago.The last packet sent successfully to the server was 40698 seconds ago, which 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.

I get a lot of google-hits. I think now, the problem can be solved, but the Hibernate Tool can’t be used ‘out-of-the-box’ for me, I think. I will stay you informed on this thread.
May anybody have a quick solution right now?
Greetings
coder694

could not execute query
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:2223)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
    at org.hibernate.loader.Loader.list(Loader.java:2099)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
    at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
    at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
    at com.xxxx.service.MesswertServiceImpl.getPrototypData(MesswertServiceImpl.java:101)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at flex.messaging.services.remoting.adapters.JavaAdapter.invoke(JavaAdapter.java:406)
    at flex.messaging.services.RemotingService.serviceMessage(RemotingService.java:183)
    at flex.messaging.MessageBroker.routeMessageToService(MessageBroker.java:1417)
    at flex.messaging.endpoints.AbstractEndpoint.serviceMessage(AbstractEndpoint.java:878)
    at flex.messaging.endpoints.amf.MessageBrokerFilter.invoke(MessageBrokerFilter.java:121)
    at flex.messaging.endpoints.amf.LegacyFilter.invoke(LegacyFilter.java:158)
    at flex.messaging.endpoints.amf.SessionFilter.invoke(SessionFilter.java:49)
    at flex.messaging.endpoints.amf.BatchProcessFilter.invoke(BatchProcessFilter.java:67)
    at flex.messaging.endpoints.amf.SerializationFilter.invoke(SerializationFilter.java:146)
    at flex.messaging.endpoints.BaseHTTPEndpoint.service(BaseHTTPEndpoint.java:274)
    at flex.messaging.MessageBrokerServlet.service(MessageBrokerServlet.java:377)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    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:286)
    at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:857)
    at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:565)
    at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1509)
    at java.lang.Thread.run(Unknown Source)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error:


** BEGIN NESTED EXCEPTION ** 

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
MESSAGE: The last packet successfully received from the server was40698 seconds ago.The last packet sent successfully to the server was 40698 seconds ago, which  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.

STACKTRACE:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was40698 seconds ago.The last packet sent successfully to the server was 40698 seconds ago, which  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(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3246)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1917)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)
    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.hql.QueryLoader.list(QueryLoader.java:378)
    at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
    at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
    at com.xxxx.service.MesswertServiceImpl.getPrototypData(MesswertServiceImpl.java:101)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at flex.messaging.services.remoting.adapters.JavaAdapter.invoke(JavaAdapter.java:406)
    at flex.messaging.services.RemotingService.serviceMessage(RemotingService.java:183)
    at flex.messaging.MessageBroker.routeMessageToService(MessageBroker.java:1417)
    at flex.messaging.endpoints.AbstractEndpoint.serviceMessage(AbstractEndpoint.java:878)
    at flex.messaging.endpoints.amf.MessageBrokerFilter.invoke(MessageBrokerFilter.java:121)
    at flex.messaging.endpoints.amf.LegacyFilter.invoke(LegacyFilter.java:158)
    at flex.messaging.endpoints.amf.SessionFilter.invoke(SessionFilter.java:49)
    at flex.messaging.endpoints.amf.BatchProcessFilter.invoke(BatchProcessFilter.java:67)
    at flex.messaging.endpoints.amf.SerializationFilter.invoke(SerializationFilter.java:146)
    at flex.messaging.endpoints.BaseHTTPEndpoint.service(BaseHTTPEndpoint.java:274)
    at flex.messaging.MessageBrokerServlet.service(MessageBrokerServlet.java:377)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    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:286)
    at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:857)
    at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:565)
    at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1509)
    at java.lang.Thread.run(Unknown Source)
Caused by: java.net.SocketException: Software caused connection abort: socket write error
    at java.net.SocketOutputStream.socketWrite0(Native Method)
    at java.net.SocketOutputStream.socketWrite(Unknown Source)
    at java.net.SocketOutputStream.write(Unknown Source)
    at java.io.BufferedOutputStream.flushBuffer(Unknown Source)
    at java.io.BufferedOutputStream.flush(Unknown Source)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3227)
    ... 46 more


** END NESTED EXCEPTION **


    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.Util.getInstance(Util.java:381)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
    at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1098)
    at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4076)
    at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4042)
    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

[Riyad Kalla]

The solution to the problem is actually in your stack trace, more specifically, adding “autoReconnect=true” to your connection string in your hibernate configuration file. The issue is actually with the Connector/J driver disconnecting from the DB after a long time out, and when Hibernate goes to work again, the driver doesn’t issue a new reconnect.

I have no idea why autoReconnect=true isn’t set to try by default to be honest in the Connector/J driver… there’s probably a good reason for it, I just don’t know what it is 😉

[coder694]

Hello,
I will use autoReconnect=true now, but in the mysql-dokumentation it is marked as “not recommended”. I think for a proof-of-concept I will give that a try, but for the real-live I have to dig into deep.
Thanks all

[coder694]

I put autoReconnect in the hibernate.cfg.xml:

<property name=”connection.url”>jdbc:mysql://[myurl]:3306/[mydatabase]?autoReconnect=true</property>

and the error message is slightly different:

could not execute query
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:2223)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at com.xxxx.service.MesswertServiceImpl.getPrototypData(MesswertServiceImpl.java:102)
at sun.reflect.GeneratedMethodAccessor42.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at flex.messaging.services.remoting.adapters.JavaAdapter.invoke(JavaAdapter.java:406)
at flex.messaging.services.RemotingService.serviceMessage(RemotingService.java:183)
at flex.messaging.MessageBroker.routeMessageToService(MessageBroker.java:1417)
at flex.messaging.endpoints.AbstractEndpoint.serviceMessage(AbstractEndpoint.java:878)
at flex.messaging.endpoints.amf.MessageBrokerFilter.invoke(MessageBrokerFilter.java:121)
at flex.messaging.endpoints.amf.LegacyFilter.invoke(LegacyFilter.java:158)
at flex.messaging.endpoints.amf.SessionFilter.invoke(SessionFilter.java:49)
at flex.messaging.endpoints.amf.BatchProcessFilter.invoke(BatchProcessFilter.java:67)
at flex.messaging.endpoints.amf.SerializationFilter.invoke(SerializationFilter.java:146)
at flex.messaging.endpoints.BaseHTTPEndpoint.service(BaseHTTPEndpoint.java:274)
at flex.messaging.MessageBrokerServlet.service(MessageBrokerServlet.java:377)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
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:286)
at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:857)
at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:565)
at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1509)
at java.lang.Thread.run(Unknown Source)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was36635 seconds ago.The last packet sent successfully to the server was 36635 seconds ago, which 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(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3246)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1917)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)
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)
… 35 more
Caused by: java.net.SocketException: Software caused connection abort: socket write error
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(Unknown Source)
at java.net.SocketOutputStream.write(Unknown Source)
at java.io.BufferedOutputStream.flushBuffer(Unknown Source)
at java.io.BufferedOutputStream.flush(Unknown Source)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3227)
… 45 more

I further read, that autoConnect is depreceated and it is recommented to handle
reconnection in your application. But I though, hibernate does it all for me, because
it seems to be a mysql-related problem.

Any idea?

Greeting coder694

[Riyad Kalla]

coder694,

Unfortunately not, you could try checking the Hibernate forums I suppose for the “correct way” of doing this from hibernate I suppose. I have developed long-running apps and just used autoReconnect and haven’t had any problems before.

[rmcvay]

This is just a WAG but if you’re using connection pools make sure you allow the pool to drop to zero connections and make sure the idle connection timeout is shorter than the database connection idle timeout.

[Author]

Posts