wso2 is and api manager database configuration for clustered mysql

Question

I'm trying to setup IS as key manager (v3.0.0) and api manager (v3.0.0) using a HA mysql (v8.0) configuration. While running the sql scripts for db creation, I'm getting errors from mysql on the entries about foreign keys with ON DELETE CASCADE, which is not supported by mysql when group replication is on. How can I create a fully HA setup if I can only duplicate IS and API manager but not mysql? Is there a workaround?

Answer 1

You can replace cascade operations with triggers. Here is an Oracle sample. You can do the same with MySQL.

CREATE or REPLACE TRIGGER TRG_DEL_AM_APPLICATION
  BEFORE DELETE
    on AM_APPLICATION
    FOR EACH ROW 
      BEGIN
        DELETE FROM AM_SUBSCRIPTION AMSU WHERE AMSU.APPLICATION_ID = :OLD.APPLICATION_ID;
        DELETE FROM AM_APPLICATION_KEY_MAPPING AMKM WHERE AMKM.APPLICATION_ID = :OLD.APPLICATION_ID;
        DELETE FROM AM_APPLICATION_REGISTRATION AMAR WHERE AMAR.APP_ID = :OLD.APPLICATION_ID;
        DELETE FROM AM_APPLICATION_GROUP_MAPPING AMGM WHERE AMGM.APPLICATION_ID = :OLD.APPLICATION_ID;
    DELETE FROM AM_APPLICATION_ATTRIBUTES AAA WHERE AAA.APPLICATION_ID = :OLD.APPLICATION_ID;
      END;

Ref:

[1] https://github.com/bhathiya/apim-multi-dc-sql-scripts/blob/master/dbscripts/multi-dc/oracle/apimgt/tables.sql#L1855

[2] https://docs.oracle.com/goldengate/1212/gg-winux/GWUAD/wu_bidirectional.htm#GWUAD287