New Port Definitions fail to update on Harvesters due to duplicate Primary Key error in ManagerService*.log

Document ID : KB000031797
Last Modified Date : 14/02/2018
Show Technical Document Details

Issue/Symptoms: 

My newly added NFA Port Definitions will not update on my NFA Harvester(s).

I am seeing the following error message in my \Reporter\logs\ManagerService*.log files:

Duplicate entry 'TCP-1080-1080' for key 'PRIMARY'

   insert into port_definitions                     (   PortType,                          StartPort,                          EndPort                     ) values ('UDP',1056,1056),('TCP',1080,1080),('TCP',1080,1080),('UDP',1214,1214),('TCP',1214,1214),...('UDP',38037,38037),('UDP',38037,38037),('UDP',38293,38293),('UDP',38293,38293),('TCP',41523,41523),('UDP',41524,41524),('TCP',58261,58261),('TCP',65301,65301),('TCP',65301,65301)

   (2.0000 ms)

   at NetQoS.Data.MySqlDataComponentBase.ExecuteNonQuery(MySqlConnection connection, String sql, DataParameter[] dataParameters)

   at NetQoS.Data.MySqlDataComponentBase.ExecuteNonQueryAndUnlockOnError(String sql)

   at NetQoS.Data.MySqlDataComponentBase.ExecuteBulkInsertOnDuplicateKeyUpdate(String insertSql, String valuesSql, String updateSql, DataTable table, DataColumn[] columns, Int32 insertLimit, DbConnection connection)

   at NetQoS.Data.MySqlDataComponentBase.ExecuteBulkInsert(String insertSql, String valuesSql, DataTable table, DataColumn[] columns)

   at NetQoS.ReporterAnalyzer.Data.Harvester.PortDefinitionsDc.LoadPortDefinitionsTable(DataTable portDefinitions)

   at NetQoS.ReporterAnalyzer.Business.Port.Deploy(IFileLog log)

  at NetQoS.ReporterAnalyzer.Business.SystemMaintenance.DeployConfiguration()

   at NetQoS.ReporterAnalyzer.Business.SystemMaintenance.SyncConfiguration()

   at NetQoS.ReporterAnalyzer.Business.SystemMaintenance.MaintenanceTask.Run()

   at NetQoS.ReporterAnalyzer.Business.SystemMaintenance.Run()

   at NetQoS.ReporterAnalyzer.Business.SystemMaintenanceThread.ThreadHandler()

   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)

   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

   at System.Threading.ThreadHelper.ThreadStart() 

 

Environment:  

You may see this issue in any version of ReporterAnalyzer or Network Flow Analysis.

 

Cause: 

This can happen if the sync process between the NFA Console and the Harvesters become out of sync.

 

Resolution:

 

You can get the databases back in sync by following the steps below:

1. RDP to your NFA console server and stop the "Netqos ReporterAnalyzer Manager Service"

2. RDP to your NFA Harvester(s) and open a command prompt.

3. Run the following command on the Harvester(s):

mysql -P3308 -D harvester -t -e "truncate table port_definitions;"

4. RDP to your NFA Console server again and open a command prompt.

5. Locate any duplicate entries in the port_definitions table on the NFA Console server in the reporter database, such as with the example port 1080 above, and make note of the ID

mysql -P3308 reporter

select * from port_definitions where startPort=1080;

port_defs.png

Then delete the duplicate value, in this case ID 105.

delete from port_definitions where ID=105;

6. Run the following command to reset the sync status:

mysql -P3308 -D reporter -t -e "update system_settings set Value=unix_timestamp() where Parameter='portDefinitionLastModified';

7. Start up the "Netqos ReporterAnalyzer Manager Service" you stopped in step 1.

8. Check the \Reporter\logs\ManagerService*.log to verify that the error is no longer being logged.

    If you no longer see the error, your issue should be resolved, if you still see this error or a new error please open an issue with CA Support and attach this log file.