AWS RDS MySQL error. scenario when restoring data generates error ERROR 1227(42000)?

Question

In a CI/CD deployment environment there is a MYSQL restore error. This error is due to AWS RDS MySQL permissions. One mysql dump file is generated in version 5.7 and restored in other environments with AWS RDS Mysql version 5.6 such as the following relationship:

MySQL Dump = version: 5.7.23 MySQL Restore = version: 5.6.40

Error

The error that is obtained in the CI/CD deployment tool is:

`Backup format OK
Restoring MySQL
ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
Error in hook: post_extract
Hook script failed
Build step 'Execute shell' marked build as failure
Finished: FAILURE`

Basically is just trying to restore a MYSQL dump file `mysql.sql`. The first 20 lines of the backup `mysql.sql` contains the following:
## Mysql.sql dump comments
`
-- MySQL dump 10.13  Distrib 5.7.24, for Linux (x86_64)
-- Server version       5.7.23-log
7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10 /*!40101 SET NAMES utf8 */;
11 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
12 /*!40103 SET TIME_ZONE='+00:00' */;
13 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
17 SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
18 SET @@SESSION.SQL_LOG_BIN= 0;
19
20 -- GTID state at the beginning of the backup
`

This is just a normal dump file generated after the execution of a backup.sh script by the following mysqldump code that passes the secrets parameters from a parameters.yml file:

`mysqldump > $tmpdir/db-backup/mysql.sql \
           --defaults-extra-file=<(cat << EOF
[mysqldump]
user=$mysql_user
password=$mysql_pass
host=$mysql_host
EOF
) \
    "$mysql_db" \
|| return 1

Fetching required credentials

parameters_yml=$secrets/config/parameters.yml
get_param() {
    sed -r -n "/^ *$1 *:/ {s/^ *$1 *: *(.*)/\1/ ; p}" $parameters_yml
}
mysql_host=$(get_param database_host)
mysql_user=$(get_param database_user)
mysql_pass=$(get_param database_password)
mysql_db=$(get_param database_name)

Now when commenting line 17 and 18 of the above mysql.sql the restore procedure works flawlesly:

restore(){
mysql < $tmpdir/db-backup/mysql.sql \
          --defaults-extra-file=<(cat <<EOF
[mysql]
user=$mysql_user
password=$mysql_pass
host=$mysql_host
EOF
) \
          "$mysql_db" \
        || return 1
}

The questions here are:

1. How to pass some [optional parameters] (https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html)! to mysql dump command to correct this behaviour?
2. Are there any other recommendations given to deal with this considering is AWS RDS and the DB versions are different?

I have been trying this in some AWS RDS DB instances with no sucess. The problem was found out and it is the issue with those comments in the mysql.sql file generated by mysql dump.

the code is included in the question's description. it is basically a db.sh shell script contianing backup() and restore() functions.

mysql.sql should be generated with those specific comments. restore() function should execute successfully and never show the error: ERROR 1227 (42000) at line n: Access denied; you need (at least one of) the SUPER privilege(s) for this operation.

Simons

Answer 1

In mysqldump comment out the below lines

SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN; SET @@SESSION.SQL_LOG_BIN= 0;

SET @@GLOBAL.GTID_PURGED='';

SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

The import should succeed.

for more details follow this link https://help.poralix.com/articles/mysql-access-denied-you-need-the-super-privilege-for-this-operation

Iceskate

True answer but @Andre Leon Rangel's newer answer is better, as it does the same thing through a mysqldump argument instead of manual edits. – Havard

You can also pass in -f to the mysql command which will make it skip over the errors and just log them out instead. – Undershorts

Answer 2

problem:

mysqldump with no extra parameters would generate a .sql dump file with extra unnecessary comments that doesnt make the dump portable.

solution:

add --set-gtid-purged=OFF in the mysqldump command to remove those comments.

some more details:

gtid stands for global Transaction ID and is a powerful feature of RDBS. Take consideration with this solution if your DB has Global transactions.

mysqldump ^{(Ver 14.14 Distrib 5.7.27)} even displays a warning to make sure you know about --set-gtid-purged=OFF:

$ mysqldump my_db_name my_table_name > foo.sql

Warning: A partial dump from a server that has GTIDs will by default include the
GTIDs of all transactions, even those that changed suppressed parts of the database.
If you don't want to restore GTIDs, pass
--set-gtid-purged=OFF
To make a complete dump, pass
--all-databases --triggers --routines --events

Simons

This answer is correct - note that you must use OFF and not COMMENTED which still leaves the binlog commands uncommented. – Esmeraldaesmerelda

Answer 3

Solution:

You can running this command in your mysql prompt (terminal or mysql workbench)

alert: you will insert your user in admin

GRANT SYSTEM_VARIABLES_ADMIN, SESSION_VARIABLES_ADMIN ON *.* TO 'admin'@'%';

Haymow