Problem with my jbpm database

[nicolemans72]

Hello!

I have a problem since a long time with my jbpm database.

I work on a program which use Apache Tomcat 5.0 and Jbpm 3.1.2 and a mysql database.

The workflow well run but when when the server is running all the night, we have strange problems in the morning...

The workflow have some bugs and we solve them by re-deploy the process definition (sometimes, we have just to redeploy the processdefinition on an another application on the same server and it run!!!!)

We take the log on the server :

2007-04-03 09:48:21 StandardWrapperValve[Faces Servlet]: Servlet.service() for servlet Faces Servlet threw exception
javax.faces.el.EvaluationException: Exception while invoking expression #{transfertBean.selectTransfert}
 at org.apache.myfaces.el.MethodBindingImpl.invoke(MethodBindingImpl.java:153)
 at javax.faces.component.UICommand.broadcast(UICommand.java:86)
 at javax.faces.component.UIData.broadcast(UIData.java:513)
 at org.ajax4jsf.framework.ajax.AjaxViewRoot.processEvents(AjaxViewRoot.java:281)
 at org.ajax4jsf.framework.ajax.AjaxViewRoot.broadcastEvents(AjaxViewRoot.java:257)
 at org.ajax4jsf.framework.ajax.AjaxViewRoot.processApplication(AjaxViewRoot.java:412)
 at org.apache.myfaces.lifecycle.LifecycleImpl.invokeApplication(LifecycleImpl.java:343)
 at org.apache.myfaces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:86)
 at javax.faces.webapp.FacesServlet.service(FacesServlet.java:137)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
 at org.apache.myfaces.webapp.filter.ExtensionsFilter.doFilter(ExtensionsFilter.java:144)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
 at org.ajax4jsf.framework.ajax.xmlfilter.BaseFilter.doFilter(BaseFilter.java:232)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
 at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
 at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
 at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
 at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
 at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
 at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
 at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
 at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
 at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
 at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
 at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
 at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
 at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
 at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
 at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
 at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
 at java.lang.Thread.run(Thread.java:595)
Caused by: org.jbpm.JbpmException: couldn't get pooled task instances list for actors '[ADMIN, ADMIN]'
 at org.jbpm.db.TaskMgmtSession.findPooledTaskInstances(TaskMgmtSession.java:122)
 at bsa.common.jbpm.view.ProcessManageBean.findTask(ProcessManageBean.java:480)
 at bsa.common.jbpm.view.ProcessManageBean.isProcess(ProcessManageBean.java:529)
 at bsa.transfert.view.TransfertBean.checkPageTransfertBooleanProcess(TransfertBean.java:937)
 at bsa.transfert.view.TransfertBean.selectTransfert(TransfertBean.java:646)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:585)
 at org.apache.myfaces.el.MethodBindingImpl.invoke(MethodBindingImpl.java:129)
 ... 38 more
Caused by: 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 org.jbpm.db.TaskMgmtSession.findPooledTaskInstances(TaskMgmtSession.java:118)
 ... 47 more
Caused by: 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:2692)
 at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2621)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1552)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:2994)
 at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:936)
 at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1030)
 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 org.jbpm.db.TaskMgmtSession.findPooledTaskInstances(TaskMgmtSession.java:118)
 at bsa.common.jbpm.view.ProcessManageBean.findTask(ProcessManageBean.java:480)
 at bsa.common.jbpm.view.ProcessManageBean.isProcess(ProcessManageBean.java:529)
 at bsa.transfert.view.TransfertBean.checkPageTransfertBooleanProcess(TransfertBean.java:937)
 at bsa.transfert.view.TransfertBean.selectTransfert(TransfertBean.java:646)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:585)
 at org.apache.myfaces.el.MethodBindingImpl.invoke(MethodBindingImpl.java:129)
 at javax.faces.component.UICommand.broadcast(UICommand.java:86)
 at javax.faces.component.UIData.broadcast(UIData.java:513)
 at org.ajax4jsf.framework.ajax.AjaxViewRoot.processEvents(AjaxViewRoot.java:281)
 at org.ajax4jsf.framework.ajax.AjaxViewRoot.broadcastEvents(AjaxViewRoot.java:257)
 at org.ajax4jsf.framework.ajax.AjaxViewRoot.processApplication(AjaxViewRoot.java:412)
 at org.apache.myfaces.lifecycle.LifecycleImpl.invokeApplication(LifecycleImpl.java:343)
 at org.apache.myfaces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:86)
 at javax.faces.webapp.FacesServlet.service(FacesServlet.java:137)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
 at org.apache.myfaces.webapp.filter.ExtensionsFilter.doFilter(ExtensionsFilter.java:144)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
 at org.ajax4jsf.framework.ajax.xmlfilter.BaseFilter.doFilter(BaseFilter.java:232)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
 at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
 at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
 at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
 at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
 at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
 at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
 at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
 at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
 at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
 at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
 at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
 at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
 at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
 at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
 at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
 at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
 at java.lang.Thread.run(Thread.java:595)


