java - JDBCConnectionException 有时会出现 spring 数据和 amazon RDS

Question

我经常不得不重新启动我们拥有的微服务，因为我在一段时间(6-12 小时)后收到 DataAccessResourceFailureException 和 JDBCConnectionException(我在下面插入了堆栈跟踪)。我正在使用 Spring 数据，数据库是 AWS RDS

这是我认为可以解决问题的配置文件片段

spring.datasource.dbcp2.validation-query= SELECT 1
spring.datasource.dbcp2.test-while-idle= true
spring.datasource.dbcp2.test-on-borrow=真
spring.datasource.dbcp2.time-between-eviction-runs-millis= 5000


[E] s.s.s.TaskUtils$LoggingErrorHandler:    Unexpected error occurred in scheduled task.
org.springframework.dao.DataAccessResourceFailureException: could not prepare statement; nested exception is org.hibernate.exception.JDBCConnectionException: could not prepare statement
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:257)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:488)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
Caused by: org.hibernate.exception.JDBCConnectionException: could not prepare statement
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:48)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:148)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
    at sun.reflect.GeneratedConstructorAccessor80.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 39,231,404 milliseconds ago.  The last packet sent successfully to the server was 39,231,405 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:62) 
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) 
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
Caused by: java.net.SocketException: Connection timed out (Write failed)
    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) 

Answer 1

DataAccessResourceFailureException ->当资源完全失败时抛出数据访问异常，例如:如果我们无法使用 JDBC 连接到数据库。我怀疑我们由于连接而收到错误。

请尝试查看RDS的日志。

而且我从日志中了解到的是 MySQLNonTransientConnectionException。所以这是由于超时后从池中删除了连接。

所以最好的选择是配置timeout，eviction-time要多一些。但是你的应用程序正在做的是正确的，不建议有更大的超时或驱逐时间