title: AWS RDS MySQL error. scenario when restoring data generates error ERROR 1227(42000)?
body: <p>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:</p>
  
  <p>MySQL Dump    = version:  5.7.23
  MySQL Restore = version:  5.6.40</p>
  
  <h2>Error</h2>
  
  <p>The error that is obtained in the CI/CD deployment tool is:</p>
  
  <pre><code>`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
  `
  </code></pre>
  
  <p>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 <code>parameters.yml</code> file:</p>
  
  <pre><code>`mysqldump &gt; $tmpdir/db-backup/mysql.sql \
             --defaults-extra-file=&lt;(cat &lt;&lt; EOF
  [mysqldump]
  user=$mysql_user
  password=$mysql_pass
  host=$mysql_host
  EOF
  ) \
      "$mysql_db" \
  || return 1
  </code></pre>
  
  <h3>Fetching required credentials</h3>
  
  <pre><code>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)
  </code></pre>
  
  <p>Now when commenting line 17 and 18 of the above <code>mysql.sql</code> the restore procedure works flawlesly:</p>
  
  <pre><code>restore(){
  mysql &lt; $tmpdir/db-backup/mysql.sql \
            --defaults-extra-file=&lt;(cat &lt;&lt;EOF
  [mysql]
  user=$mysql_user
  password=$mysql_pass
  host=$mysql_host
  EOF
  ) \
            "$mysql_db" \
          || return 1
  }
  </code></pre>
  
  <h1>The questions here are:</h1>
  
  <ol>
  <li>How to pass some [optional parameters] (<a href="https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html" rel="noreferrer">https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html</a>)! to mysql dump command to correct this behaviour?</li>
  <li>Are there any other recommendations given to deal with this considering is AWS RDS and the DB versions are different?</li>
  </ol>
  
  <p>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.</p>
  
  <p>the code is included in the question's description.
  it is basically a db.sh shell script contianing backup() and restore() functions.</p>
  
  <p>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.</p>