Spring Boot JPA - 配置自动重新连接

Spring Boot JPA - configuring auto reconnect

提问by stoffer

I have a nice little Spring Boot JPA web application. It is deployed on Amazon Beanstalk and uses an Amazon RDS for persisting data. It is however not used that often and therefore fails after a while with this kind of exception:

我有一个不错的 Spring Boot JPA Web 应用程序。它部署在 Amazon Beanstalk 上并使用 Amazon RDS 来持久化数据。然而，它不经常使用，因此在一段时间后失败，出现这种异常：

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 79,870,633 milliseconds ago.
The last packet sent successfully to the server was 79,870,634 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.

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException：从服务器成功接收的最后一个数据包是 79,870,633 毫秒前。
最后一个成功发送到服务器的数据包是在 79,870,634 毫秒之前。比服务器配置的“wait_timeout”值长。您应该考虑在应用程序中使用之前使连接有效性过期和/或测试连接有效性，增加客户端超时的服务器配置值，或使用连接器/J 连接属性“autoReconnect=true”来避免此问题。

I am not sure how to configure this setting and can not find information on it on http://spring.io(a very good site though). What are some ideas or pointers to information?

我不确定如何配置此设置，也无法在http://spring.io（虽然是一个非常好的网站）上找到有关它的信息。有哪些想法或信息提示？

回答by Stephane Nicoll

I assume that boot is configuring the DataSourcefor you. In this case, and since you are using MySQL, you can add the following to your application.propertiesup to 1.3

我假设 boot 正在DataSource为您配置。在这种情况下，并且由于您使用的是 MySQL，您可以将以下内容添加到您的application.properties1.3

spring.datasource.testOnBorrow=true
spring.datasource.validationQuery=SELECT 1

As djxak noted in the comment, 1.4+ defines specific namespaces for the four connections pools Spring Boot supports: tomcat, hikari, dbcp, dbcp2(dbcpis deprecated as of 1.5). You need to check which connection pool you are using and check if that feature is supported. The example above was for tomcat so you'd have to write it as follows in 1.4+:

