NFA 9.3.8 / CAPC Sync Timeout

Document ID : KB000102119
Last Modified Date : 20/06/2018
Show Technical Document Details
Issue:

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: 
reporter@127.0.0.1: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:
reporter@127.0.0.1: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)

Environment:

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

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