How do I resolve 1227 and definer errors when I use mysqldump to import data to my Amazon RDS for MySQL DB instance?

Resolution

To review your database events and changes, review the binary log. For more information, see The binary log on the MySQL website.

Error 1227

When you activate binary logging and the mysqldump file contains a stored object, such as a trigger, view, function, or event, you might receive the following error:

"Error: 1227 SQLSTATE: 42000 (ER_SPECIFIC_ACCESS_DENIED_ERROR) Access denied; you need (at least one of) the %s privilege(s) for this operation."

To resolve this error, complete the following steps:

1. In the custom DB parameter group that you created for your DB instance, set the log_bin_trust_function_creators parameter to True.
2. Remove the command lines such as SET @ @SESSION .SQL_LOG_BIN= 0; from the dump file before you run the file against the instance.

Definer error

A definer error occurs when MySQL tries to create an object as a database user that doesn't exist in the target database. Or, a definer occurs when MySQL tries to create a user for the localhost because Amazon RDS doesn't have superuser permissions. You might an error similar to the following:
"Definer error: example: /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER customer_update AFTER UPDATE ON `customer` FOR EACH ROW" error message."

To resolve the error, take the following actions.

Remove the DEFINER line

Locate the DEFINER line, and then delete it:

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

Example output:

/*!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:

/*!50017 DEFINER=`masteruser`@`%host`*/

Example output:

/*!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.

Remove the DEFINER option, and then create or recreate the dump file

The MySQL dump utility doesn't include an option to remove DEFINER. Some MySQL client utilities provide an option to ignore the DEFINER when you create logical backups, but this feature isn't available by default. To determine whether you have the option ignore the DEFINER, check the documentation for your preferred MySQL client. The MySQL command line client can't exclude the DEFINER. However, you can use the client with third-party tools to remove the DEFINER or find and replace the username and host.

To find and delete the DEFINER line in Linux, macOS, or Windows Subsystem for Linux (WSL), run the following command:

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

To find and replace the username and host in Linux, macOS, or WSL, run the following command:

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

Note: Replace masteruser with the name of your Amazon RDS primary user.

Related information

Amazon RDS for MySQL

Exporting data from a MySQL DB instance by using replication