Custom reports fail with connection errors when the query needs to be adjusted with Clarity

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Reports are failing with the below error messages. This is typically seen with custom reports in Advanced reporting. 

"net.sf.jasperreports.engine.JRException: Unable to get next record" and "java.sql.SQLRecoverableException: Closed Connection: next"

The Jaspersoft Catalina log shows an "org.apache.tomcat.jdbc.pool.ConnectionPool abandon" message.

Environment

Release: Any
Component: Clarity Jaspersoft

Cause

The errors are related to Tomcat understanding that the connection was abandoned. This can happen if the query is returning a massive amount of records in a time frame that is longer than what is configured in tomcat as the "abandoned timeout".

Resolution

Adjust the Query

It's best to optimize the query.

Parameter updates

Changes to parameters are not suggested, but are noted below for On Premise customers as an alternative:

1. Change the following parameters in the <JaspersoftTomcatInstallFolder>/webapps/reportservice/WEB-INF/applicationContext-proxyDataSourcePoolClarity.xml
   
   

   (<JasperSoft>/webapps/reportservice/WEB-INF)

   <bean id="dwhTntPoolProperties" class="org.apache.tomcat.jdbc.pool.PoolProperties" scope="prototype">

   ....

   <property name="removeAbandonedTimeout" value="12000"/>

2. Change from 900  to 12000 as shown below: 
   
   

   <bean id="ppmTntPoolProperties" class="org.apache.tomcat.jdbc.pool.PoolProperties" scope="prototype">

   ....

   <property name="removeAbandonedTimeout" value="12000"/>  

   Note: removeAbandonedTimeout - (int) Timeout in seconds before an abandoned(in use) connection can be removed.

   The default value is 900 (900 seconds). The value should be set to the longest-running query your reports might have.

3. Restart the Jaspersoft Tomcat service after making this change.