Database upgrade fails when upgrading to Gateway 11.2

Products

• CA API Gateway

Issue/Introduction

Below error is returned when upgrading database from setup.sh script.

Operation CREATE USER failed for 'gateway'@'%' as it is referenced as a definer account in a stored routine

Environment

API Gateway 11.2 (Software form-factor)

Cause

During the 11.2 DB upgrade script, setup.sh tries to CREATE USER 'gateway'@'%', but MySQL 8.4 is stricter — it refuses if that account is already referenced as a definer in existing stored routines, views, or triggers.

Resolution

1. Connect to your SSG database as root:

mysql -u root -p ssg

2. Check which objects reference gateway as definer:

SELECT ROUTINE_TYPE, ROUTINE_NAME, DEFINER 
FROM information_schema.ROUTINES 
WHERE DEFINER = 'gateway@%';

SELECT TABLE_NAME, DEFINER 
FROM information_schema.VIEWS 
WHERE DEFINER = 'gateway@%';

SELECT TRIGGER_NAME, DEFINER 
FROM information_schema.TRIGGERS 
WHERE DEFINER = 'gateway@%';

3. If there are many objects, this is faster and less error-prone:

 

# 1. Backup first
mysqldump -u root -p --routines --triggers --events ssg > ssg_backup_pre112.sql

# 2. Create a version with gateway definer replaced
sed "s/DEFINER=\`gateway\`@\`%\`/DEFINER=\`root\`@\`localhost\`/g" \
  ssg_backup_pre112.sql > ssg_fixed.sql

# 3. Re-import
mysql -u root -p ssg < ssg_fixed.sql

4. After either approach, verify no gateway definers remain:

SELECT 'ROUTINE' AS type, ROUTINE_NAME AS name, DEFINER FROM information_schema.ROUTINES WHERE DEFINER='gateway@%' AND ROUTINE_SCHEMA='ssg'
UNION ALL
SELECT 'VIEW', TABLE_NAME, DEFINER FROM information_schema.VIEWS WHERE DEFINER='gateway@%' AND TABLE_SCHEMA='ssg'
UNION ALL
SELECT 'TRIGGER', TRIGGER_NAME, DEFINER FROM information_schema.TRIGGERS WHERE DEFINER='gateway@%' AND TRIGGER_SCHEMA='ssg';

Once that returns 0 rows, the DROP USER will succeed and setup.sh can proceed.

5. If you get this error during import database.

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

Fix the Dump File

Strip out the privileged statements before importing:

sed \
  -e '/^SET @@SESSION.SQL_LOG_BIN/d' \
  -e '/^SET @@GLOBAL/d' \
  -e 's/DEFINER=`gateway`@`%`//g' \
  ssg_backup_pre112.sql > ssg_fixed.sql

Note the definer is removed entirely (not replaced with root) — on RDS you can't set arbitrary definers either. MySQL will default to the importing user.

Then re-import:

 

mysql -u admin -p -h your-rds-endpoint ssg < ssg_fixed.sql

Run setup.sh and upgrade the database.

Run ./gateway start to start gateway.