Re: JPA/Hibernate persistence and MySQL connection timeout

Mon, 29 Dec 2008 05:42:03 -0800 

Kees,

Can you point me to a discussion of how to do that?

Thanks,
Ken

On Dec 29, 2008, at 3:42 AM, Kees de Kooter wrote:


Why don't u use the existing datasources for JPA (instead of setting
the hibernate.connection properties)?

Kees


On Mon, Dec 29, 2008 at 00:50, Ken Bowen <kbo...@als.com> wrote:

Hi All,
I'm using: Tomcat 6.0.18; Mysql 5.0.51a; Java 1.5; Hibernate 3.2; (no 
spring)

MyApp utilizes five (5) distinct mysql catalogs (databases).
Originally all 5 were accessed using JDBC/JNDI with Resources that all look 
like this
(in webapps/MyApp/META-INF):

<Resource name="jdbc/CATALOGNAME" auth="Container"
type="javax.sql.DataSource"
          maxActive="100" maxIdle="30" maxWait="10000"
          username="XXXXX" password="XXXXX"
          driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost:3306/CATALOGNAME"
          validationQuery="select 1"
/>
Everything worked fine, including the validationQuery to deal with MySQL 
closing connections overnight.

Recently I replaced the JDBC access for 3 of the catalogs by use of
JPA/Hibernate persistence,
while leaving the other two catalogs as originally implemented (they consist 
of large tables
containing daily price information; hence not very object- oriented.) The 
persistence.xml looks
like this:

<persistence-unit name="ctdbPU" transaction-type="RESOURCE_LOCAL">
  <provider>org.hibernate.ejb.HibernatePersistence</provider>

  ..... lots of class entries like:
  <class>com.connectedtrading.database.users.AbstractLineitem</class>
  <class>com.connectedtrading.database.users.Lineitem</class>

  <properties>
    <property name="hibernate.connection.driver_class"
value="com.mysql.jdbc.Driver" />
    <property name="hibernate.connection.url"
value="jdbc:mysql://localhost:3306/CATALOGNAME" />
    <property name="hibernate.connection.username" value="XXXXX" />
    <property name="hibernate.connection.password" value="XXXXX" />
  </properties>
</persistence-unit>

[Interestingly enough, only one of the 3 CATALOGNAMEs appears.]
Note that I left all 5 of the Resource elements in place in context.xml.
Once again, everything works well, EXCEPT that now when I leave the system 
running overnight and
no accesses occur (it's only in dev mode), in the morning I now get the 
exception shown at the
end of this email. It certainly looks like the "mysql" disconnect behavior. As with the JDBC case, repeating the access causes everything to start 
working correctly.
The query being submitted is

      org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:75)
com .connectedtrading .database.users.UserDAO.findByProperty(UserDAO.java:180)
com .connectedtrading.database.users.UserDAO.findByLogin(UserDAO.java: 203) 

which is a hibernate query supporting user login.
If anyone on the list has had experience with this issue under JPA/ Hibernate 
persistence,
I hope you can give me some pointers to dealing with it.

Thanks much in advance,
Ken Bowen

----- The Exception-----

HTTP Status 500 -

type Exception report

message
description The server encountered an internal error () that prevented it 
from fulfilling this request.

exception
javax.servlet.ServletException: javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: could not execute query
org .apache .struts .action.RequestProcessor.processException(RequestProcessor.java:523)
org .apache .struts .action.RequestProcessor.processActionPerform(RequestProcessor.java: 421)
org .apache .struts.action.RequestProcessor.process(RequestProcessor.java:224)
org.apache.struts.action.ActionServlet.process(ActionServlet.java: 1194) org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432) 
      javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
root cause

javax.persistence.PersistenceException:
org.hibernate.exception.JDBCConnectionException: could not execute query
org .hibernate .ejb .AbstractEntityManagerImpl .throwPersistenceException(AbstractEntityManagerImpl.java:630) 
      org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:75)
com .connectedtrading .database.users.UserDAO.findByProperty(UserDAO.java:180)
com .connectedtrading.database.users.UserDAO.findByLogin(UserDAO.java: 203)
com .connectedtrading .user_manage.LoginProcessor.locateUser(LoginProcessor.java:54)
com .connectedtrading .user_manage.LoginProcessor.checkLogin(LoginProcessor.java:35)
com .connectedtrading .actions.ProcessLoginAction.execute(ProcessLoginAction.java:33)
org .apache .struts .action.RequestProcessor.processActionPerform(RequestProcessor.java: 419)
org .apache .struts.action.RequestProcessor.process(RequestProcessor.java:224)
org.apache.struts.action.ActionServlet.process(ActionServlet.java: 1194) org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432) 
      javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
