What is the significance of ERROR 1227 42000 when a MySQL-dump import fails?
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
Related tutorials
Related components
External Sources
Expert
root360 Knowledge Base - This portal is hosted by Atlassian (atlassian.com | Privacy Policy)