How to set Amazon RDS SQL_MODE with multiple values

MySQL allows parameter SQL_MODE to be set to multiple values in the my.cnf file. However, Amazon RDS allows only one value, whether you use their browser-based console or their CLI tools.

I have found a (perhaps not perfect) solution to the problem of not being able to set SQL_MODE to multiple values. In my procedure below, I show what my settings are. You may choose whatever values you think are pertinent to your environment.

1) In your parm group, set SQL_MODE = TRADITIONAL (or which ever one value is your highest priority)

2) In your parm group, set init_connect to: SET SESSION sql_mode = 'TRADITIONAL,IGNORE_SPACE,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY'

Now when a client logs into the database, their session SQL_MODE should be set to all values passed in by the init_connect string. In my case, this equates to: IGNORE_SPACE, ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, TRADITIONAL, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

Caveat: Editing the init_connect value after setting it involved cut-n-paste. However, I found that trying to delete an init_connect value after setting it is a big hassle, so be careful in working with it. The hassle? To delete an init_connect value after setting it required that I create a new parm group from scratch, set all values except init_connect to what the original parm group had, delete the original parm group, then rename the new group with the old name. This was true with both Firefox and Chrome. I also could not delete the value using the CLI tool (aws rds modify-db-parameter-group).

Although not really part of this post, if anyone responds with how to delete/reset/null-out init_connect without the kind of hassle I describe above, that would be great.

UPDATE 1:

Never mind. I have discovered that an init_connect setting (on RDS) will NOT survive a database reboot. Therefore, the solution I offered above does not work.

Also, since one cannot remove (delete) an init_connect setting through the console (why not?), one can remove it by using the aws rds reset-db-parameter-group CLI command rather than rebuilding the whole parameter group like I described before.

I am VERY disappointed that Amazon RDS:

• Does not allow multiple SQL_MODE values.
• Does not allow us to delete an init_connect setting through the console.

UPDATE 2:

Per Ross Scrivener's response, I tested setting multiple SQL_MODE values through the AWS console. I used the values 'TRADITIONAL,IGNORE_SPACE,ONLY_FULL_GROUP_BY' (no spaces). I then rebooted the server twice, and everything seemed to have held.

Thank you Amazon for fixing this, and thank you Ross Scrivener for pointing out the AWS change.

Helpfully it looks like Amazon have fixed this as of May 24th 2016

The sql_mode parameter can now be set to a list of values for all MySQL and MariaDB versions. The list of values should be correct in all regions as well.

https://forums.aws.amazon.com/thread.jspa?threadID=232266