0

I'm moving my application to AWS ElasticBeanStalk and after spending countless hours trying to get my database connection working, I've repetitively failed.

First attempt,

I currently have an existing RDS database in use which I would like to continue use. I tried to connect to it with a plain old jdbc connection as followed. 

    <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>

    <property name="hibernate.connection.url">jdbc:mysql://masdfwe.czwweehqejmbr.us-east-1.rds.amazonaws.com:3306/project</property>
    <property name="hibernate.connection.username">root</property>
    <property name="hibernate.connection.password">xxxxx</property>

But found the following exception in the log.

Caused by: java.sql.SQLException: No suitable driver found for jdbc:mysql://masdfwe.czwweehqejmbr.us-east-1.rds.amazonaws.com:3306/project at java.sql.DriverManager.getConnection(DriverManager.java:596) at java.sql.DriverManager.getConnection(DriverManager.java:187) at org.hibernate.service.jdbc.connections.internal.DriverManagerConnectionProviderImpl.getConnection(DriverManagerConnectionProviderImpl.java:192) at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:278) at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:297) ... 125 more

The mysql driver is deff in the lib directory, so I'm not sure why I'm seeing this exception.

Do I need to create a JNDI connection? If so, how do I go about altering the configuration files in tomcat7 on ElasticBeanStalk? Am I suppose to ssh into the ec2 instance, or do I do it into the ElasticBeanStalk instance?

Would I use something like this in my hibernate.cfg.xml file?

<property name="hibernate.connection.datasource">java:comp/env/jdbc/project</property>

web.xml

 <resource-ref>
   <description>MyDatabase Description</description>
    <res-ref-name>jdbc/project</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
 </resource-ref>

I'm just not sure where or what I'm suppose to be configuring. Any help would be appreciated, I'm very confused.

Thanks in advance.

Code Junkie
  • 7,602
  • 26
  • 79
  • 141

3 Answers3

1

You shouldn't have to much about with JNDI to get your RDS connection to work. From your description, I'm also a bit puzzled as to what's not working for you. I've set up my JDBC/RDS connections with no problems, although I've used Spring to access the database, so details are handled by Spring's data sources.

Just an idea: You do have a

Class.forName ("com.mysql.jdbc.Driver").newInstance ();

to load your JDBC driver in your code?

Good luck!

joker
  • 746
  • 4
  • 11
  • Yeah I don't understand it either, I was starting to think it was a firewall issue, but I don't know enough about aws to say for sure. I do not have a newInstance of the driver, I'm using hibernate.cfg.xml. – Code Junkie Jan 24 '14 at 12:17
  • No, from the log message I'd say it's not a firewall issue. In some cases, you do need to update the firewall rules of the security group of your RDS instance to allow connections from your Elastic Beanstalk application. But the error you'd get in such a situation is more along the lines of a timeout, since you won't be able to get a response from the host... Are you certain that your JDBC .jar is in /WEB-INF/lib of your .war file? – joker Jan 24 '14 at 12:36
  • mysql-connector-java-5.1.27.jar can be found in the /WEB-INF/lib and I do have RDS configured to use my EC2 security group. I can change the JDBC url and run the app locally while connecting to a local database. Perhaps I'm wrong and hopefully you can confirm, but it's my impression that my hibernate.cfg.xml containing my jdbc connection info and the mysql jar in the lib directory should be all that's needed to get this to work, right? Or do you know if I have to make any manual configurations to tomcat or the Beanstalk GUI and if so, do those changes need to be made within the EC2 instance. – Code Junkie Jan 24 '14 at 13:34
  • You "shouldn't" need to make any changes to the Tomcat instance, to the Beanstalk configuration, nor to the EC2 instance. All that you "should" need should be able to go into the `.war` file that you deploy to your Elastic Beanstalk environment. However, I found this: [link](https://forums.aws.amazon.com/thread.jspa?messageID=286011). You could try to remove the MySQL jar from your WEB-INF/lib and add it to the Tomcat's `lib` on EC2 instead, just to see if that changes the error behaviour at all... – joker Jan 24 '14 at 13:51
  • Excellent find, I'll give that a shot later tonight and see if that does the trick. My last question which is do to me being so green with AWS, do I just add the jar to the EC2 instance and AMI automatically updates? I'm not sure how the EC2 instance interacts with AMI. Thanks so much. If it works, I'll mark your answer up tonight. – Code Junkie Jan 24 '14 at 14:16
  • To add the jar, you need to [log on to the instance](http://stackoverflow.com/questions/4742478/ssh-to-elastic-beanstalk-instance). Adding the jar to the instance does *NOT* automatically update your AMI. If this indeed helps, you'll need to either create a customised AMI for your application (probably a bad idea), or by creating an [instance `.config` script](http://docs.aws.amazon.com/elasticbeanstalk/latest/dg/using-features.customami.html) in the `.ebextensions` directory [not too fun either](http://www.hudku.com/blog/innocuous-looking-evil-devil/). – joker Jan 24 '14 at 14:42
  • That seems like a lot of work just to add a driver. I'd be interested in knowing why it can just pick it up the driver from my class path. I know it's packaged in my war during deployment, I'll have to take a look and see if it's in the lib directory on the deployed instance. I'm assuming every time EBS replicates an instance, it also creates a new instance of tomcat with a new deployed version that it load balances? Thanks for all your help. – Code Junkie Jan 24 '14 at 15:54
0

Your assumption is right. With Hibernate you should be able to use your configuration as is. No need to instantiate driver by name, it applies to Java code without Hibernate.

Your configuration settings look right.

Could it be that the jar with MySQL connector somehow got damaged? Is there anything else in stacktrace?

kukido
  • 10,431
  • 1
  • 45
  • 52
  • I think it said connection refused prior to the driver exception and no the driver isn't damaged, Im able to use it locally without issue. Im going to look tonight to verify the deployed version actually contains the jar, I know locally the packaged war does. I actually thought the driver exception actually meant you had a bad jdbc url. If the driver was actually missing wouldn't it result in some type of class exception. That actually lead me to believe this may have been a firewall issue, but joker doesn't think that's the issue. I dont like the idea of putting the driver in tomcat do to ami – Code Junkie Jan 24 '14 at 19:49
  • The driver should stay in WEB-INF/lib for sure. – kukido Jan 24 '14 at 20:03
  • Check out this guide: [Using Amazon RDS and MySQL Connector/J](http://docs.aws.amazon.com/elasticbeanstalk/latest/dg/create_deploy_Java.rds.html), although it is not for Hibernate, it might help. – kukido Jan 24 '14 at 20:04
0

I had a similar sounding problem with RDS and MySQL. I tried copying the driver's jar file and manipulating the classpath but none of that seemed necessary nor did it help. Eventually, I changed the code where my DataSource gets initialized to pass in my connection string rather than each property individually (e.g., ds.setUser( xxx )). This resolved my issue.

So in the end, my properties file contains these properties:

app.jdbc.driverClassName=com.mysql.jdbc.Driver
app.jdbc.url=jdbc\:mysql\://xyz.rds.amazonaws.com:3306/schema?user=username&password=mypassword

And my data source config:

ComboPooledDataSource ds = new ComboPooledDataSource();
ds.setDriverClass( jdbcDriverClassName );
ds.setJdbcUrl( jdbcUrl );
// other ds configuration parameters follow

With this change, I was able to overcome the "driver" exception and connect to the RDS database as expected.

Alex
  • 855
  • 7
  • 21