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 :
Enter database name [ssg]:
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]: gatewayRoot2
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
Release : 10.1
2022-04-12T10:51:23.729133Z 99 Query grant all on ssg_testUpgrade.* to gateway@'localhost'
2022-04-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'