JasperServer 6.3 Ad Hoc report timeout while running the MySQL query

Document ID : KB000008101
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

Timeout is seeing in some JasperReports 6.3 Ad Hoc reports.

The following message is displayed:

Ad_Hoc_Query_Timeout_message.png

 

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.

Environment:
CA Spectrum 10.2.x and JasperReports 6.3.0
Cause:

The MySQL query is exceeding the default Ad Hoc Query Timeout to start returning data. 

Resolution:

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. 

 

Ad_Hoc_Query_Timeout.png