Error importing mysql data dump from gateway 9.0

Products

• CA API Gateway
• API SECURITY
• CA API Gateway Precision API Monitoring Module for API Gateway (Layer 7)
• CA API Gateway Enterprise Service Manager (Layer 7)
• STARTER PACK-7
• CA Microgateway

Issue/Introduction

After exporting data from 9.0 gateway using the following command:

mysqldump ssg --routines > /home/ssgconfig/ssgdev.sql     

While trying to import the data into the 10.0 gateway database, we are getting the below error a couple of times. 

mysql ssg < /home/ssgconfig/ssgdev.sql

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation

All the other data get imported without errors.

Environment

Release : 10.0

Component : API GATEWAY

Resolution

On mysql 8.0 the user used to import the data need to have SUPER privileges which you can grant with :

UPDATE mysql.user SET Super_Priv='Y' WHERE user='user1' AND host='%';

FLUSH PRIVILEGES;

This does not work on AWS RDS as there is no option to assign Super privileges on AWS RDS.

https://stackoverflow.com/questions/11601692/mysql-amazon-rds-error-you-do-not-have-super-privileges

As a workaround, you can remove the definer statements from the database dump before importing the data.