API Gateway: MySQL Cluster not sync

Products

• CA Mobile API Gateway
• CA Rapid App Security
• CA API Gateway

Issue/Introduction

We are in throuble with a MySQL Cluster not synchronizing due some replication error.

The "mysql -e "show slave status\G"" command on  both nodes status returns:

Slave_IO_State: Waiting for master to send event

Slave_IO_Running: Yes

Slave_SQL_Running: No

Last_Errno: 1062

Last_Error: Error 'Duplicate entry '\xB4\xBF\xF6N\xA3\xA8E\xDB`\xB7\x81Q\xA8}\xC1o-765' for key 'i_policy_ordinal'' on query. Default database: 'ssg'. Query: 'insert into policy_version (name, version, active, ordinal, policy_goid, time, user_login, user_provider_goid, `xml`, goid) values (null, 0, 1, 765, x'B4BFF64EA3A845DB60B78151A87DC16F', 1567771200227, 'localsystem', x'0000000000000000FFFFFFFFFFFFFFFF', '<wsp:Policy xmlns:wsp="http://schemas.xmlsoap.org/ws/2002/12/policy" xmlns:l7="http://ns.l7tech.com/2012/04/api-management" xmlns:L7p="http://www.layer7tech.com/ws/policy">\n<wsp:All wsp:Usage="Required">\n<L7p:AuditDetailAssertion>\n<L7p:Detail stringValue="Policy Fragment: Account Plans Fragment"/>\n<L7p:LoggingOnly booleanValue="true"/>\n</L7p:AuditDetailAssertion>\n<L7p:CommentAssertion>\n<L7p:Comment stringValue="// This fragment is auto generated by the gateway. DO NOT MODIFY"/>\n</L7p:CommentAssertion>\n<L7p:SetVariable>\n<L7p:Base64Expression stringValue="JHthcGlLZXl9"/>\n<L7p:VariableToSet stringValue="api

        Seconds_Behind_Master: NULL

Environment

Release : 9.x and 10.x

Component : CA API Gateway

Resolution

Evident that someone updated policy.version this seems to have been very slow so the write failed over to the secondary database and wrote successfully there. (But also got committed on the primary). Then when replication tried to re-write it the entry already existed and caused this break in mysql replication.

The only supported way to correct this is to re-initialize replication, Which the following KB can be followed,
https://knowledge.broadcom.com/external/article?articleId=44402

And if required you can also see it in video form as well if needed here,
https://www.youtube.com/watch?v=54S06WdaSBo