All Products
Search
Document Center

A "OPERATION need to be executed set by ADMIN" error is reported for RDS MySQL

Last Updated: Jul 22, 2020

Problem description

  • The following error occurs when you create a stored procedure, function, trigger, event, or view:
  • When you export an SQL statement from a local database, the following error occurs when you apply this SQL statement to apsaradb for RDS:
  • When you download a logical backup from an RDS MySQL 5.6 instance to an apsaradb for RDS instance or an on-premises database, the following errors occur. The error codes are 1227 and 1725.
[Err] 1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation-common in RDS MySQL 5.6 
ERROR 1725 (HY000) at line 1936: OPERATION need to be executed set by ADMIN-common in RDS MySQL 5.5
ERROR 1227 (42000) at line 2984: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Causes

  • When you import data to the RDS for MySQL instance, the SQL statement contains one or more SQL statements that require the Super permission to run. The RDS for MySQL instance does not provide the Super permission to run such SQL statements. Therefore, you must delete these SQL statements.
  • GTID is not enabled for the local MySQL instance.

Solutions

Alibaba Cloud reminds you that:

  • When you perform operations that have risks, such as modifying instance configurations or data, check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
  • Before you modify the configurations and data of instances including but not limited to ECS and RDS instances, we recommend that you create snapshots or enable RDS log backup.
  • If you have authorized or submitted sensitive information such as the logon account and password in the Alibaba Cloud Management Console, we recommend that you modify such information in a timely manner.
  1. Delete the DEFINER statement.
    1. Check the SQL file and delete SQL statements such as the following.
      DEFINER=`root`@`%`
    2. In Linux, you can try to delete using the following command.
      sed -ie 's/DEFINER[ ]*=[ ]*[^ ]*/ / 'your.sql
  2. Delete the GTID_PURGED statement.
    Description: You can also add the "-- set-gtid-purged=off" parameter after the mysqldump command to cancel the output GTID_PURGED clause during export.
    1. Check the SQL file and delete SQL statements such as the following.
      SET @@GLOBAL.GTID_PURGED='d0502171-3e23-11e4-9d65-d89d672af420:1-373, d5deee4e-3e23-11e4-9d65-d89d672a9530:1-616234';
    2. In Linux, you can try to delete using a command similar to the following.
      awk '{ if (index($0,"GTID_PURGED")) { getline; while (length($0) > 0) { getline; } } else { print $0 } }' your.sql | grep -iv 'set @@' > your_revised.sql

Application scope

  • ApsaraDB RDS for MySQL