正如 djxak 在评论中指出的那样，1.4+ 为 Spring Boot 支持的四个连接池定义了特定的命名空间：tomcat, hikari, dbcp, dbcp2(dbcp从 1.5 开始已弃用。您需要检查您使用的是哪个连接池，并检查该功能是否受支持。上面的示例是针对 tomcat 的，因此您必须在 1.4+ 中按如下方式编写它：

spring.datasource.tomcat.testOnBorrow=true 
spring.datasource.tomcat.validationQuery=SELECT 1

Note that the use of autoReconnectis not recommended:

请注意，使用的autoReconnect是不建议：

The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly.

不建议使用此功能，因为当应用程序不能正确处理 SQLExceptions 时，它会产生与会话状态和数据一致性相关的副作用，并且仅在您无法配置应用程序以处理由以下原因导致的 SQLExceptions 时使用死和陈旧的连接正确。

回答by Soumya

The above suggestions did not work for me. What really worked was the inclusion of the following lines in the application.properties

以上建议对我不起作用。真正有效的是在 application.properties 中包含以下几行

spring.datasource.testWhileIdle = true
spring.datasource.timeBetweenEvictionRunsMillis = 3600000
spring.datasource.validationQuery = SELECT 1

You can find the explanation out here

你可以在这里找到解释

回答by whoami

Setting spring.datasource.tomcat.testOnBorrow=truein application.properties didn't work.

spring.datasource.tomcat.testOnBorrow=trueapplication.properties 中的设置不起作用。

Programmatically setting like below worked without any issues.

像下面这样的编程设置没有任何问题。

import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;    

@Bean
public DataSource dataSource() {
    PoolProperties poolProperties = new PoolProperties();
    poolProperties.setUrl(this.properties.getDatabase().getUrl());         
    poolProperties.setUsername(this.properties.getDatabase().getUsername());            
    poolProperties.setPassword(this.properties.getDatabase().getPassword());

    //here it is
    poolProperties.setTestOnBorrow(true);
    poolProperties.setValidationQuery("SELECT 1");

    return new DataSource(poolProperties);
}

回答by Jose Jurado

I just moved to Spring Boot 1.4 and found these properties were renamed:

我刚搬到 Spring Boot 1.4，发现这些属性被重命名了：

spring.datasource.dbcp.test-while-idle=true
spring.datasource.dbcp.time-between-eviction-runs-millis=3600000
spring.datasource.dbcp.validation-query=SELECT 1

回答by naoru

whoami's answeris the correct one. Using the properties as suggested I was unable to get this to work (using Spring Boot 1.5.3.RELEASE)

whoami 的答案是正确的。使用建议的属性我无法让它工作（使用 Spring Boot 1.5.3.RELEASE）

I'm adding my answer since it's a complete configuration class so it might help someone using Spring Boot:

我正在添加我的答案，因为它是一个完整的配置类，因此它可能对使用 Spring Boot 的人有所帮助：

@Configuration
@Log4j
public class SwatDataBaseConfig {

    @Value("${swat.decrypt.location}")
    private String fileLocation;

    @Value("${swat.datasource.url}")
    private String dbURL;

    @Value("${swat.datasource.driver-class-name}")
    private String driverName;

    @Value("${swat.datasource.username}")
    private String userName;

    @Value("${swat.datasource.password}")
    private String hashedPassword;

    @Bean
    public DataSource primaryDataSource() {
        PoolProperties poolProperties = new PoolProperties();
        poolProperties.setUrl(dbURL);
        poolProperties.setUsername(userName);
        poolProperties.setPassword(password);
        poolProperties.setDriverClassName(driverName);
        poolProperties.setTestOnBorrow(true);
        poolProperties.setValidationQuery("SELECT 1");
        poolProperties.setValidationInterval(0);
        DataSource ds = new org.apache.tomcat.jdbc.pool.DataSource(poolProperties);
        return ds;
    }
}

回答by grep

I have similar problem. Spring 4 and Tomcat 8. I solve the problem with Spring configuration

我有类似的问题。Spring 4和Tomcat 8.我用Spring配置解决问题

<bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">
    <property name="initialSize" value="10" />
    <property name="maxActive" value="25" />
    <property name="maxIdle" value="20" />
    <property name="minIdle" value="10" />
     ...
    <property name="testOnBorrow" value="true" />
    <property name="validationQuery" value="SELECT 1" />
 </bean>

I have tested. It works well! This two line does everything in order to reconnect to database:

我已经测试过了。它运作良好！这两行可以重新连接到数据库：

<property name="testOnBorrow" value="true" />
<property name="validationQuery" value="SELECT 1" />

回答by Justin

In case anyone is using custom DataSource

如果有人使用自定义数据源

@Bean(name = "managementDataSource")
@ConfigurationProperties(prefix = "management.datasource")
public DataSource dataSource() {
    return DataSourceBuilder.create().build();
}

Properties should look like the following. Notice the @ConfigurationProperties with prefix. The prefix is everything before the actual property name

属性应如下所示。注意带有前缀的@ConfigurationProperties。前缀是实际属性名称之前的所有内容

management.datasource.test-on-borrow=true
management.datasource.validation-query=SELECT 1

A reference for Spring Version 1.4.4.RELEASE

Spring 版本 1.4.4.RELEASE 的参考

回答by code4kix

As some people already pointed out, spring-boot 1.4+, has specific namespaces for the four connections pools. By default, hikaricp is used in spring-boot 2+. So you will have to specify the SQL here. The default is SELECT 1. Here's what you would need for DB2 for example: spring.datasource.hikari.connection-test-query=SELECT current date FROM sysibm.sysdummy1

正如一些人已经指出的那样，spring-boot 1.4+ 为四个连接池提供了特定的命名空间。默认情况下，hikaricp 用于 spring-boot 2+。因此，您必须在此处指定 SQL。默认值为SELECT 1. 例如，以下是 DB2 所需的内容： spring.datasource.hikari.connection-test-query=SELECT current date FROM sysibm.sysdummy1

Caveat: If your driver supports JDBC4 we strongly recommend not setting this property. This is for "legacy" drivers that do not support the JDBC4 Connection.isValid() API. This is the query that will be executed just before a connection is given to you from the pool to validate that the connection to the database is still alive. Again, try running the pool without this property, HikariCP will log an error if your driver is not JDBC4 compliant to let you know. Default: none

警告：如果您的驱动程序支持 JDBC4，我们强烈建议不要设置此属性。这适用于不支持 JDBC4 Connection.isValid() API 的“传统”驱动程序。这是将在从池中向您提供连接之前执行的查询，以验证与数据库的连接是否仍然有效。同样，尝试在没有此属性的情况下运行池，如果您的驱动程序不符合 JDBC4，HikariCP 将记录错误以通知您。默认值：无

回答by enesaltinok

For those who want to do it from YAML with multiple data sources, there is a great blog post about it: https://springframework.guru/how-to-configure-multiple-data-sources-in-a-spring-boot-application/

对于那些想要使用多个数据源从 YAML 执行此操作的人，有一篇关于它的很棒的博客文章：https: //springframework.guru/how-to-configure-multiple-data-sources-in-a-spring-boot -应用/

It basically says you both need to configure data source properties and datasource like this:

它基本上说你们都需要像这样配置数据源属性和数据源：

@Bean
@Primary
@ConfigurationProperties("app.datasource.member")
public DataSourceProperties memberDataSourceProperties() {
    return new DataSourceProperties();
}

@Bean
@Primary
@ConfigurationProperties("app.datasource.member.hikari")
public DataSource memberDataSource() {
    return memberDataSourceProperties().initializeDataSourceBuilder()
            .type(HikariDataSource.class).build();
}

@Bean
@Primary
@ConfigurationProperties("app.datasource.member")
public DataSourceProperties memberDataSourceProperties() {
    return new DataSourceProperties();
}

@Bean
@Primary
@ConfigurationProperties("app.datasource.member.hikari")
public DataSource memberDataSource() {
    return memberDataSourceProperties().initializeDataSourceBuilder()
            .type(HikariDataSource.class).build();
}

Do not forget to remove @Primaryfrom other datasources.

不要忘记@Primary从其他数据源中删除。