Running reports failing with 'Cannot get a connection, pool error Timeout waiting for idle object'

Issue: 

When running a report in the TIBCO JasperReports® Server, the server becomes unresponsive and the report fails with the following error recorded in the jasperserver.log:

2021-05-22T10:51:47,463 ERROR AsyncJasperPrintAccessor,pool-7-thread-84:321 - Error during report execution net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression for source text: ((javax.sql.DataSource)((javax.naming.Context) (new javax.naming.InitialContext()).lookup("java:comp/env")).lookup("jdbc/prodassets")).getConnection()
...
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object

After restarting the Tomcat server, we are then able to run the report successfully. 

Solution:

The error is an indication that the report was unable to get a connection from the db connection pool for connection to "prodassets" database. 

This could be associated to the max active connections being reached which may need to be increased. 

Steps:

1. Navigate to and edit the file:

..\jasperserver-pro\META-INF\context.xml

2. Increase the 'maxActive' value under Resource name "jdbc/prodassets". The default is:

maxActive="100"

maxActive is the maximum number of active connections that can be allocated from this pool at the same time. 

Note: Although there is a default value, there is no specific recommended value for this attribute as it depends on the requirement of each site. 
It should not be set too high since it can impact on system resources arising from too many active connections. A gradual increase and monitoring approach is suggested.  
  
3. For the change to take effect, restart the Tomcat server

There are other additional configurations that can be set to handle possible situations where connections are not being closed out automatically for it to return back into the connection pool. In terms of monitoring, you can log the abandoned connections to catalina.out by setting the attribute logAbandoned="true" in the JNDI connection. 

logAbandoned    
(boolean) Flag to log stack traces for application code which abandoned a Connection. Logging of abandoned Connections adds overhead for every Connection borrow because a stack trace has to be generated. The default value is false.

Should there be any abandoned connections, the root cause should be investigated further through log file analysis of the stack traces. Setting the maxActive higher will only prolong the issue from occurring and restarting Tomcat server will release all these abandoned connections until the issue occurs again. 

The following attributes can be set so that abandoned connections are automatically removed once its time limit expires. These additional attributes are :

removeAbandoned    
(boolean) Flag to remove abandoned connections if they exceed the removeAbandonedTimeout. If set to true a connection is considered abandoned and eligible for removal if it has been in use longer than the removeAbandonedTimeout Setting this to true can recover db connections from applications that fail to close a connection. See also logAbandoned. The default value is false.

removeAbandonedTimeout 
(int) Timeout in seconds before an abandoned(in use) connection can be removed. The default value is 60 (60 seconds). The value should be set to the longest running query your applications might have.

Please refer to the section "The Tomcat JDBC Connection Pool" of Apache Tomcat Reference for information on the above attributes. See Related Articles below for the direct link.

Related Articles:

https://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html

https://tomcat.apache.org/tomcat-8.5-doc/jdbc-pool.html