Versions Compared
compared with
Key
- This line was added.
- This line was removed.
- Formatting was changed.
| Excerpt |
|---|
Error
When importing a MySQL dump, the following error occurs:
| Code Block |
|---|
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:
| Code Block |
|---|
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`correctuser`@`%`
To replace all DEFINER statements, the following sed command can be used on the NATGW:
| Code Block |
|---|
sed -i "s/DEFINER=\`[^\`]*\`@\`[^\`]*\`/DEFINER=\`USERNAME\`@\`%\`/g" CustomerSQLDump.sql |
Related tutorials
| Filter by label (Content by label) | ||||||||
|---|---|---|---|---|---|---|---|---|
|
Related components
| Filter by label (Content by label) | ||||||||
|---|---|---|---|---|---|---|---|---|
|
External Sources
| Status | ||||
|---|---|---|---|---|
|
| Table of Contents | ||
|---|---|---|
|
| Filter by label (Content by label) | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
|