java - Spring boot、hibernate、hikariCP 和 mysql 不活动后无通信

Question

我们遇到了一个奇怪的错误。当我们第一次启动服务器时，我们可以登录等。几个小时不活动后，我们无法再登录。我们可以通过重新启动应用程序来解决该问题，但这种情况每天至少会发生一次。重启应用只是一个短期解决方案，我们需要找到更稳定的解决方案来解决这个关键问题。

我正在使用以下配置:

环境

Hibernate       : 5.2.13
JPA             : spring-boot-starter-data-jpa (1.5.10)
JDK version     : 1.8.0_191
Database        : Mysql (version 5.7.25)
JDBC Driver     : 5.1.45
Spring Boot     : 1.5.10
OS              : Ubuntu 16.04.6

光

HikariCP version: 2.7.7

datasource:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/<database>?useUnicode=true&characterEncoding=utf8&useSSL=false
        username: <username>
        password: <secret password>
        hikari:
            data-source-properties:
                cachePrepStmts: true
                prepStmtCacheSize: 250
                prepStmtCacheSqlLimit: 2048
                useServerPrepStmts: true

来自应用程序日志:

AuthenticationServiceException[http-nio-18080-exec-9] UserJWTController: Authentication exception trace: {}
org.springframework.security.authentication.InternalAuthenticationServiceException: Could not open JPA EntityManager for transaction; nested exception is org.hibernate.TransactionException: JDBC begin transaction failed: uthentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:126)
        at org.springframework.security.authentication.dao.AbstractUserDetailsAuthenticationProvider.authenticate(AbstractUserDetailsAuthenticationProvider.java:144)
        at org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:174)
        at com.example.web.rest.UserJWTController.authorize(UserJWTController.java:48)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
        ...
        ...
        ...
        ...
        ...
        ...
        Caused by: org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is org.hibernate.TransactionException: JDBC begin transaction failed: 
        at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:431)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:461)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:277)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
        at com.sun.proxy.$Proxy159.loadUserByUsername(Unknown Source)
        at org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:114)
        ... 107 common frames omitted
Caused by: org.hibernate.TransactionException: JDBC begin transaction failed: 
        at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.begin(AbstractLogicalConnectionImplementor.java:73)
        at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.begin(LogicalConnectionManagedImpl.java:263)
        at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.begin(JdbcResourceLocalTransactionCoordinatorImpl.java:214)
        at org.hibernate.engine.transaction.internal.TransactionImpl.begin(TransactionImpl.java:56)
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction(HibernateJpaDialect.java:189)
        at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:380)
        ... 115 common frames omitted
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
        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)
        at com.mysql.jdbc.Util.getInstance(Util.java:408)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
        at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1184)
        at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1179)
        at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4770)
        at sun.reflect.GeneratedMethodAccessor325.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
        at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
        at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:79)
        at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
        at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81)
        at com.sun.proxy.$Proxy98.setAutoCommit(Unknown Source)
        at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.begin(AbstractLogicalConnectionImplementor.java:67)
        ... 120 common frames omitted
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 33,215,831 milliseconds ago.  The last packet sent successfully to the server was 7 milliseconds ago.

我真的不知道如何在重新启动后手动触发此问题，因此我们必须等待几个小时才能进入此状态。

似乎我们并不是唯一遇到此问题的人，但几天后仍未解决该问题，这有点令人沮丧。

我们需要尽快解决这个问题，有什么想法吗？

Answer 1

我找到了解决问题的方法。我们在 yaml 文件中所做的配置似乎无法正常工作，因此我必须以编程方式配置 HikariCP。

我删除了 yaml 文件中 hikari 的配置，之后数据源看起来像:

datasource:
    type: com.zaxxer.hikari.HikariDataSource

数据源配置如下所示:

@Configuration
public class DatasourceConfiguration {

    private static final Logger log = LoggerFactory.getLogger(DatasourceConfiguration.class);

    private final Environment env;

    public DatasourceConfiguration(Environment env) {
        this.env = env;
    }

    @Bean(name="primaryDatasource")
    @Primary
    @ConfigurationProperties(prefix="spring.datasource")
    public DataSource dataSource() {

        HikariConfig config = new HikariConfig();
        config.setDriverClassName("com.mysql.jdbc.Driver");
        config.setPoolName("JPAHikariCP");
        config.setAutoCommit(false);
        config.setConnectionTestQuery("SELECT 1");

        String profile = env.getProperty("spring.profiles.active");

        Optional<String> db = Optional.of("database");
        Optional<String> username = Optional.of("user");
        Optional<String> password = Optional.of("pass");

        if("test".equals(profile) || "dev".equals(profile)) {
            config.addDataSourceProperty("cachePrepStmts", true);
            config.addDataSourceProperty("prepStmtCacheSize", 250);
            config.addDataSourceProperty("prepStmtCacheSqlLimit", 2048);
            config.addDataSourceProperty("useServerPrepStmts", true);
        }

        config.setJdbcUrl(String.format("jdbc:mysql://localhost:3306/%s?useUnicode=true&characterEncoding=utf8&useSSL=false", db.get()));
        config.setUsername(username.get());
        config.setPassword(password.get());

        return new HikariDataSource(config);
    }



}

现在它可以工作了。