Setup CA API Gateway9.4 with Azure Database for MySQL5.7

book

Article ID: 145366

calendar_today

Updated On:

Products

CA API Gateway API SECURITY

Issue/Introduction

API Gateway 9.4 has been deployed on Microsoft Azure.

https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/layer7-api-management/api-gateway/9-4/other-gateway-form-factors/run-ca-api-gateway-in-microsoft-azure-cloud/best-practices-and-recommendations-to-configure-gateway-in-azure.html

Azure Database for MySQL was created along with the link "Create an Azure Database for MySQL server by using the Azure portal" on the above page but "2) Create a new CA API Gateway database" was failed with the following error message.

Error creating database when saving configuration 'Cannot create database: 'Migration failed for change set ssg-base.xml::createProcedure-toGoid::gateway:
   Reason: liquibase.exception.DatabaseException: Error executing SQL CREATE FUNCTION toGoid (prefix bigint, suffix bigint)
          RETURNS binary(16) DETERMINISTIC
          begin
          if suffix is null then RETURN null;
          else RETURN concat(lpad(char(prefix >> 32, prefix),8,'\0'),lpad(char(suffix >> 32, suffix),8,'\0'));
          end if;
          end;: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)' [code:5, ERROR]'


This error was resolved by the knowledge article https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=46740 but another error was shown up.


---------------------
Configuration Results
---------------------

An error occurred during configuration.

Error creating database when saving configuration 'Cannot create database: 'Migration failed for change set ssg-base.xml::1408665452836-106::gateway:
   Reason: liquibase.exception.UnexpectedLiquibaseException: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.' [code:5, ERROR]'

Cause

The idle connection timeout of Azure Database for MySQL seems too short by default.

Environment

Release : 9.4

Component : API GATEWAY

Resolution

Azure Database for MySQL has a short wait_timeout value for the server parameter by default. The Gateway Configuration Menu may fail to setup the external ssg database on Azure. The value for the wait_timeout parameter needs to be increased to 28800 which is the default of MySQL 5.7.