Unable to upgrade the gateway - due to DB grant error
search cancel

Unable to upgrade the gateway - due to DB grant error


Article ID: 268395


Updated On:


CA API Gateway


We are using Layer7 Gateway version 10.0. We are upgrading our Gateway to version 10.1. We have hosted our servers in centOS in AWS cloud. We are using Amazon RDS(MYSQL) for the Layer7 database. 

The upgrade steps were completed successfully in Test environments. Now we are working on PROD upgrade, where we are finding issues in Upgrade the database step.

The patches were applied one by one and gateway was rebooted. The service status is "Wont start". When we give the upgrade the Database option in SSGCONFIG menu, we are getting the below error,

"You are not allowed to create a user with GRANT"

We are using the correct admin password for the RDS endpoint. We have below global parameter already set on MySQL AWS instance. 

SET GLOBAL log_bin_trust_function_creators = 1;

We got the same issue during past upgrade which is resolved by following the steps in below KB article.  


We have tried the same steps now, which is not working.




Release : 10.0  AIM gateway with RDS MYSQL database



The RDS for Amazon had set some restriction for the assigned user role and upgrade script is trying to execute a mysql statement to grant privileges for the specified the user but it failed.  The failed sql was:  

grant all on <dbname name>.* to <user>  [with grant option] ;

Which is not allowed in the RDS (Amazon MYSQL). 

Also, work around in


does not work for the 10.1 base version (but works in 10.1 CR02),  it gives error (a known defect): 

Error: Unable to initialize main class liquibase.integration.commandline.Main
Caused by: java.lang.NoClassDefFoundError: ch/qos/logback/core/Context


1.  mysqldump for the production database 

          mysqldump <ssg db> -h MYSQLRDSPROD -u CUSTOMUSER -pYourDBPass --set-gtid-purged=OFF --routines > /tmp/mysqlbackup.sql

2. create new database in the lower env where upgrade was successful.

3.  mysql import the dump data from production

    mysql <ssg db> -h MYSQLRDSTEST -u CUSTOMUSER -pYourDBPass < /tmp/mysqlbackup.sql

4. change node.properties file to point to new host/database (make a copy of the node.properties file before edit)

5. restart ssg in the lower environment where the node.properties file was change

6. ssgconfig menu option for upgrade database to do the upgrade

7. after successful upgrade,  mysqldump the upgraded database

8.  import upgraded database mysqldump file to production database,  restart the ssg

9.  copy back lower environment saved node.properties file and restart ssg