Can't remove old ESM certificate

Products

• CA API Gateway

Issue/Introduction

After removing ESM since it's no longer supported, it's necessary to clean up and remove the expired ESM certificate. The certificate has been removed from the Remote Management menu, but when trying to remove it within the Policy Manager, it says it's still in use. When using Restman to remove the certificate, the following error is observed: 

<l7:Error xmlns:l7="http://ns.l7tech.com/2010/04/gateway-management">
    <l7:Type>ConstraintViolation</l7:Type>
    <l7:TimeStamp>2022-04-05T11:55:08.057-04:00</l7:TimeStamp>
    <l7:Link rel="self" uri="https://gateway:8443/restman/1.0/trustedCertificates/0bcab05c86a5779581fd1d217f5e7218"/>
    <l7:Detail>Cannot delete or update a parent row: a foreign key constraint fails (`ssg`.`trusted_esm`, CONSTRAINT `trusted_esm_ibfk_1` FOREIGN KEY (`trusted_cert_goid`) REFERENCES `trusted_cert` (`goid`))</l7:Detail>
</l7:Error>

Environment

API Gateway: 10.1

Cause

If you were on Gateway 9.4 and did not remove the certificate prior to migrating your database, the certificate cannot be removed from the ssgconfig menu or Policy Manager.

Resolution

1. Take a snapshot of the VM
2. Run command: mysql ssg
3. Run query: select name from trusted_esm;
   There should be one record, your ESM certificate
4. Run query: delete from trusted_esm where name like '%<NAME>%';
   Replace <NAME> with the name of the ESM certificate
5. Run query: flush privileges;

You should be able to delete the certificate from the Policy Manager.