What do I do if the "OPERATION need to be executed set by ADMIN" error message is displayed? - ApsaraDB RDS

Problem description

One of the following errors occurs when you create a stored procedure, function, trigger, event, or view.
One of the following errors occurs when you export an SQL statement from an on-premises database and execute the statement on ApsaraDB RDS.
One of the following errors occurs when you download logical backup files from an ApsaraDB RDS for MySQL 5.6 instance and import them to ApsaraDB RDS or an on-premises database. The error code may be 1227 or 1725.
One of the following errors occurs when you execute a MySQL statement to modify parameters.

[Err] 1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation --A common error for ApsaraDB RDS for MySQL 5.6 
ERROR 1725 (HY000) at line 1936: OPERATION need to be executed set by ADMIN --A common error for ApsaraDB RDS for 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 data is imported to ApsaraDB RDS for MySQL, specific SQL statements can be executed only if your account is granted super permissions. ApsaraDB RDS for MySQL does not provide super permissions. As a result, these SQL statements must be removed.
No global transaction identifier (GTID) is assigned to the ApsaraDB RDS for MySQL instance.
Database accounts do not have sufficient permissions to modify specific parameters by executing MySQL statements. Example: SET GLOBAL event_scheduler = 1.

Solutions

Note

Take note of the following items:

Before you perform high-risk operations such as modifying the configurations or data of Alibaba Cloud instances, we recommend that you check the disaster recovery and fault tolerance capabilities of the instance to ensure data security.
Before you modify the configurations or data of an Alibaba Cloud instance, such as an Elastic Compute Service (ECS) instance or an ApsaraDB RDS instance, we recommend that you create snapshots or enable backup for the instance. For example, you can enable log backup for an ApsaraDB RDS instance.
If you have granted specific users the permissions on sensitive information, such as usernames and passwords, or submitted sensitive information in the Alibaba Cloud Management Console, we recommend that you modify the sensitive information at the earliest opportunity.

Remove DEFINER statements.
1. Check your SQL file and remove DEFINER statements that are similar to the following example:
```
DEFINER=`root`@`%`
```
2. In Linux, run the following command to remove DEFINER statements:
```
sed -ie 's/DEFINER[ ]*=[ ]*[^ ]*/ / ' your.sql
```
Remove GTID_PURGED statements.
Note
Note: You can also add the "--set-gtid-purged=off" parameter to the end of the mysqldump command to remove GTID_PURGED clauses from the output.
1. Check your SQL file and remove GTID_PURGED statements that are similar to the following example:
```
SET @@GLOBAL.GTID_PURGED='d0502171-3e23-11e4-9d65-d89d672af420:1-373, d5deee4e-3e23-11e4-9d65-d89d672a9530:1-616234';
```
2. In Linux, run the following command to remove GTID_PURGED statements:
```
awk '{ if (index($0,"GTID_PURGED")) { getline; while (length($0) > 0) { getline; } } else { print $0 } }' your.sql | grep -iv 'set @@' > your_revised.sql
```
Check permissions of your account. If your account does not have super permissions, grant super permissions to your account or switch to another account and then try again. If you do not have permissions on account operations, contact your administrator.

Applicable scope

ApsaraDB RDS for MySQL