NFA 9.3.8 / CAPC Sync Timeout

book

Article ID: 102119

calendar_today

Updated On:

Products

CA Network Flow Analysis (NetQos / NFA)

Issue/Introduction

You may notice your NFA 9.3.8 and CAPC could potentially have sync timeout problems with errors like this in your ProductSyncWSLog or WebServicesQueryLog:

16:47:13 -1 - PushRequest - : 
Exception has been thrown by the target of an invocation. 
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> MySql.Data.MySqlClient.MySqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.TimeoutException: Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. ---> System.IO.IOException: Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. ---> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 

0:19:05 -1 - Query: 
[email protected]:3308 
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. 
DELETE FROM rt1 USING interface_relations_staged rt1 INNER JOIN (SELECT * FROM groups_pushedmembers WHERE UpdatedOn = 0 and ParentTypeName = 'group' and ChildTypeName = 'group') pr ON pr.ParentLocalID = rt1.ParentGroupId INNER JOIN interface_relations_staged rt2 on rt2.ParentGroupId = pr.ChildLocalId WHERE rt1.ChildGroupId = rt2.ChildGroupId 
(3 min 1 sec) 

15:27:02 -1 - Query:
[email protected]:3308
Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
drop table if exists interface_relations_staged;    create table interface_relations_staged like interface_relations;                     insert into interface_relations_staged                         select * from interface_relations
(3 min 1 sec)

Cause

The reporter. groups_pushedmembers table can become bloated and queries based on the (updatedon) column can take very long as we do not have an index of that table/column.

Environment

NFA 9.3.8 (this will work with 9.3.6 as well).
Windows 2012

Resolution

  1. RDP to the NFA Console Server
  2. Open a CMD Prompt.
  3. Type: mysql -P3308 -D Reporter -t -e "ALTER TABLE groups_pushedmembers ADD INDEX(updatedon);"
  4. Type: mysql -P3308 -D Reporter -t -e "ALTER TABLE groups_pushedItems ADD INDEX(updatedon);"
  5. Do a full resync on the CAPC system to NFA.