ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

API Gateway: MySQL replication issues with the service_metrics and service_metrics_details tables


Article ID: 141122


Updated On:


CA API Gateway


This article will discuss some common issues seen when replication for service metrics is enabled, and the recommendations from Broadcom.

Common issues seen:

  • Replication breaks while ssg backup is running (due to slow queries against the database).
  • Replication breaks due to duplicate entries:
    Error 'Duplicate entry '20c25295fdfc4e81bcf84945596e4543-\x00\x00\x00\x00\x00\x00\x00\x0' 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 ('20c25295fdfc4e81bcf84945596e4543', 1573200430000, 5000, null, null, null, null, 0, 0, 0, 1573200425000, 0, x'0000000000000000FFFFFFFFFFFFFFFF', null, 1573200425000, 0, 0, x'D201C5A7A9D6517C4C31A18990B76F22')'​
  • Replication breaks due to key contraints:
    Error 'Cannot add or update a child row: a foreign key constraint fails (`ssg`.`service_metrics_details`, CONSTRAINT `service_metrics_details_ibfk_1` FOREIGN KEY (`mapping_values_goid`) REFERENCES `message_context_mapping_values` (`goid`) ON DELETE NO ACTION ON UPD)' on query. Default database: 'ssg'. Query: 'insert into service_metrics_details (back_max, front_max, back_min, front_min, attempted, authorized, completed, back_sum, front_sum, mapping_values_goid, service_metrics_goid) values (27, 29, 27, 29, 1, 1, 1, 27, 29, x'BD0182D9CE24D1A3AE79E38B9ADF2B95', x'BD0182D9CE24D1A3AE79E38B9ADF62C6')'​


There are many root causes for the various different replication errors, but the common theme is replication of service metrics, creating additional overhead and making a storm for new issues.


This article applies to all supported API Gateway versions.


Broadcom recommends disabling the replication of the service_metrics and service_metrics_details tables in MySQL in production systems. The solution below will exclude the service metrics-related tables from MySQL replication.

  1. Edit the /etc/my.cnf MySQL configuration file, and add the following three lines:
    • replicate-ignore-table=ssg.service_metrics
    • THIS SHOULD BE DONE Just below the following section in my.cnf,
    • # Slave reliability items:
      #Add replication ignores here
  2. While editing the /etc/my.cnf MySQL configuration file, edit the existing line below by changing it from 1 to 0 to read as follows:
    • innodb_flush_log_at_trx_commit=0
  3. Restart the mysqld service: service mysqld restart

The above steps should be completed on each database node in the cluster.

Additional Information

If replication broke after making these changes and after restarting the mysqld service, restart replication with the script.