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

Last updated: 2019-04-18

When I attempt to import data to an Amazon Relational Database Service (Amazon RDS) for MySQL instance using mysqldump, I receive 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

Short Description

Definer errors are triggered when MySQL attempts to create an object under a database user, and 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, which is not permitted for Amazon RDS, because Amazon RDS doesn't have superuser privileges.

Resolution

Definer errors can be addressed in multiple ways:

1.    Remove the following definer line:

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

The line should now read as follows: 

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

2.    Rename the definer users root and localhost to masteruser and %host, respectively:

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

3.    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.

See the following examples to see how the DEFINER can be removed in Linux, macOS, and 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.