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


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() 



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



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




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;


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.