Upgrade SSG DB failed 9.4 to 10.1

Products

• CA API Gateway

Issue/Introduction

Getting Access denied for user 'gateway'@'%' to database 'ssg10_testUpgrade'

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

Enter database host [localhost]: <HostName>
Enter database port [3306]:
Enter database name [ssg]: ssg10
Enter database username [gateway]: 
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
Enter Administrative Database Password:

Performing database upgrade:

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

Access denied for user 'gateway'@'%' to database 'ssg10_testUpgrade'

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

Environment

API Gateway 10.1

Cause

The documentation is missing key steps when the SSG database is external and named different  (Permission to create and test the upgrade).

 

Resolution

Modify Permissions on the Database Host

Next, perform these permissions grants on the master database host:

Run the following MySQL commands to modify the permissions for the Administrative Database User (ADU);

1. CREATE USER '<ADU>'@'<HOSTNAME>' IDENTIFIED BY '<PASSWORD>';
2. GRANT ALL ON <DBNAME>_testUpgrade.* TO '<ADU>'@'<HOSTNAME>' IDENTIFIED BY 'password' WITH GRANT OPTION;
3. GRANT ALL ON <DBNAME>.* TO '<ADU>'@'<HOSTNAME>' IDENTIFIED BY '<PASSWORD>';
4. GRANT ALL ON mysql.* TO '<ADU>'@'<HOSTNAME>' IDENTIFIED BY '<PASSWORD>';
5. FLUSH PRIVILEGES;