Upgrade from 9.4 to 10.1 database on external Mysql

Products

• CA API Gateway

Issue/Introduction

There is an a virtual appliance 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 from 9.4 to 10.1.

Basic configuration:

- appliance 10.1 CR02

- google cloud MySQL 8.0.28

- users:

----gateway (used by the appliance)

----Gateway_USER (as administrative access)

For some reason, the upgrader is trying to create a grant to localhost, which fails.

Parameters on Upgrader:

(Select option 2 (Display CA API Gateway configuration menu) and then option 1 (Upgrade the CA API Gateway database).

Gateway Database Upgrader.
Enter 'quit' to exit at any time.

Enter database host [localhost]: <mysql_hostname>
Enter database port [3306]:
Enter database name [ssg]: ssg
Enter database username [gateway]:Gateway_USER
Enter database password:
Database upgrade is required.

 Software version : 10.1.00
 Database version : 9.4.00

Perform upgrade? [No]: yes
Enter Administrative Database Username [root]: Gateway_USER
Enter Administrative Database Password:

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        ##

##      https://hub.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.