aws mysql connection issues

I just set up a new spring mvc + hibernate application on aws instance via elastic beanstalk.

I created a new MySql db and was able to access it through my localhost, but I couldn't connect to mysql through my beanstalk application deployed on aws instance. 

I was able to fix these issues, and thought its worth sharing the solution as many others developers might be facing the same issue.  The connectivity issue could happen for different reasons, but in my 

case it was security groups that caused the issue.

First thing to make sure you correctly created you db instance, try to ping it from console.

>>>  nc -zv test.xxxxxxxxx.us-west-2.rds.amazonaws.com 3306

Connection to mgdb.czivgxrqpb9q.us-west-2.rds.amazonaws.com port 3306 [tcp/mysql] succeeded!

The aws troubleshooting guide is helpful and I would recommend to read it first.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Troubleshooting.html#CHAP_Troubleshooting.Connecting

Here is my spring-hibernate configuration file just for the reference:

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://www.springframework.org/schema/beans 

http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">

<bean id="appDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" >

    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>

    <property name="url" value="jdbc:mysql://xxxxxxx.us-west1.rds.amazonaws.com:3306/mgdb?zeroDateTimeBehavior=convertToNull"/>

    <property name="username" value="xxxxxx"/>

    <property name="password" value="xxxxxx"/>

</bean>

<bean id="appSessionFactory" 

class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">

    <property name="dataSource" ref="appDataSource"/>

    <property name="mappingResources">

        <list>

            <value>employee.hbm.xml</value>        

        </list>

    </property>

    <property name="hibernateProperties">

        <value>hibernate.dialect=org.hibernate.dialect.HSQLDialect</value>

    </property>

</bean>

<bean id="hibernateTemplate" 

class="org.springframework.orm.hibernate3.HibernateTemplate">

    <property name="sessionFactory">

        <ref bean="appSessionFactory"/>

    </property>

</bean>

</beans>

• First Connectivity issue:

28-Nov-2015 23:58:47.504 ERROR [localhost-startStop-1] <unknown>.<unknown> Unable obtain JDBC Connection

 com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

Caused by: java.net.ConnectException: Connection refused

Fix:

Initially I was getting Connection refused exception but this was gone once I updated the security group to enable all traffic to my ElasticBeanstalk application. 

I have updated the security group to accept All traffic with default IP as shown here. 

(This is okay for testing if you are troubleshooting the connectivity issue, but its a good idea to set specific protocols and destination for more security)

• Second Connectivity issue :

org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

Caused by: java.net.ConnectException: Connection timed out

Fix :

After resolving first issue I was still getting Connection timed out error. Going further in aws documentation I found that we also need to enable the DB Security group to make it accessible from EC2. 

As per aws docs - A DB security group controls network access to a DB instance that is not inside a VPC. By default, network access is turned off to a DB instance. You can specify rules in a security group that allows access from an IP address range, port, or EC2 security group. 

More can be found at this link:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithSecurityGroups.html

To change this, go to you DB instance and select "See Details" from "Instance Actions" dropdown.

On the details page click on the security group for your db instance.

Check the inbound and outbound settings for your DB security group. For testing purpose I am setting it to accept All traffic but its always recommended to configure more specific protocols and ip addresses, specially if its production database.

Inbound:

Outbound: