Elastic beanstalk & # 8594; RDS Connection Error Using Grails

Question

I am deploying the Grails application for Amazon Web Services Elastic Beanstalk and am using RDS. I am using Grails, Spring-Security (RDS tables). The application works fine (input and data in RDS are delayed). However, from time to time, when I first log in, I get a database connection error. The second time it (immediately) works fine. The only thing I do a little differently is to use the Java system properties for the connection string, username and password to externalize the connection properties:

production { dataSource { url = System.getProperty("JDBC_CONNECTION_STRING") driverClassName = "com.mysql.jdbc.Driver" dbCreate = "validate" dialect = org.hibernate.dialect.MySQL5InnoDBDialect username = System.getProperty("PARAM1") password = System.getProperty("PARAM2") } } 

Stack trace:

 Caused by: org.hibernate.TransactionException: JDBC begin failed: at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:96) at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1353) at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:555) ... 80 more Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 41,541,715 milliseconds ago. The last packet sent successfully to the server was 41,541,716 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:532) at com.mysql.jdbc.Util.handleNewInstance(Util.java:407) at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3358) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1970) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2150) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620) at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5022) at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:371) at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:328) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:616) at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:239) at $Proxy11.setAutoCommit(Unknown Source) at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:91) ... 82 more Caused by: java.net.SocketException: Connection timed out at java.net.SocketOutputStream.socketWrite0(Native Method) at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109) at java.net.SocketOutputStream.write(SocketOutputStream.java:153) at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3339) ... 95 more 

What I tried: I added: autoReconnect = true for JDBC_CONNECTION_STRING

I added this for conf / spring / resources.groovy:

 beans = { dataSource(BasicDataSource) { minEvictableIdleTimeMillis=1800000 timeBetweenEvictionRunsMillis=1800000 numTestsPerEvictionRun=3 testOnBorrow=true testWhileIdle=true testOnReturn=true validationQuery="SELECT 1" } 

}

However, is this correct or can / should be added to the datasource.groovy file for each env configuration? In addition, the above only appears to be valid if I add the URL / driverClassName, username and password, which now means that it is in several places. I have not confirmed if it solves the problem, but is there a way to have it all in one place behind env?

Answer 1

This should work:

 production { dataSource { url = System.getProperty("JDBC_CONNECTION_STRING") driverClassName = "com.mysql.jdbc.Driver" dbCreate = "validate" dialect = org.hibernate.dialect.MySQL5InnoDBDialect username = System.getProperty("PARAM1") password = System.getProperty("PARAM2") //configure DBCP properties { minEvictableIdleTimeMillis=1800000 timeBetweenEvictionRunsMillis=1800000 numTestsPerEvictionRun=3 testOnBorrow=true testWhileIdle=true testOnReturn=true validationQuery="SELECT 1" } } }