Timeout is seen in some JasperReports 6.3 Ad Hoc reports.
The following message is displayed:
Enabled the MySQL query logging in JasperReports.
1. Edit the C:\Program Files\CA\SC\CA Business Intelligence\apache-tomcat\webapps\jasperserver-pro\WEB-INF\log4j.properties file.
2. Locate the following section:
### JasperReports loggers
#log4j.logger.net.sf.jasperreports.engine.query.JRJdbcQueryExecuter=debug
3. Uncomment the debug line as follows:
### JasperReports loggers
log4j.logger.net.sf.jasperreports.engine.query.JRJdbcQueryExecuter=debug
4. Stop and then start all JasperReports services
C:\Users\spectrum\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\CA\CA Business Intelligence\Start and stop servers
- Stop CABI Services
- Start CABI Services
5. Reproduce the Ad Hoc issue.
6. Review the C:\Program Files\CA\SC\CA Business Intelligence\apache-tomcat\webapps\jasperserver-pro\WEB-INF\logs\jasperserver.log file.
2017-09-12 17:54:52,034 DEBUG JRJdbcQueryExecuter,Thread-45:362 - SQL query string: select `v_dim_event_creator`.`creator_name` as `v_dim_event_creator_creator_name`,
`v_dim_model`.`model_name` as `v_dim_model_model_name`
from `v_fact_event`
left outer join `v_dim_model` on (`v_fact_event`.`model_key` = `v_dim_model`.`model_key`)
right outer join `v_dim_event_creator` on (`v_dim_event_creator`.`creator_id` = `v_fact_event`.`creator_id`)
group by `v_dim_model`.`model_name`, `v_dim_event_creator`.`creator_name`
order by `v_dim_model_model_name`, `v_dim_event_creator_creator_name`
limit 1000
2017-09-12 18:00:52,277 ERROR AdhocAjaxController,http-apr-8080-exec-5:954 - ad hoc controller exception: Ocorreu um erro durante a execução da solicitação anterior.
java.lang.RuntimeException: exception getting dataset from cache
at com.jaspersoft.commons.semantic.dsimpl.memory.MemoryDataSet.getWorkingDataSet(MemoryDataSet.java:147)
at com.jaspersoft.commons.semantic.dsimpl.memory.MemoryDataSet.getWorkingDataSet(MemoryDataSet.java:75)
Caused by: com.jaspersoft.commons.dataset.DataSetException: Exception calling JRDataSource.next() for query select `v_dim_event_creator`.`creator_name` as `v_dim_event_creator_creator_name`,
`v_dim_model`.`model_name` as `v_dim_model_model_name`
from `v_fact_event`
left outer join `v_dim_model` on (`v_fact_event`.`model_key` = `v_dim_model`.`model_key`)
right outer join `v_dim_event_creator` on (`v_dim_event_creator`.`creator_id` = `v_fact_event`.`creator_id`)
group by `v_dim_model`.`model_name`, `v_dim_event_creator`.`creator_name`
order by `v_dim_model_model_name`, `v_dim_event_creator_creator_name`
limit 1000
Caused by: com.jaspersoft.commons.util.QueryExecutionTimeoutException: jsexception.QueryExecutionTimeoutException
Arguments: 360,
The QueryExecutionTimeoutException is occurring after 360 seconds.
CA Spectrum JasperReports 6.3.0
The MySQL query is exceeding the default Ad Hoc Query Timeout to start returning data.
You have to increase the Ad Hoc Query Timeout.
Login at JasperServer, click on Manage, select "Server Settings", click on "Ad Hoc Settings" option in the left hand side pane.
And then change the "Ad Hoc Query Timeout (seconds) value. It is the maximum time for an Ad Hoc report's query to start returning data.