mysql - 一段时间后，spring-boot web 应用程序无法连接到 MySQL/RDS

Question

我有一个普通的 spring boot 1.2.x 网络应用程序，带有嵌入式 Tomcat 7.x 容器并连接到 RDS 实例(运行 MySQL 5.6)。如果应用程序空闲一段时间(8 小时？)，然后它收到一个请求，它会抛出以下异常

** BEGIN NESTED EXCEPTION ** 

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
MESSAGE: The last packet successfully received from the server was39320 seconds ago.The last packet sent successfully to the server was 39320 seconds ago, whi
ch  is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your ap
plication, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this proble
m.

STACKTRACE:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was39320 seconds ago.The last packet sent succe
ssfully to the server was 39320 seconds ago, which  is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or t
esting connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection pr
operty '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:526)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
... trimmed more of the stacktrace ... 
Caused by: java.net.SocketException: Broken pipe
	at java.net.SocketOutputStream.socketWrite0(Native Method)
	at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113)
	at java.net.SocketOutputStream.write(SocketOutputStream.java:159)
	at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
	at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3227)
	... 119 more


** END NESTED EXCEPTION **

在我的 application.yml(可以设置数据源、 hibernate 等的配置)中，我有以下内容(这是我调用管理 API/env 时得到的部分内容

   "applicationConfig: [classpath:/application.yml]#rds-profile":{  
      "spring.profiles":"rds-profile",
      "spring.datasource.driverClassName":"com.mysql.jdbc.Driver",
      "spring.datasource.url":"jdbc:mysql://rds-host:3306/mydb?user=mysqlusername&password=****",
      "spring.datasource.schema":"classpath:/schema.sql",
      "spring.datasource.username":"mysqlusername",
      "spring.datasource.password":"******",
      "spring.datasource.testOnBorrow":true,
      "spring.datasource.validationQuery":"SELECT 1",
      "spring.datasource.continueOnError":true,
      "spring.datasource.timeBetweenEvictionRunsMillis":5000,
      "spring.datasource.minEvictableIdleTimeMillis":5000,
      "spring.datasource.max-active":500,
      "spring.jpa.database-platform":"org.hibernate.dialect.MySQL5InnoDBDialect",
      "spring.jpa.database":"MYSQL",
      "spring.jpa.show-sql":false,
      "spring.jpa.generate-ddl":false,
      "spring.jpa.hibernate.ddl-auto":"none",
      "spring.jpa.hibernate.dialect":"org.hibernate.dialect.MySQL5InnoDBDialect"
   },

奇怪的是，当我调用管理 API“/configprops”时，我得到了这个(我不知道这是否是问题的根源？

  "spring.datasource.CONFIGURATION_PROPERTIES":{  
      "prefix":"spring.datasource",
      "properties":{  
         "platform":"all",
         "data":null,
         "driverClassName":"com.mysql.jdbc.Driver",
         "password":"******",
         "url":"jdbc:mysql://rds-host:3306/mydb?user=mysqlusername&password=****",
         "schema":"classpath:/schema.sql",
         "username":"mysqlusername",
         "jndiName":null,
         "xa":{  
            "dataSourceClassName":null,
            "properties":{  

            }
         },
         "continueOnError":true,
         "sqlScriptEncoding":null,
         "separator":";",
         "initialize":true
      }
   },

问题是:鉴于上述配置和细节，为什么我仍然得到“wait_timeout”异常？我希望在借用连接时测试连接，如果没有可用连接，我希望 JDBC 连接池创建有效连接...那么为什么我的应用程序在(8 小时？)或不活动后用完有效连接？

谢谢。

Answer 1

如果您使用自动配置从属性文件中定义 RDS 连接，如下所示:

cloud.aws.rds.testdb.password=testdbpwd
cloud.aws.rds.testdb.username=testdbuser
cloud.aws.rds.testdb.databaseName=testdb

即使您将这些(或 tomcat 数据源 conf)放入配置文件，spring boot 数据源自动配置也不会工作:

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.test-on-borrow: true
spring.datasource.validation-query: SELECT 1 FROM DUAL
spring.datasource.log-validation-errors: true

我认为这就是为什么您无法在使用连接之前在池中验证连接的原因。

您需要覆盖 postProcessAfterInitialization 方法来设置 TomcatJdbcDataSourceFactory bean 的池属性，如下所示:

@Component
public class PoolConfiguration implements BeanPostProcessor {

@Override
public Object postProcessAfterInitialization(Object bean, String beanName) throws BeansException {
    if (bean instanceof TomcatJdbcDataSourceFactory) {
        TomcatJdbcDataSourceFactory tomcatJdbcDataSourceFactory = (TomcatJdbcDataSourceFactory) bean;
        tomcatJdbcDataSourceFactory.setTestOnBorrow(true);
        tomcatJdbcDataSourceFactory.setTestWhileIdle(true);
        tomcatJdbcDataSourceFactory.setValidationQuery("SELECT 1");
    }
    return bean;
}
}

我找不到任何其他解决方案。顺便说一下，这可能是 spring-cloud-aws-autoconfigure 数据包的错误。

祝你好运!