JasperServer 6.3 Ad Hoc report timeout while running the MySQL query

book

Article ID: 8101

calendar_today

Updated On:

Products

CA Spectrum

Issue/Introduction

Timeout is seeing in some JasperReports 6.3 Ad Hoc reports.

The following message is displayed:

<Please see attached file for image>

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.

Cause

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

Environment

CA Spectrum JasperReports 6.3.0

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. 

 

<Please see attached file for image>

Ad_Hoc_Query_Timeout.png

Attachments

1558691235737000008101_sktwi1f5rjvs16gjm.png get_app
1558691232868000008101_sktwi1f5rjvs16gjl.png get_app