JasperServer 6.3 Ad Hoc report timeout while running the MySQL query

Products

• CA Spectrum
• DX NetOps

Issue/Introduction

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.

Environment

CA Spectrum 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.