** END NESTED EXCEPTION **

[kukeltje]

I wonder what you might have found if you searched the hibernate forum, but the answer is: configure your datasource to check the validity of the connnection before using it.

[mputz]

Hint: http://wiki.jboss.org/wiki/Wiki.jsp?page=WhatHappensToJBossIfIRestartTheDatabase

Martin

[nicolemans72]

I have just posted on the Hibernate forum to

The solution of mputz is for a JBoss AS but I work on a Tomcat server.

Is there a similar solution on Tomcat?

[mputz]

in that case, just look at the tomcat docu ;-)

"validationQuery - SQL query that can be used by the pool to validate connections before they are returned to the application. If specified, this query MUST be an SQL SELECT statement that returns at least one row."

Martin

[dleerob]

I have the a similair problem, where my MySQL connection seems to die with a socket connection error. I am going to try the following:

In hibernate.cfg.xml file, use:

<property name="hibernate.connection.datasource">java:comp/env/jdbc/JbpmDS</property>

instead of the direct jdbc connection.

In my webapps context.xml which should be found in tomcat\conf\Catalina\localhost\[myappname].xml file, use something like:

<Resource name="jdbc/JbpmDS" auth="Container" type="javax.sql.DataSource"/>
 <ResourceParams name="jdbc/JbpmDS">
 <parameter>
 <name>factory</name>
 <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
 </parameter>
 <parameter>
 <name>maxActive</name>
 <value>100</value>
 </parameter>
 <parameter>
 <name>maxIdle</name>
 <value>30</value>
 </parameter>
 <parameter>
 <name>maxWait</name>
 <value>10000</value>
 </parameter>
 <parameter>
 <name>username</name>
 <value>jbossjbpm</value>
 </parameter>
 <parameter>
 <name>password</name>
 <value>jbossjbpm</value>
 </parameter>
 <parameter>
 <name>driverClassName</name>
 <value>com.mysql.jdbc.Driver</value>
 </parameter>
 <parameter>
 <name>defaultAutoCommit</name>
 <value>true</value>
 </parameter>
 <parameter>
 <name>url</name>
 <value>jdbc:mysql://localhost/jbpm321</value>
 </parameter>
 <parameter>
 <name>removeAbandoned</name>
 <value>true</value>
 </parameter>
 <parameter>
 <name>removeAbandonedTimeout</name>
 <value>60</value>
 </parameter>
 <parameter>
 <name>logAbandoned</name>
 <value>true</value>
 </parameter>
 </ResourceParams>

I'm hoping the maxIdle parameter will do the trick.
You could lower the maxIdle value if need be.

Or, you could look at using hibernate.c3p0.timeout property in hibernate.cfg.xml, instead of using the above method, but I think you may then need to go and get some extra hibernate jar files or something.

[dleerob]

By the way, here is a link that should help you sort out your problem:
http://michaelstudman.com/fullfathomfive/articles/2004/06/07/mysql-dropping-connections-and-hibernate

Here is also more info the the hibernate.C3p0 config: http://www.hibernate.org/214.html

If my tomcat datasource with maxIdle parameter mentioned in my above post doesn't work, I will then use the methods mentioned in the above links.

[dleerob]

A quick update: I no longer seem to have dropped connections after creating the datasource in tomcat, and setting the maxIdle parameter.

[kukeltje]

great... as with many 'problems' not jBPM related ;-)