UsingMySQLtriggersandviewsinAmazonRDS_MySQL

I recently had an opportunity to migrate a customer from a physical server into Amazon’s RDS environment. In this particular case the customers’ platform makes extensive use of MySQL triggers and views. I came across two significant issues that prevented me from following Amazon’s documentation, which basically states “use mysqldump” but doesn’t call out a specific method of dealing with MySQL triggers and views.

Amazon Relational Database Service (Amazon RDS) is a great platform if you’re looking for complete hands-off management of your MySQL environment, but comes at a cost in the area of flexibility, i.e. you don’t have SUPER privilege and this brings up additional challenges.

1. You need to ensure you set log_bin_trust_function_creators=1 ( by default this is off, 0).
2. You need to clean up your mysqldump syntax.

#1 is easy, you simply make a configuration change within the Amazon RDS GUI on the node’s Parameter Group to set log_bin_trust_function_creators=1 and then a restart of your Amazon RDS node. The restart is required since without the SUPER privilege you lose access to changing DYNAMIC variables on the fly. #2 is a little more complex. If you go with vanilla mysqldump (from say a 5.5 mysqldump binary) on a schema that has triggers and views, you will see error 1227, something like this:

ERROR 1227 (42000) at line 27311: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

ERROR 1227 ( 42000 ) at line 27311 : Access denied ; you need ( at least one of ) the SUPER privilege ( s ) for this operation

You’re seeing this message because MySQL in Amazon RDS doesn’t provide the SUPER privilege, and thus you cannot set up a trigger or view to run as a different user — only a user with SUPER can do that.

mysqldump will generate syntax for a trigger like this:

DELIMITER ;;/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER `after_insert_lead` AFTER INSERT ON `leads` FOR EACH ROW BEGINUPDATE analytics.mapping SET id_lead = NEW.id_lead WHERE mc_email = NEW.email;END */;;DELIMITER ;

DELIMITER ; ; / * ! 50003 CREATE * / / * ! 50017 DEFINER = ` root ` @ ` % ` * / / * ! 50003 TRIGGER ` after_insert_lead ` AFTER INSERT ON ` leads ` FOR EACH ROW BEGIN UPDATE analytics .mapping SET id_lead = NEW .id_lead WHERE mc_email = NEW .email ; END * / ; ; DELIMITER ;

and for a view like this:

/*!50001 CREATE ALGORITHM=UNDEFINED *//*!50013 DEFINER=`web`@`%` SQL SECURITY DEFINER *//*!50001 VIEW `admin_user_view` AS SELECT ...

/ * ! 50001 CREATE ALGORITHM = UNDEFINED * / / * ! 50013 DEFINER = ` web ` @ ` % ` SQL SECURITY DEFINER * / / * ! 50001 VIEW ` admin_user_view ` AS SELECT . . .

The problem is in the “DEFINER” lines.

Here’s one method that worked for me:

1. Identify all the DEFINER lines in your schema. I found it helpful to dump out a –no-data and then weed through that to get a unique list of the DEFINER lines
2. Create a sed line for each unique DEFINER line (see my example in a moment)
3. Include this sed line in your dump/load script

Here’s what my sed matches looked like:

sed-e 's//*!50017 DEFINER=`root`@`localhost`*///'-e 's//*!50017 DEFINER=`root`@`%`*///'-e 's//*!50017 DEFINER=`web`@`%`*///'-e 's//*!50017 DEFINER=`cron`@`%`*///'-e 's//*!50013 DEFINER=`cron`@`%` SQL SECURITY DEFINER *///'-e 's//*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER *///'-e 's//*!50013 DEFINER=`root`@`%` SQL SECURITY DEFINER *///'-e 's//*!50013 DEFINER=`web`@`%` SQL SECURITY DEFINER *///'

sed - e 's//*!50017 DEFINER=`root`@`localhost`*///' - e 's//*!50017 DEFINER=`root`@`%`*///' - e 's//*!50017 DEFINER=`web`@`%`*///' - e 's//*!50017 DEFINER=`cron`@`%`*///' - e 's//*!50013 DEFINER=`cron`@`%` SQL SECURITY DEFINER *///' - e 's//*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER *///' - e 's//*!50013 DEFINER=`root`@`%` SQL SECURITY DEFINER *///' - e 's//*!50013 DEFINER=`web`@`%` SQL SECURITY DEFINER *///'

Note: the example above won’t directly work due to WordPress “helpfully” stripping my text… you need to escape the forward slashes and asterisks.

A big caveat: this method is akin to a brute force method of getting your data into Amazon RDS — you’ve lost the elegance & security of running your triggers and views as separate defined users within the database — they are all now going to run as the user you loaded them in as. If this is a show-stopper for you, contact Percona and I’d be happy to take on your case and develop a more comprehensive solution.

Now all that’s left is to integrate this into your dump flow. Something like this should work:

mysqldump--host=source| sed-e ... lots of lines| mysql--host=destination

mysqldump -- host = source | sed - e . . . lots of lines | mysql -- host = destination

I hope this helps someone!