CABI is reporting errors for some reports

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

Some CABI based Unified dashboards are reporting "An unexplained error has occurred and the operation failed."

CABI log contains an error similar to the one below:

Error Message

net.sf.jasperreports.engine.JRException: Error executing SQL statement for: QOS_Trend_TimeSeries.

Error Trace

net.sf.jasperreports.engine.JRException: Error executing SQL statement for: QOS_Trend_TimeSeries. at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:344) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:169) at com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.createDatasource(JSControlledJdbcQueryExecuter.java:114) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1129) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:696) at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:437) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:548) at net.sf.jasperreports.engine.fill.BaseReportFiller.fill(BaseReportFiller.java:396) at net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:732) at net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:59) at net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:221) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: java.sql.BatchUpdateException: Prepared statement needs to be re-prepared at org.mariadb.jdbc.MariaDbServerPreparedStatement.execute(MariaDbServerPreparedStatement.java:374) at org.mariadb.jdbc.MariaDbServerPreparedStatement.executeQuery(MariaDbServerPreparedStatement.java:341) at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:310) ... 13 more Caused by: java.sql.SQLException: Prepared statement needs to be re-prepared at org.mariadb.jdbc.internal.util.ExceptionMapper.get(ExceptionMapper.java:136) at org.mariadb.jdbc.internal.util.ExceptionMapper.throwException(ExceptionMapper.java:69) at org.mariadb.jdbc.MariaDbServerPreparedStatement.executeQueryEpilog(MariaDbServerPreparedStatement.java:336) at org.mariadb.jdbc.MariaDbServerPreparedStatement.executeInternal(MariaDbServerPreparedStatement.java:291) at org.mariadb.jdbc.MariaDbServerPreparedStatement.execute(MariaDbServerPreparedStatement.java:369) ... 17 more Caused by: org.mariadb.jdbc.internal.util.dao.QueryException: Prepared statement needs to be re-prepared at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:479) at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executePreparedQuery(AbstractQueryProtocol.java:592) at org.mariadb.jdbc.MariaDbServerPreparedStatement.executeInternal(MariaDbServerPreparedStatement.java:279) ... 18 more

Note highlighted section in the stack trace above

Environment

MySQL Database

Cause

This is caused by exceeding the configured table_definition_cache value in MySQL.  This can occur in environments with a large number of RN, HN, DN and BN tables.  The default computed value is 1400 which may not be high enough in some environments.

Resolution

Work with a DBA to update the MySQL my.cnf configuration to increase the table_definition_cache value to 2800.

Additional Information

MySQL Documentation on table_definition_cache