API Gateway Replication Issue in Production

Products

• CA API Gateway

Issue/Introduction

In Production, the API gateway Secondary DB server Replication got broken:

Error 'Cannot delete or update a parent row: a foreign key constraint fails (`ssg`.`policy`, CONSTRAINT `policy_folder` FOREIGN KEY (`folder_goid`) REFERENCES `folder` (`goid`))' on query. Default database: 'ssg'. Query: 'delete from folder where goid=x'89AF7E89645468B50379A1D8B3BE50A8'  

Environment

API Gateway: 10.1

Cause

This error message indicates that a folder could not be deleted because there is (at least) one policy within the folder that another service/policy is dependent on.

Resolution

If the steps in KB 44402 fail to reinitialize replication, you can follow the manual steps below. Please note the steps in KB 44402 is the preferred method to reinitialize replication. Performing manual steps can lead to user errors and unexpected results. Proceed with caution.

1. Take VM snapshots of both database nodes!
2. Dump the ssg database on the primary DB node: mysqldump ssg --routines > /home/ssgconfig/primary-ssg.sql
3. Copy primary-ssg.sql to the secondary DB node
4. Stop ALL ssg services (database nodes + processing nodes): service ssg stop
5. Stop slave replication on both database nodes: mysqladmin stop-slave
6. Reset the master configuration on both database nodes: mysql -e "reset master"
7. Reset the slave configuration on both nodes: mysql -e "reset slave; reset slave all"
8. [OPTIONAL] Dump the ssg database on the secondary DB node: mysqldump ssg --routines > /home/ssgconfig/secondary-ssg.sql
9. Log in to MySQL on the secondary DB node: mysql
10. Drop the ssg database: drop database ssg;
11. Create the ssg database: create database ssg;
12. Import the copied primary database: mysql ssg < /home/ssgconfig/primary-ssg.sql
13. Clear cluster_info table on both database nodes: truncate table cluster_info;
14. Reset the master configuration on both database nodes: mysql -e "reset master"
15. Reset the slave configuration on both database nodes: mysql -e "reset slave; reset slave all"
16. Execute the create_slave.sh script on the secondary DB node: /opt/SecureSpan/Appliance/bin/create_slave.sh
     + Follow the prompts of the script, keeping the following in mind:
        - Provide the FQDN of the primary node
        - Enter NO to the prompt "Do you want to clone a database from $Master (yes or no)?"
17. Execute the create_slave.sh script on the primary DB node: /opt/SecureSpan/Appliance/bin/create_slave.sh
     + Follow the prompts of the script, keeping the following in mind:
        - Provide the FQDN of the secondary node
        - Enter NO to the prompt "Do you want to clone a database from $Master (yes or no)?"
18. Start the ssg service on the database nodes: service ssg start
19. Query the status of the replication on both nodes: mysql -e "show slave status\G"
20. Start the processing nodes: service ssg start
21. Query the status of the replication on both nodes: mysql -e "show slave status\G"

 

Additional Information

Did you run into this error on step #12?

ERROR 3546 (HY000) at line 24: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

To resolve this error, run command mysql -e "reset master" BEFORE importing the SQL file.