NFA ODATA API 500 error timeout

book

Article ID: 142599

calendar_today

Updated On:

Products

CA Network Flow Analysis (NetQos / NFA)

Issue/Introduction

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: [email protected]

Environment

Release : 10.0+

Component : NQRPTA - REPORTERANALYZER

Resolution

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.