(Archived) What is the significance of ERROR 1227 42000 when a MySQL-dump import fails
You have tried to access an archived page. Please go to the new Knowledge Base to find more documents.
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 @all
To replace all DEFINER statements, the following sed command can be used on the NATGW:
sed -i "s/DEFINER=\`[^\`]*\`@\`[^\`]*\`/DEFINER=\`USERNAME\`@\`%\`/g" CustomerSQLDump.sql
root360 Knowledge Base - This portal is hosted by Atlassian (atlassian.com | Privacy Policy)