0

Tearing my hair out. Learnt lots from my previous mistakes (Cannot connect remotely to EC2 MySQL installation), however I have now configured identically (AFAICT, outputs below), but cannot get heroku to connect to my new AWS RDS DB MYSQL instance! my old instances are fine.

One concern I have is that the Heroku article https://devcenter.heroku.com/articles/amazon-rds seems to have conflicting info out there about how to use use wild cards for the GRANT statements.

RDS article: https://devcenter.heroku.com/articles/amazon-rds says 

    GRANT USAGE ON *.* TO 'username'@'%';

BUT https://www.flydata.com/blog/access-denied-issue-amazon-rds/, https://www.flydata.com/blog/access-denied-issue-amazon-rds/ suggest a different syntax using '%'

GRANT USAGE ON `%`.* TO `username`@`%` IDENTIFIED BY 'pwd';

to no affect.

So..

  • all instances created with same security group
  • security group has inbound access (and works for 2 other instances)
  • GRANT access (as per my original 2 instances )
  • Tried new suggested syntax of % not *

Have tried

  1. with or without SSL
  2. creating a new security group

Security groups (all instances are the same for my 3 environments, but one i cannot connect from heroku)

            $ grep sg- aws_instance.txt 
                "VpcSecurityGroupId": "sg-c8ce36b4"
                "VpcSecurityGroupId": "sg-c8ce36b4"
                "VpcSecurityGroupId": "sg-c8ce36b4"

Security group config

and visually i can see inboound config: MYSQL,TCP,3306,0.0.0.0/0

        {
            "DBSecurityGroups": [
                {
                    "DBSecurityGroupDescription": "default", 
                    "IPRanges": [
                        {
                            "Status": "authorized", 
                            "CIDRIP": "0.0.0.0/32"
                        }, 
                        {
                            "Status": "authorized", 
                            "CIDRIP": "0.0.0.0/0"
                        }, 
                        {
                            "Status": "authorized", 
                            "CIDRIP": "87.1.1.1/32"
                        }
                    ], 
                    "OwnerId": "xxxxxxx", 
                    "DBSecurityGroupArn": "arn:aws:rds:us-east-1:xxxxxxx:secgrp:default", 
                    "EC2SecurityGroups": [
                        {
                            "Status": "authorized", 
                            "EC2SecurityGroupName": "default", 
                            "EC2SecurityGroupOwnerId": "xxxxxxxxx", 
                            "EC2SecurityGroupId": "sg-2aca2f43"
                        }
                    ], 
                    "DBSecurityGroupName": "default"
                }, 
                {
                    "VpcId": "vpc-a7d034c1", 
                    "DBSecurityGroupDescription": "Inbound DB only", 
                    "IPRanges": [], 
                    "OwnerId": "xxxxxx", 
                    "DBSecurityGroupArn": "arn:aws:rds:us-east-1:xxxxxxx:secgrp:mysecuritygroupdbonly", 
                    "EC2SecurityGroups": [], 
                    "DBSecurityGroupName": "mysecuritygroupdbonly"
                }
            ]
        }
Community
  • 1
  • 1
Ben
  • 1,292
  • 1
  • 13
  • 21
  • There's no point in discussing SSL *and* permissions *and* security groups. This fundamentally means you're guessing, rather than troubleshooting, because the symptoms of these three types of problems are all completely different... though I can't really fault or criticize you because there is so much inane and inaccrate advice to be found online, written by unqualified people. So... let's troubleshoot. What is the exact error, when you try to connect, preferrably using the `mysql` cli? – Michael - sqlbot Mar 10 '17 at 03:31
  • Thanks Michael (apologies for tardiness this is part time thing for me). You've made me rethink the problem. In my defence I assumed looking at differences between what works and what doesn't was a good place to start, but always happy to learn! the exact error is ruby rails app cannot connect (username/pwd) whilst running on heroku. I'm just adding more debug log output to get more details on the error. will revert – Ben Mar 19 '17 at 21:27

0 Answers0