一段时间后，spring-boot Web 应用程序失去连接到 MySQL/RDS 的能力 [英] spring-boot web app loses ability to connect to MySQL / RDS after a while

问题描述

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

** 开始嵌套异常 **com.mysql.jdbc.exceptions.jdbc4.CommunicationsExceptionMESSAGE: 最后一个从服务器成功接收的数据包是39320秒前.最后一个成功发送到服务器的数据包是39320秒前，which 比服务器配置的 'wait_timeout' 值长.在您的 ap 中使用之前，您应该考虑过期和/或测试连接有效性plication，增加客户端超时的服务器配置值，或使用连接器/J 连接属性'autoReconnect=true' 来避免此问题米.堆栈跟踪:com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: 从服务器成功接收的最后一个数据包是 39320 秒前.最后一个数据包发送成功ssfully 到服务器是 39320 秒前，这比服务器配置的 'wait_timeout' 值要长.您应该考虑到期和/或 t在应用程序中使用之前测试连接有效性，增加客户端超时的服务器配置值，或使用连接器/J 连接 pr操作 'autoReconnect=true' 来避免这个问题.在 sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)在 sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)在 sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)在 java.lang.reflect.Constructor.newInstance(Constructor.java:526)在 com.mysql.jdbc.Util.handleNewInstance(Util.java:406)...修剪了更多的堆栈跟踪...引起:java.net.SocketException: Broken pipe在 java.net.SocketOutputStream.socketWrite0(本机方法)在 java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113)在 java.net.SocketOutputStream.write(SocketOutputStream.java:159)在 java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)在 java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)在 com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3227)... 119 更多** 结束嵌套异常 **

在我的 application.yml(可以设置数据源、休眠等的配置)中，我有以下内容(这是我调用管理 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":真，"spring.datasource.validationQuery":"SELECT 1",spring.datasource.continueOnError":真，"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":假，spring.jpa.generate-ddl":假，"spring.jpa.hibernate.ddl-auto":"无","spring.jpa.hibernate.dialect":"org.hibernate.dialect.MySQL5InnoDBDialect"},

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

 "spring.datasource.CONFIGURATION_PROPERTIES":{"前缀":"spring.datasource",特性":{"平台":"全部",数据":空，"driverClassName":"com.mysql.jdbc.Driver",密码":"******"，"url":"jdbc:mysql://rds-host:3306/mydb?user=mysqlusername&password=****","schema":"classpath:/schema.sql","用户名":"mysql用户名",jndiName":空，x":{数据源类名称":空，特性":{}},continueOnError":真，"sqlScriptEncoding":null,分隔器":";"，初始化":真}},

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

谢谢.

解决方案

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

cloud.aws.rds.testdb.password=testdbpwdcloud.aws.rds.testdb.username=testdbusercloud.aws.rds.testdb.databaseName=testdb

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

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driverspring.datasource.test-on-borrow: 真spring.datasource.validation-query: SELECT 1 FROM DUALspring.datasource.log-validation-errors: 真

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

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

@Component公共类 PoolConfiguration 实现 BeanPostProcessor {@覆盖公共对象 postProcessAfterInitialization(Object bean, String beanName) 抛出 BeansException {如果(TomcatJdbcDataSourceFactory的bean实例){TomcatJdbcDataSourceFactory tomcatJdbcDataSourceFactory = (TomcatJdbcDataSourceFactory) bean;tomcatJdbcDataSourceFactory.setTestOnBorrow(true);tomcatJdbcDataSourceFactory.setTestWhileIdle(true);tomcatJdbcDataSourceFactory.setValidationQuery("SELECT 1");}还豆；}}

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

祝你好运！

I have a normal spring boot 1.2.x web app with an embedded Tomcat 7.x container and connected to an RDS instance (running MySQL 5.6). If the application is idle for a period of time (8 hours?) and then it receives a request it throws the following exception

** 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 **

In my application.yml (where configurations for datasource, hibernate, etc. can be set) I have the following (this is part of what I get when I call the management 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"
   },

Curiously, when I call the management API "/configprops" I get this (I don't know if this is the root of the problem?

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

The question is: given the above configurations and details, why is it that I am still getting the "wait_timeout" exception? I would expect the connections to be tested when borrowed and I would expect the JDBC connection pool to create valid connections if none are available... so why is my application running out of valid connections after (8 hours?) or inactivity?

Thank you.

解决方案

If you are using auto-configuration to define RDS connection from the property file like this:

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

spring boot datasource auto-configuration will not work even you put these(or tomcat datasource conf) to your configuration file:

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

I think this is the reason why you cannot validate your connections in the pool, before using them.

You need to override postProcessAfterInitialization method to set pool properties of the TomcatJdbcDataSourceFactory bean like this:

@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;
}
}

I could not find any other solution for this.By the way this might be a bug of spring-cloud-aws-autoconfigure packet.

Good Luck!

这篇关于一段时间后，spring-boot Web 应用程序失去连接到 MySQL/RDS 的能力的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！