DB upgrade issue

Products

• CA API Gateway
• CA API Gateway Enterprise Service Manager (Layer 7)

Issue/Introduction

We have an issue upgrading the DB (external), here is the error:

Gateway Database Upgrader.

Enter 'quit' to exit at any time.

Enter database host [localhost]: mysql-host

Enter database port [3306]:

Enter database name [ssg]:

Enter database username [gateway]:

Enter database password:

Database upgrade is required.

Software version : [ upgraded version ]

Database version : [ previous version ]

Perform upgrade? [No]: Yes

Enter Administrative Database Username [root]: (Provide the name of the user that was specified during the installation, in general this is gateway and in some instances this might be root as well)

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

Release : 10.1, 11.1

Component : CA API Gateway

Cause

This means that to grant some privileges to a user, the user must be created first.

Resolution

20xx-xx-12T10:51:23.729133Z        99 Query     grant all on ssg_testUpgrade.* to gateway@'localhost'
20xx-xx-12T10:51:23.733976Z        98 Query     rollback

So this shows that the grants were rolled back. Hence, it was fixed after creating the 'user' locally.

i.e the user@local was required along with user@%

After creating user@local and granting it grant option like below, upgrade worked:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' with grant option;

Note: Here user can be 'gateway'