How to set Amazon RDS SQL_MODE with multiple values

Question

MySQL allows parameter SQL_MODE to be set to multiple values in the my.cnf file. How can I do the same on Amazon RDS?

UPDATE: This problem has been fixed...see below.

Tincal

Answer 1

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

Caecum

Answer 2

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.

Tincal