Connecting Heroku to RDS using MySql2

1
votes

All I'm trying to do is deploy my Rails app on Heroku with a db instance on RDS, which seems like it should be straightforward.

I'm using the mysql2 adapter in my database.yml and have pushed to heroku and added my db instance per this answer. I've basically followed it to a T, and I don't get a missing gem error. But this is what the heroku logs reveal:

/app/vendor/bundle/ruby/1.9.1/bundler/gems/mysql2-e5992664a7bc/lib/mysql2/client.rb:56:in connect': Can't connect to MySQL server on 'nickcoxrails.cud3e2tma07q.us-east-1.rds.amazonaws.com' (110) (Mysql2::Error)

Also notice that my RDS instance is in us-east, which I heard is an issue here.

In my gemfile, I have:

gem 'mysql2', :git => 'git://github.com/brianmario/mysql2.git'

for my mysql2 gem because it was the only way I could get it working locally (because using the version < 0.3 per that first answer gave me an error on my local rails server because I'm on Rails 3.2.6), having tried a number of things (I really had trouble getting mysql running on my local machine).

I have definitely added the security groups per this answer and have added the mysql2 database per this answer. I have also added the RDS add-on in my heroku account. I ran rake db:create and rake db:schema:load and rake db:migrate and everything, but since I exported my local db to my RDS instance (I need to keep that data intact), none of that does anything new. And I can connect to my RDS instance through Sequel Pro, so I know its not a credentials issue.

This is my first Rails app deployment, so don't be afraid to dumb down your answers and give me the equivalent of the "Is it plugged in?" tech support questions for heroku/Rails/RDS/etc.

3
mysqlruby-on-railsherokuamazon-rds
You may want to try using the RDS addon as per the official docs. – Michael Mior
Thanks for the suggestion. Notice in my question I mention that add-on. When I heroku conifig:get DATABASE_URL, it echoes my RDS database, so I assume that's set up correctly. Is that a reasonable assumption? – nickcoxdotme
Sorry, missed that when reading the question. If DATABASE_URL is set your RDS instance, that suggests it was set up correctly. Did you run rds-authorize-db-security-group-ingress as mentioned after installing the addon? The error message suggests this might be the issue. – Michael Mior
Thanks for sticking with this. I didn't run that (because I couldn't make sense of the documentation for the RDS CLI tools), but I did authorize my ip through the RDS ui, which looks like it did the same thing…? Here's a screenshot. Does that do the same thing? – nickcoxdotme
That depends on how your security group is configured. It would be best to add the default security group from account 098166147350 (which is owned by Heroku) – Michael Mior

3 Answers

4
votes

Heroku changed its procedure to allow Heroku servers to connect to RDS instances. The accepted answer to this question is not how it should be done anymore.

You have to grant Heroku dynos access to your RDS instance. The recommended way to do this is to configure the RDS instance to only accept SSL-encrypted connections and configure the security group for your instance to permit ingress from all IPs.

Previously, Heroku published its AWS account ID and security group name as a way to grant access to an AWS RDS instance. This is no longer recommended.

Ref: https://devcenter.heroku.com/articles/amazon_rds

2
votes

Make sure you have authorized the security group default from AWS account 098166147350 which is owned by Heroku. The error message suggests that your connection is not being allowed.

0
votes

I just achieved victory with Heroku+AWS (MySQL instance). I had to drop a support ticket into Heroku to get this solved (operator error) and I wanted to post this info to help anyone else out. I kept bumping into this thread.

I am using Rails(4) on Heroku w/Amazon MySQL RDS. Yes, you need to configure SSL (the pem file and all that fun). But I could not get Rails to connect and followed all walk throughs very close. The Amazon DB was working and I could connect outside of Heroku/Rails no problem (via my IP).

The problem was in the security group and IP access. Via my support ticket "[your] RDS mysql DB...you need to allow ingress from all IPs". This was the link Heroku provided : https://devcenter.heroku.com/articles/amazon_rds#require-ssl where Heroku currently states :

"You have to grant Heroku dynos access to your RDS instance. The recommended way to do this is to configure the RDS instance to only accept SSL-encrypted connections from authorized users and configure the security group for your instance to permit ingress from all IPs."

Some searches soon led me to modifying the default security group for my AWS DB instance and adding a CIDR/IP entry of :

0.0.0.0/0

Essentially allowing any/all IPs. As soon as that was authorized all connected.

I am still reviewing this setting and seeking feedback from friends and community. Luckily I am still in dev and have some time to digest this setting.

Hoping this can save someone some time and this seems to be the current method to connect to AWS DB via Heroku Dynos.

Good luck and I will be watching for any feedback.

EDIT: After sleeping on the setting, I removed the 0.0.0.0/0 from default and added it to an isolated security group and tested - working. I was concerned with default security group being applied with a . style IP access on a non-SSL DB. That felt wrong.