Upgrade database on external Mysql

Products

• CA API Gateway

Issue/Introduction

A virtual appliance is installed on Google Cloud (GCP) and it needs to be configured to an external MySQL database.

This MySQL instance is also on GCP and there are issues trying to upgrade the DB.

The following message shows up during gateway configuration

Performing database upgrade:

  Testing the upgrade on a test database ...
  Creating test database "ssg_testUpgrade" (without audits).
  Database creation may take a few minutes.
  The database was not upgraded due to the following reasons:

You are not allowed to create a user with GRANT

No changes have been made to the database. Please correct the problem and try again.
Press [Enter] to continue

Environment

API Gateway 10.1, 11.x

Cause

Some CLOUD based environment restrictions can cause failure in our upgrades related to MySQL object creations i.e. SSG DB Azure,  Google Cloud 

Resolution

We have documentation for a workaround that does direct database update

https://techdocs.broadcom.com/us/en/ca-enterprise-software/layer7-api-management/api-gateway/10-1/release-notes/known-issues.html

From the docs:

1. Backup your Azure MySQL database.
2. In privileged shell, enter the following command: (NOTE need to replace version info and access to your setup)

GW10.1

/opt/SecureSpan/JDK/bin/java -jar /opt/SecureSpan/Gateway/runtime/lib/liquibase-#.#.#.jar --driver=com.mysql.jdbc.Driver  --classpath=/opt/SecureSpan/Gateway/runtime/lib/mysql-connector-java-#.#.##.jar --changeLogFile=/opt/SecureSpan/Gateway/config/etc/db/ssg.xml --url=jdbc:mysql://<mysql_hostname>:3306/<ssg_db-name> --username='<dbuser>@<gatewayHostname>' --password='x' update

GW11.x

/opt/SecureSpan/JDK/bin/java -jar /opt/SecureSpan/Gateway/runtime/lib/liquibase-#.#.#.jar --driver=com.mysql.cj.jdbc.Driver  --classpath=/opt/SecureSpan/Gateway/runtime/lib/mysql-connector-java-#.#.##.jar --changeLogFile=/opt/SecureSpan/Gateway/config/etc/db/ssg.xml --url=jdbc:mysql://<mysql_hostname>:3306/<ssg_db-name> --username='<dbuser>@<gatewayHostname>' --password='x' update

How to find version info:

#cd /opt

# find . -name liquibase*

./SecureSpan/Appliance/config/lib/liquibase-4.5.0.jar

./SecureSpan/Gateway/runtime/lib/liquibase-4.5.0.jar

./SecureSpan/Controller/lib/liquibase-4.5.0.jar

# find . -name mysql-connect*

./SecureSpan/Appliance/config/lib/mysql-connector-java-8.0.26.jar

./SecureSpan/Gateway/runtime/lib/mysql-connector-java-8.0.26.jar

./SecureSpan/Controller/lib/mysql-connector-java-8.0.26.jar

(NOTE :/ is needed at end of classpath for newer liquibase on my GW 10.1 CR2)

# /opt/SecureSpan/JDK/bin/java -jar /opt/SecureSpan/Gateway/runtime/lib/liquibase-4.5.0.jar --driver=com.mysql.jdbc.Driver  --classpath=/opt/SecureSpan/Gateway/runtime/lib/mysql-connector-java-8.0.26.jar:/ --changeLogFile=/opt/SecureSpan/Gateway/config/etc/db/ssg.xml --url=jdbc:mysql://<mysql_hostname>:3306/<ssg_db-name> --username='<dbuser>' --password='xxxxx' update

 

Results:

 

# /opt/SecureSpan/JDK/bin/java -jar /opt/SecureSpan/Gateway/runtime/lib/liquibase-4.5.0.jar --driver=com.mysql.jdbc.Driver  --classpath=/opt/SecureSpan/Gateway/runtime/lib/mysql-connector-java-8.0.26.jar:/ --changeLogFile=/opt/SecureSpan/Gateway/config/etc/db/ssg.xml --url=jdbc:mysql://<mysql_hostname>:3306/<ssg_db-name> --username='<dbuser>' --password='xxxxx' update

Dec 02, 2022 12:46:01 PM liquibase.servicelocator

INFO: Cannot load service: liquibase.license.LicenseService: Provider liquibase.license.pro.DaticalTrueLicenseService could not be instantiated

####################################################

##   _     _             _ _                      ##

##  | |   ()           () |                       ##

##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##

##  | |   | |/ _ | | | | | '_ \ / _ / __|/ _ \    ##

##  | |___| | (| | || | | |) | (| \__ \  __/      ##

##  \_____/|\__, |\__,||.__/ \__,_|___/\___|    ##

##              | |                               ##

##              |_|                               ##

##                                                ##

##  Get documentation at docs.liquibase.com       ##

##  Get certified courses at learn.liquibase.com  ##

##  Free schema change activity reports at        ##

##      learn.liquibase.com                       ##

##                                                ##

####################################################

Starting Liquibase at 12:46:01 (version 4.5.0 #52 built at 2021-09-27 16:19+0000)

Loading class com.mysql.jdbc.Driver'. This is deprecated. The new driver class is com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

Liquibase: Update has been successful.