Troubleshooting "ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation"

Issue

Copying a database from an On Demand Environment to a non ODE fails with the following error in the task log:

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

Affected Applications

Affecting On Demand Environments only.

Resolution

The current workaround is to follow the below steps:

• Take a backup manual of the ODE database (see https://docs.acquia.com/cloud-platform/manage/back-up/)
• Move the backup you just created in /mnt/files/[example].[env]/backups/on-demand to the non ODE using Copying files to a different environment
• Unzip it using gunzip [backup-date-example.sql.gz] and remove the following entries from the beginning of the backup file:

  SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;                <====== Remove this
  SET @@SESSION.SQL_LOG_BIN= 0;              <======  Remove this
  
  --
  -- GTID state at the beginning of the backup
  --
  
  SET @@GLOBAL.GTID_PURGED='';               <====== Remove this

• Follow Importing your database dump file , replacing gunzip -c with cat since you already unziped the database.