root cause
org.hibernate.exception.JDBCConnectionException: could not execute query
org .hibernate .exception.SQLStateConverter.convert(SQLStateConverter.java:74)
org .hibernate .exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) 
      org.hibernate.loader.Loader.doList(Loader.java:2216)
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java: 2104) 
      org.hibernate.loader.Loader.list(Loader.java:2099)
      org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
org .hibernate .hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
org .hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java: 172) 
      org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
      org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
      org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:66)
com .connectedtrading .database.users.UserDAO.findByProperty(UserDAO.java:180)
com .connectedtrading.database.users.UserDAO.findByLogin(UserDAO.java: 203)
com .connectedtrading .user_manage.LoginProcessor.locateUser(LoginProcessor.java:54)
com .connectedtrading .user_manage.LoginProcessor.checkLogin(LoginProcessor.java:35)
com .connectedtrading .actions.ProcessLoginAction.execute(ProcessLoginAction.java:33)
org .apache .struts .action.RequestProcessor.processActionPerform(RequestProcessor.java: 419)
org .apache .struts.action.RequestProcessor.process(RequestProcessor.java:224)
org.apache.struts.action.ActionServlet.process(ActionServlet.java: 1194) org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432) 
      javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
root cause
com.mysql.jdbc.CommunicationsException: Communications link failure due to 
underlying exception:

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: Broken pipe

STACKTRACE:

java.net.SocketException: Broken pipe
      at java.net.SocketOutputStream.socketWrite0(Native Method)
      at
java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
at java.net.SocketOutputStream.write(SocketOutputStream.java: 136) 
      at
java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java: 65) at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123) 
      at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2739)
      at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2650)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1581)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
      at com.mysql.jdbc.Connection.execSQL(Connection.java:3026)
      at
com .mysql .jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1137) 
      at
com .mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java: 1231) 
      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:2213)
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 org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:66)
      at
com .connectedtrading .database.users.UserDAO.findByProperty(UserDAO.java:180) 
      at
com .connectedtrading.database.users.UserDAO.findByLogin(UserDAO.java: 203) 
      at
com .connectedtrading .user_manage.LoginProcessor.locateUser(LoginProcessor.java:54) 
      at
com .connectedtrading .user_manage.LoginProcessor.checkLogin(LoginProcessor.java:35) 
      at
com .connectedtrading .actions.ProcessLoginAction.execute(ProcessLoginAction.java:33) 
      at
org .apache .struts .action.RequestProcessor.processActionPerform(RequestProcessor.java: 419) 
      at
org .apache .struts.action.RequestProcessor.process(RequestProcessor.java:224) 
      at
org.apache.struts.action.ActionServlet.process(ActionServlet.java: 1194) 
      at
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
      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.Http11Processor.process(Http11Processor.java: 845) 
      at
org.apache.coyote.http11.Http11Protocol $Http11ConnectionHandler.process(Http11Protocol.java:583) 
      at
org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java: 447) 
      at java.lang.Thread.run(Thread.java:595)


** END NESTED EXCEPTION **



Last packet sent to the server was 1 ms ago.
      com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2757)
      com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2650)
      com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1581)
      com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
      com.mysql.jdbc.Connection.execSQL(Connection.java:3026)
com .mysql .jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1137)
com .mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java: 1231)
org .hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java: 186) 
      org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
      org.hibernate.loader.Loader.doQuery(Loader.java:674)
org .hibernate .loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java: 236) 
      org.hibernate.loader.Loader.doList(Loader.java:2213)
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java: 2104) 
      org.hibernate.loader.Loader.list(Loader.java:2099)
      org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
org .hibernate .hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
org .hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java: 172) 
      org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
      org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
      org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:66)
com .connectedtrading .database.users.UserDAO.findByProperty(UserDAO.java:180)
com .connectedtrading.database.users.UserDAO.findByLogin(UserDAO.java: 203)
com .connectedtrading .user_manage.LoginProcessor.locateUser(LoginProcessor.java:54)
com .connectedtrading .user_manage.LoginProcessor.checkLogin(LoginProcessor.java:35)
com .connectedtrading .actions.ProcessLoginAction.execute(ProcessLoginAction.java:33)
org .apache .struts .action.RequestProcessor.processActionPerform(RequestProcessor.java: 419)
org .apache .struts.action.RequestProcessor.process(RequestProcessor.java:224)
org.apache.struts.action.ActionServlet.process(ActionServlet.java: 1194) org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432) 
      javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
note The full stack trace of the root cause is available in the Apache 
Tomcat/6.0.18 logs.


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org




---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org




---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Reply via email to