What is the significance of ERROR 1227 42000 when a MySQL-dump import fails?

Owned by Steffen Drya
Last updated: Feb 01, 2022 by Andreas Ulm
1 min read

 

Error

When importing a MySQL dump, the following error occurs:

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

Reason

AWS RDS does not allow the creation of functions and stored procedures by default. In addition, there may be problems with DEFINER statements in the SQL dump.

Solutions

1. Enable the use of functions, triggers, views

Root360 can use a support ticket to enable the use of SQL functions, views, and stored procedures. For this purpose, the following value must be set in the RDS parameter group:

log_bin_trust_function_creators = 1


2. Set DEFINER correctly

If DEFINER statements with fixed SQL users are specified in the SQL dump, they must be replaced with the user that is doing the import.

Before:

DEFINER = user_a @all

or

DEFINER=user_a@localhost

After:

DEFINER=`correctuser`@`%`

To replace all DEFINER statements, the following sed command can be used on the NATGW:

sed -i "s/DEFINER=\`[^\`]*\`@\`[^\`]*\`/DEFINER=\`USERNAME\`@\`%\`/g" CustomerSQLDump.sql

External Sources

 

root360 Knowledge Base - This portal is hosted by Atlassian (atlassian.com | Privacy Policy)