How can I resolve 1227 and definer errors when importing data to my Amazon RDS for MySQL DB instance using mysqldump?

3 minute read
0

When I attempt to import data to an Amazon Relational Database Service (Amazon RDS) for MySQL DB instance using mysqldump, I receive an error similar to one of the following:

Error: 1227 SQLSTATE: 42000 (ER_SPECIFIC_ACCESS_DENIED_ERROR) Access denied; you need (at least one of) the %s privilege(s) for this operation.
Definer error: example: /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER customer_update AFTER UPDATE ON `customer` FOR EACH ROW

Short description

A 1227 error occurs when the database has the binary log enabled and the mysqldump file contains a stored object, such as a trigger, view, function, or event. For more information, see Binary Log.

Definer errors are triggered when MySQL attempts to create an object under a database user but that database user doesn't exist on the destination database. You might receive a similar error when MySQL attempts to create a user for localhost, an action that isn't permitted for Amazon RDS. This is because Amazon RDS doesn't have superuser permissions.

Resolution

Resolve error 1227

1.    Set the log_bin_trust_function_creators parameter to true in the custom DB parameter group that you create for your DB instance.

2.    Some commands usually present in MySQL dump files, such as " SET @@SESSION.SQL_LOG_BIN= 0;", aren't allowed in RDS. These lines should be deleted from or commented on the dump file before the file is run against the RDS instance.

Resolve definer error

Definer errors can be addressed in several ways:

  • Remove the definer line
  • Rename the definer users
  • Create or re-create the dump file without the definer option

Remove the definer line

/*!50017 DEFINER=`root`@`localhost`*/

The line now shows output that's similar to this:

/*!50003 CREATE*/ /*!50003 TRIGGER customer_update AFTER UPDATE ON `customer` FOR EACH ROW

Rename the definer users

Rename the root to masteruser and localhost to %host:

/*!50003 CREATE*/ /*!50017 DEFINER=`masteruser`@`%`*/ /*!50003 TRIGGER customer_update AFTER UPDATE ON `customer` FOR EACH ROW

Note: You can use % as a wildcard for all hosts.

Create or re-create the dump file without the definer option.

The MySQL dump utility doesn't provide the option to remove a DEFINER. Some MySQL clients provide the option to ignore the definer when creating a logical backup, but this option doesn't occur by default. Review the documentation for your preferred MySQL client to see if the option to ignore the DEFINER is available. The MySQL command line client is unable to exclude the definer. However, the client can be used with third-party tools to remove the DEFINER or to find and replace the user name and host

The following examples demonstrate how the DEFINER can be removed in Linux, macOS, or Windows Subsystem for Linux (WSL):

Remove:

sed -i -e 's/DEFINER=`root`@`localhost`//g' dump.sql

Find and replace:

sed -i -e 's/DEFINER=`root`@`localhost`/DEFINER=`masteruser`@`%`/g' dump.sql

Note: Replace the value for masteruser with the name of your Amazon RDS master user.


Related information

MySQL on Amazon RDS

Exporting data from a MySQL DB instance by using replication