AWS RDS does not assign SUPER priveledge to master account

Question

I am getting a too many connections error with my web app: http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html

I therefore want to connect into the database to show full processes/g so I can debug the code. I am using the following CLI command:

mysql -h domain.eu-west-1.rds.amazonaws.com -P 3306 -u username -p

The username I am using is the master username created when I launched the instance. It's my understanding that mysql always keeps one connection free to allow accounts with SUPER priveledges to connect, however when I do, I get the too many errors response.

Looking at the config, the master username appears to only have super priveledges on the database and not domain, is this correct? How else can I log into mysql to debug the server?

Answer 1

It is correct, RDS does not allow any account to have the SUPER privilege. This is an unfortunate limitation in RDS.

Your problem, though, sounds like it could be "fixed" (or at least addressed) by increasing max_connect_errors in the RDS parameter group for your instance, which will cause RDS to be more tolerant of the connect errors from your IP.

Your question leaves me wondering whether you have too many connections... or too many errors.

If errors, then you can try accessing it from a different IP, you can clear the counters with FLUSH HOSTS;.

Setting log_warnings = 2 in the parameter group will increase the amount of logging for connection errors (the error log is available via the console) and this might help you pinpoint whatever is causing the error counts from your host to rack up in the first place.

Parameter Group changes will be applied "hot" to the server where possible, but the Instance Details screen, next to the parameter group will give you a hint as to whether applying the changes is pending, requires a reboot, or is complete (in-sync).

If the problem is only "too many connections" then the only solution -- if you can't force the app to drop some connections -- is to reboot the instance and grab a connection slot, and remain connected so that you have access when the problem returns.