Jasper Server 5.6 - All scheduled reports are failing after database restart

[jsasidharan]

Hi,

I am using jasperserver 5.6. Many scheduled reports are pointing to oracle JDBC datasource. (It is JDBC not JNDI datasource)

We had an oracle instance restart. But after that alll scheduled reports pointing to that oracle instance started failing with the below exception. We restarted the jasperserver tomcat  instance and everything came back to normal. Oracle instance went down only for 5 mins, but reports kept on failing for 5-6 hours till the restart.

 

Caused by: java.sql.SQLException: No more data to read from socket

 

ERROR EngineServiceImpl,quartzScheduler_Worker-4:1716 - Error while closing data source connection

com.jaspersoft.jasperserver.api.JSExceptionWrapper:

java.sql.SQLException: Already closed.

        at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:77)

        at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:180)

        at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.BaseJdbcDataSource.closeConnection(BaseJdbcDataSource.java:57)

 

Anyone has any idea why all reports failed? 

Thanks in advance.

[jsasidharan]

That is a great info!  I will try using JNDI instead of JDBC.

I was under the impression that, JDBC connection will be created only when the scheduled report starts running.

But here the connections are already created and added to the DBCP pool for all the reports. And it started failing because the already created connections are failed to refresh after the oracle restart.

And here, since I am not using JNDI, for each report a new JDBC connection object will be created and added to the pool, and will be closed after the report generation. If I use JNDI, connections will not be closed but will be released to the pool for the new reports, along with the frequent validations and refresh.

Please correct me if I said anything wrong. Thank You.

 

 

[Friendly User]

Hello,

sorry, I messed up the post by accident removing my previous answer and making your followup question a comment. Apologies for that.

What happened here was that the scheduler was trying to re-use the connection which wasn't closed properly. It wasn't marked as a closed connection and the scheduler kept assuming it is open. When you use JDBC, a connection is also created and added to DBCP pool. JRS tries to re-use connections when possible.

To avoid such a problem it is recommended to use JNDI data sources and specify the connection pool settings. Specifically, you need

validationQuery="select 1 from dual"
testOnBorrow="true"

When you will use JNDI data source with these settings, the JRS will re-use the connections from that pool specifically and when trying to use the connection, it will first issue a validation query to the server to see if the connection is alive or it should be abandoned and returned back to the pool.

More about properties here:

https://commons.apache.org/proper/commons-dbcp/configuration.html

And yes, you are completely right in your follow-up post. Using JNDI is more fail-safe.

Hope this helps and sorry again for messing up the post.