Failed to restore Mysql/Mariadb dump file to RDS : ERROR 1419 (HY000) & ERROR 1227 (42000)

Posted by

1. ” ERROR 1419 (HY000) at line 1778: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)”

 

While restoring Mysql/MariaDB dump file (*.sql) to amazon RDS,  sometimes there will be issue saying it failed to restore and throw the above error.

This error occurs when the destination database has the binary log enabled, and the MySQL dump file contains an object (a trigger, view, function, or event).

If any of the create statements lack the “NO SQL”, “READS SQL DATA”, or “DETERMINISTIC” keywords, MySQL is prohibited from creating those objects, and the import fails with this error.

To resolve this case, we will need to change following parameter in AWS-RDS console.

Refer: How to Create and Edit RDS Parameters in AWS

In Most of the cases after changing parameter and a restart of RDS, you will be able to restore the dump file. But in some rare cases, following error is seen.

2. [email protected]:~/mysqldump# MySQL example_com < example.sql
ERROR 1227 (42000) at line 1778: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

*this occurs in RDS, even though you are a root/master-user.

This is due to “DEFINER” declaration inside your dump file which cannot be executed in RDS environment. So you will need to remove its occurrences in your backup/dump file (.sql).

So this can be done by running following command to replace its occurrences.

This is a regex search and replace command to replace all occurrences of “DEFINER=<username>@<hostname>” with a space (like deleting).

In my case, it was replacing all occurrences of “[email protected]” string with a ” space.
NB: it may take some time to complete based on the number of occurrences of above string in your .sql file and how big your database is.

By now you will be able to restore the database using “example_outfile.sql”, the newly edited file.

 

 

References:

  1. https://aws.amazon.com/premiumsupport/knowledge-center/error-1227-mysqldump/
  2.  https://serverfault.com/questions/555953/first-attempt-to-migrate-ec2-mysql-to-amazon-rds-no-going-well-super-privilege

Leave a Reply

Your email address will not be published. Required fields are marked *