CA API Gateway: Duplicate entry errors, Last_Error: Error 'Duplicate entry

Products

• STARTER PACK-7
• CA Rapid App Security
• CA API Gateway

Issue/Introduction

When viewing your cluster nodes you may notice these errors when viewing replication status.
As a result replication will be broken and make continue to break even after re-initializing.


Last_Error: Error 'Duplicate entry '05441993516f45ec9b5d710a5103e625-\x86\xF0\x82\xD4\xF7\xFA\xB5\xA' for key 'nodeid'' on query. Default database: 'ssg'. Query: 'insert into service_metrics (nodeid, end_time, interval_size, back_max, front_max, back_min, front_min, attempted, authorized, completed, period_start, resolution, published_service_goid, service_state, start_time, back_sum, front_sum, goid) values ('05441993516f45ec9b5d710a5103e625', 1528452847862, 5000, 498, 499, 202, 203, 2, 2, 2, 1528452845000, 0, x'86F082D4F7FAB5A82B13E817B6C5EC9A', 'ENABLED', 1528452847284, 700, 702, x'683970D16A5160A03DDE48318846D5F8')' 
Skip_Counter: 0 

Environment

Release:
Component: APIGTW
API Gateway 9.4

Resolution

There are a couple of options depending on your need for the metrics data.

1) If you do not need the Service Metrics (don't need the Dashboard on the Policy Manager, or don't have any tool depending on the Metrics), we recommend disabling the Metrics. That way, your replication will not continue to break. The way to do that is to update the

Cluster Wide Property in the Policy Manager: 
serviceMetrics.enabled ---> change this to False 

2) If you need the Service Metrics, you can disable the replication of the Service Metrics table. All other tables will be replicated, but the metrics one will not. This should also should not cause replication to break. The way to implement that is: 

Edit the my.cnf file and add the following entries: 

replicate-ignore-table=ssg.service_metrics 
replicate-ignore-table=ssg.service_metrics_details 

Restart MySQL after that. Then you would most likely need to re-initialize replication as well. 

Additional Information

You also want to make sure that Primary and secondary using same primary DB 

there was number of cases where secondary was configured to use its own DB while primary was using its own .. so replication was being broken due to two active DBs ( duplicate entries ...)