INFO | jvm 1 | 2020/01/06 11:48:59 | 2020-01-06 11:48:59.012 ERROR 5868 --- [worker_thread_1] c.c.n.o.r.impl.SQLDataRepositoryClient : query timeout happened for agents_all_view - SELECT InSpeed,IfType,Description,PersistentIfIndex,Enabled,RouterAddress,Name,AgentType,LastData,OutSpeed,IfIndex,UpdatedOn,ID,HarvesterAddress FROM agents_all_view where ID NOT IN (SELECT DISTINCT SUBSTRING_INDEX(commandArguments, '=', -1) FROM database_servers_scheduler WHERE command LIKE 'DELETE INTERFACE' AND retry>0) INFO | jvm 1 | 2020/01/06 11:48:59 | INFO | jvm 1 | 2020/01/06 11:48:59 | org.springframework.dao.QueryTimeoutException: PreparedStatementCallback; SQL [SELECT InSpeed,IfType,Description,PersistentIfIndex,Enabled,RouterAddress,Name,AgentType,LastData,OutSpeed,IfIndex,UpdatedOn,ID,HarvesterAddress FROM agents_all_view where ID NOT IN (SELECT DISTINCT SUBSTRING_INDEX(commandArguments, '=', -1) FROM database_servers_scheduler WHERE command LIKE 'DELETE INTERFACE' AND retry>0) Statement cancelled due to timeout or client request; nested exception is com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request INFO | jvm 1 | 2020/01/06 11:48:59 | at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:120) INFO | jvm 1 | 2020/01/06 11:48:59 | at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) INFO | jvm 1 | 2020/01/06 11:48:59 | at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) INFO | jvm 1 | 2020/01/06 11:48:59 | at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) INFO | jvm 1 | 2020/01/06 11:48:59 | at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1402) INFO | jvm 1 | 2020/01/06 11:48:59 | at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:620) INFO | jvm 1 | 2020/01/06 11:48:59 | at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:657) INFO | jvm 1 | 2020/01/06 11:48:59 | at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:688) INFO | jvm 1 | 2020/01/06 11:48:59 | at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:741) INFO | jvm 1 | 2020/01/06 11:48:59 | at com.ca.nfa.odata.repository.impl.SQLDataRepositoryClient.performQuery(SQLDataRepositoryClient.java:278) INFO | jvm 1 | 2020/01/06 11:48:59 | at com.ca.nfa.odata.repository.impl.SQLDataRepositoryClient.getEntities(SQLDataRepositoryClient.java:113) INFO | jvm 1 | 2020/01/06 11:48:59 | at com.ca.nfa.odata.repository.impl.DataRepository.getEntitySet(DataRepository.java:145) INFO | jvm 1 | 2020/01/06 11:48:59 | at com.ca.nfa.odata.repository.impl.ODataStore.readEntitySet(ODataStore.java:77) INFO | jvm 1 | 2020/01/06 11:48:59 | at com.ca.nfa.odata.processor.ODataActionProcessor.processActionEntityCollection(ODataActionProcessor.java:303) INFO | jvm 1 | 2020/01/06 11:48:59 | at org.apache.olingo.server.core.ODataDispatcher.handleActionDispatching(ODataDispatcher.java:236) INFO | jvm 1 | 2020/01/06 11:48:59 | at org.apache.olingo.server.core.ODataDispatcher.handleResourceDispatching(ODataDispatcher.java:141) INFO | jvm 1 | 2020/01/06 11:48:59 | at org.apache.olingo.server.core.ODataDispatcher.dispatch(ODataDispatcher.java:117) INFO | jvm 1 | 2020/01/06 11:48:59 | at org.apache.olingo.server.core.ODataHandlerImpl.processInternal(ODataHandlerImpl.java:164) INFO | jvm 1 | 2020/01/06 11:48:59 | at org.apache.olingo.server.core.ODataHandlerImpl.process(ODataHandlerImpl.java:85) INFO | jvm 1 | 2020/01/06 11:48:59 | at org.apache.olingo.server.core.ODataHttpHandlerImpl.process(ODataHttpHandlerImpl.java:74) INFO | jvm 1 | 2020/01/06 11:48:59 | at org.apache.olingo.server.core.ODataHttpHandlerImpl.process(ODataHttpHandlerImpl.java:88) INFO | jvm 1 | 2020/01/06 11:48:59 | at com.ca.nfa.odata.ODataService.process(ODataService.java:76) INFO | jvm 1 | 2020/01/06 11:48:59 | at com.ca.nfa.odata.servlet.request.util.AsyncRequestTask.run(AsyncRequestTask.java:46) INFO | jvm 1 | 2020/01/06 11:48:59 | at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) INFO | jvm 1 | 2020/01/06 11:48:59 | at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) INFO | jvm 1 | 2020/01/06 11:48:59 | at java.lang.Thread.run(Thread.java:748) INFO | jvm 1 | 2020/01/06 11:48:59 | Caused by: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request INFO | jvm 1 | 2020/01/06 11:48:59 | at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2820) INFO | jvm 1 | 2020/01/06 11:48:59 | at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549) INFO | jvm 1 | 2020/01/06 11:48:59 | at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) INFO | jvm 1 | 2020/01/06 11:48:59 | at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1962) INFO | jvm 1 | 2020/01/06 11:48:59 | at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) INFO | jvm 1 | 2020/01/06 11:48:59 | at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) INFO | jvm 1 | 2020/01/06 11:48:59 | at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:666) INFO | jvm 1 | 2020/01/06 11:48:59 | at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605) INFO | jvm 1 | 2020/01/06 11:48:59 | ... 20 common frames omitted INFO | jvm 1 | 2020/01/06 11:48:59 | INFO | jvm 1 | 2020/01/06 11:48:59 | 2020-01-06 11:48:59.382 WARN 5868 --- [nio-8981-exec-5] c.c.n.o.s.r.util.AsyncRequestListner : Async request completed for request: org.apache.catalina.connector.RequestFacade@7e83e9ff
Release : 10.0+
Component : NQRPTA - REPORTERANALYZER
In the X:\CA\NFA\OData\conf\com.ca.nfa.odata.ODataLimiters.cfg file we can adjust the timeout values.
Before making any changes, please make sure to first back up this *.cfg file
These are the default:
defaultExpandTopLimit=10
expandMaxTopLimit=2000
requestTimeout=30
maxRequestTimeout=120
requestThreadPoolCoreSize=10
requestThreadPoolMaxSize=50
defaultQueryTimeout=10
maxQueryTimeout=60
defaultPageSize=10
maxPageSize=1000
enableMultiTenancy=false
Here are values you could try:
defaultExpandTopLimit=10
expandMaxTopLimit=2000
requestTimeout=60
maxRequestTimeout=240
requestThreadPoolCoreSize=10
requestThreadPoolMaxSize=50
defaultQueryTimeout=120
maxQueryTimeout=120
defaultPageSize=10
maxPageSize=1000
enableMultiTenancy=false
Once you save the file, restart the CA NFA ODATA Service and try the query again.