弹性豆茎->使用 Grails 的 RDS 连接错误

Question

我正在将 Grails 应用程序部署到 Amazon Web Services Elastic Beanstalk 并利用 RDS。我正在使用 Grails、Spring-Security（RDS 表）。该应用程序似乎工作正常（RDS 中的登录和数据被拉回）。但是，第一次登录时有时会出现数据库连接错误。第二次（立即）工作正常。我做的唯一稍有不同的事情是尝试使用 Java 系统属性作为连接字符串、用户名和密码来外部化连接属性：

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")
        }
    }

堆栈跟踪：

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

我尝试过的： 我将 ?autoReconnect=true 附加到 JDBC_CONNECTION_STRING

我已将其添加到 conf/spring/resources.groovy:

beans = {       
    dataSource(BasicDataSource) {

        minEvictableIdleTimeMillis=1800000
        timeBetweenEvictionRunsMillis=1800000
        numTestsPerEvictionRun=3
        testOnBorrow=true
        testWhileIdle=true
        testOnReturn=true
        validationQuery="SELECT 1"
    }

}

但是，这是正确的位置还是可以/应该将其添加到每个 env 配置的 datasource.groovy 文件中？另外，只有当我添加 URL/driverClassName、用户名和密码时，上述内容似乎才有效，这意味着它位于多个位置。我还没有确认它是否解决了问题，但是有没有办法将所有这些都放在每个环境的一个地方？

Answer 1

这应该有效：

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"
            }
        }
}