一段时间后，spring-boot网络应用失去了连接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小时？），然后它收到请求，则会抛出以下异常：
在我的application.yml中对于数据源，hibernate等可以设置）我有以下几点（这是我在调用管理API / env时获得的部分内容）b
$ b
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.da 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：{前缀：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小时？）或不活动之后耗尽了有效连接？
谢谢。
解决方案如果您使用自动配置来定义从属性文件的RDS连接如下所示：
cloud.aws.rds.testdb.password = testdbpwd
云。 aws.rds.testdb.username = testdbuser
cloud.aws.rds.testdb.databaseName = testdb
Spring引导数据源自动配置将无法工作，即使您将这些（或tomcat数据源conf）添加到您的配置文件中： > 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实现BeanPostProcessor {
$ b $ @Override
public Object postProcessAfterInitialization（Object bean，String beanName）抛出BeansException {
if（bean instanceof TomcatJdbcDataSourceFactory）{
TomcatJdbcDataSourceFactory tomcatJdbcDataSourceFactory =（TomcatJdbcDataSourceFactory）bean;
tomcatJdbcDataSourceFactory.setTestOnBorrow（true）;
tomcatJdbcDataSourceFactory.setTestWhileIdle（true）;
tomcatJdbcDataSourceFactory.setValidationQuery（SELECT 1）;
}
返回bean;
我无法找到任何其他解决方案。这可能是 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网络应用失去了连接MySQL / RDS的能力的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
