开发者

Getting org.hibernate.exception.JDBCConnectionException: could not execute query even through JNDI

开发者 https://www.devze.com 2023-02-17 16:04 出处：网络
I use Struts2+JSP+Tomcat6+Hibernate+Mysql as my J2EE application framework.Following to this topic, I\'ve had the problem of getting this error:
相关专题：connection-poolingjndi

I use Struts2+JSP+Tomcat6+Hibernate+Mysql as my J2EE application framework.Following to this topic, I've had the problem of getting this error:

org.hibernate.exception.JDBCConnectionException: could not execute query

It appears to be due to the fact that mysql closes it's connections after n hours. As people answered there I changed the hibernate config to get my db connections through JNDI.here is the course of actions which I've taken to do this:

my hibernate.cfg.xml:

<hibernate-configuration>
  <session-factory>

    <property name="connection.datasource">java:comp/env/jdbc/hposg</property>
    <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
    <property name="show_sql">true</property>
    <property name="current_session_context_class">thread</property>
    <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
    <property name="hbm2ddl.auto">update</property>
    <property name="hibernate.max_fetch_depth">3</property>

      <!-- Mapping files -->

  </session-factory>
</hibernate-configuration>

the context.xml file that I've put in META-INF derictory:

<Context>
  <Resource name="jdbc/hposg" 
            global="jdbc/hposg"
            auth="Container"
            type="javax.sql.DataSource" 
            username="root"
            password=""
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/hposg?autoReconnect=true&amp;characterEncoding=UTF-8"
            maxActive="8" 
            maxIdle="4"/>
</Context>

and these modifications in web.xml:

<resource-ref>
    <description>DB Connection</description>
    <res-ref-name>jdbc/hposg</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>

Strangely enough I still get the same error! here is the stacktrace:

'org.hibernate.exception.JDBCConnectionException: could not execute query
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:99)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.loader.Loader.doList(Loader.java:2297)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2172)
    at org.hibernate.loader.Loader.list(Loader.java:2167)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:448)
    at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
    at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1258)
    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
    at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:859)
    at com.hposg.domain.HPOSG.getPlayerByID(HPOSG.java:117)
    at com.hposg.login.UserSession.getPlayerById(UserSession.java:39)
    at com.hposg.controller.struts.PortfolioLoadAction.execute(PortfolioLoadAction.java:72)
    at sun.reflect.GeneratedMethodAccessor1032.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:616)
    at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:441)
    at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:280)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:243)
    at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:165)
    at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
    at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:252)
    at org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68)
    at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
    at com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:122)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
    at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:195)
    at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
    at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:195)
    at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
    at com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:179)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
    at org.apache.struts2.interceptor.MultiselectInterceptor.intercept(MultiselectInterceptor.java:75)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
    at org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:94)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
    at org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:235)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
    at com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:89)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
    at com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:130)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
    at org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:267)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
    at com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:126)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
    at com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:138)
    at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
    at com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:165)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
    at org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
    at com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:179)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
    at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:176)
    at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)
    at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:52)
    at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:488)
    at org.apache.struts2.dispatcher.FilterDispatcher.doFilter(FilterDispatcher.java:395)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    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:127)
    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(CoyoteAdapt开发者_JAVA百科er.java:298)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Thread.java:636)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 0 ms ago.
    at sun.reflect.GeneratedConstructorAccessor109.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:353)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2720)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1582)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1710)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:2436)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1402)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1556)
    at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1849)
    at org.hibernate.loader.Loader.doQuery(Loader.java:718)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
    at org.hibernate.loader.Loader.doList(Loader.java:2294)
    ... 74 more
Caused by: java.net.SocketException: Broken pipe
    at java.net.SocketOutputStream.socketWrite0(Native Method)
    at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109)
    at java.net.SocketOutputStream.write(SocketOutputStream.java:153)
    at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
    at java.io.BufferedOutputStream.write(BufferedOutputStream.java:126)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2703)
    ... 85 more
'

[EDIT] I made a few changes to the context.xml and the problem seems to be solved:

maxActive="-1" 
maxIdle="30"
validationQuery="SELECT 1"
testWhileIdle="true"

[EDIT_2] Despite the modifications I mentioned before, unfortunately I'm still getting the same error. Any Ideas?

We had the same issue in a production system with Tomcat6+Hibernate+MySQL (and Spring in our case) and the only reliable solution we found was to set the connection timeout to a large value in the MySQL configuration. I cannot remember the particulars but I believe there was an issue with the underlying commons-pool code when it verifies that pooled resource is valid.

Another possibility that we did not try was to use an alternate Connection Pooling mechanism other than DBCP. Next to try would be C3PO

Try adding tcpKeepAlive=true to the JDBC URL.

Your url is
url="jdbc:mysql://localhost:3306/hposg?autoReconnect=true&amp;characterEncoding=UTF-8".
But autoReconnect is not recommended (here). Instead, try increasing the timeout by adding this to my.cnf:

[mysqld]
interactive_timeout=864000
wait_timeout=864000

I faced same problem and got no proper solution. after that i follow below link for c3p0 and i got my solution. This link is very helpful for above problem

http://syntx.io/configuring-c3p0-connection-pooling-with-spring-and-hibernate/

0

上一篇:

:下一篇

更多 问答 相关资讯：

精彩评论

暂无评论...
登录 注册
Qedev.com
验证码 换一张
取 消

    关注公众号

    热门标签

    图文推荐

    Delphi - Custom drawing a message list

    Delphi - Custom drawing a message list
    C++ header-only include pattern

    C++ header-only include pattern
    IE7 Margin Collapses Into Padding

    IE7 Margin Collapses Into Padding
    in CoffeeScript, how can I use a variable as a key in a hash?

    in CoffeeScript, how can I use a variable as a key in a hash?
    Interactive visualization of a graph in python [closed]

    Interactive visualization of a graph in python [closed]
    How to customise PHP MYSQL tables?

    How to customise PHP MYSQL tables?
    High quality, simple random password generator

    High quality, simple random password generator
    Image Recognition ApI in android

    Image Recognition ApI in android

    开发者开发者网给大家分享系统运维,大数据运维,云计算,编程开发技巧,路由交换,运维和开发相关的资讯及技术文章，同时StackOverflow中文社区，知识经验交流分享。

    法律声明：本站内容均为网友上传，网站举办方负责审核和监督，如存在版权或非法内容，欢迎举报，我们将尽快予以删除。邮箱：devze@qq.com

    Copyright © 2018-2020 开发者. All rights reserved. Powered by 开发者ICP备案号: 京ICP备10032868号-9