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

Issue

When you try to copy a database from one environment to another, you get this error message in the logs:

stdout was:
stderr was: + set -o pipefail
+ mysql -h XXXXX -u XXXXX -pXXXXX -e 'DROP DATABASE IF EXISTS `mydb`; CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET utf8 */'
Warning: Using a password on the command line interface can be insecure.
+ mysqldump -qcK --single-transaction -h XXXXX -u XXXXX -pXXXXX
+ LANG=C
+ sed -e 's|^/[*]!50001 CREATE ALGORITHM=UNDEFINED [*]/|/*!50001 CREATE */|' -e '/^[/][*]!50013 DEFINER=/d'
+ mysql -h XXXXX -u XXXXX -pXXXXX mydb
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
ERROR 1227 (42000) at line 4241: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
mysqldump: Got errno 32 on write

Resolution

The current workaround is to follow the below steps:

Option 1:

• Take a backup manual of the CDE 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 CDE 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:
  copy

  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.

Option 2:

When copying database data from a source site to a target site, passing the --set-gtid-purged=OFF option prevents the GTID information from being included in the dump:

drush sql-sync @dev @self --extra-dump=--set-gtid-purged=OFF

Cause 

The database includes MySQL triggers, stored procedures, or functions  statements, which does require SUPER privileges as the error stated and these are not supported on the platform.

Related