Creating Triggers and Stored Procedures on MySQL AWS RDS instances

MySQL Server Parameters MySQL has a bunch of server parameters you can configure for your database (use with care because can adversely affect security, performance and data integrity):

http://dev.mysql.com/doc/refman/5.5/en/server-parameters.html

AWS RDS Parameter Groups
In order to configure these values on an AWS RDS instance, you'll need to use AWS DB Parameter Groups:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html

There's a default parameter list which you cannot modify. You can create your own parameter group and save the settings, then apply that group to your RDS instance(s).

Allow Creation of Triggers and Stored Procedures

You can't create triggers or stored procedures out of the box on AWS RDS instances because you won't get super access to the database server so you'll get an error like this:

ERROR 1419 (HY000) at line 49: 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)

To get around this you can set this server parameter to 1 (true): log_bin_trust_function_creators

Note that this can adversely affect replication if you don't know what you're doing.

Then apply it to your RDS instance using command line tools

rds-modify-db-instance --db-instance-identifier [your instance name here] --db-parameter-group-name [your group name here] --apply-immediately

Or AWS command line tools

aws rds modify-db-instance --db-instance-identifier bmxdb --db-parameter-group-name bmx --apply-immediately
 
Restart your instance.

Installing Command Line Interface:

http://websitenotebook.blogspot.com/2013/11/setting-aws-command-line-interface-on.html

Caveats Enabling These Settings - Impact on Replication

There are some issues with replication developers should be aware of when enabling this setting, if setting up fault tolerant master-slave architecture with replication.

http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html

... The implication is that although a user must have the CREATE ROUTINE privilege to create a function, the user can write a function containing a dangerous statement that will execute only on the slave where it is processed by a thread that has full privileges...
 

• To create or alter a stored function, you must have the SUPER privilege, in addition to the CREATE ROUTINE or ALTER ROUTINE privilege that is normally required. (Depending on the DEFINER value in the function definition, SUPER might be required regardless of whether binary logging is enabled. See Section 13.1.15, “CREATE PROCEDURE and CREATE FUNCTION Syntax”.)
• When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.
  By default, for a CREATE FUNCTION statement to be accepted, at least one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly. Otherwise an error occurs:
  

  ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
  or READS SQL DATA in its declaration and binary logging is enabled
  (you *might* want to use the less safe log_bin_trust_function_creators
  variable) 

  ....

  If you do not want to require function creators to have the
            SUPER privilege (for example,
            if all users with the CREATE
            ROUTINE privilege on your system are experienced
            application developers), set the global
            log_bin_trust_function_creators
            system variable to 1.