求助大佬：多数据源下 postgres 重启引起 springboot 应用数据库连接断开

2022-04-21 19:08:54 +08:00
 powinds

这是我的多数据源配置

spring:
  servlet:
    multipart:
      max-file-size: 64MB
      max-request-size: 64MB
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true
    properties:
      hibernate:
        naming:
          physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
        primary-dialect: org.hibernate.dialect.PostgreSQLDialect
        secondary-dialect: org.hibernate.dialect.PostgreSQLDialect
        temp:
          use_jdbc_metadata_defaults: false
    database-platform: org.hibernate.dialect.PostgreSQL10Dialect
  primary:
    datasource:
      driver-class-name: org.postgresql.Driver
      jdbc-url: jdbc:postgresql://postgres-0.postgres.postgres:5432/platform
      username: postgres
      password: postgres
      # JPA 重连
      test-on-borrow: true
      validation-query: SELECT 1
      # 连接池名称
      pool-name: HikariPool-1
      ## 最小空闲连接数
      minimum-idle: 10
      ## 最大连接数
      maximum-pool-size: 20
      ## 空闲连接存活最大时间
      idle-timeout: 30000
      ## 最长生命周期
      max-lefetime: 30000
      ## 连接超时时间
      connection-timeout: 30000
  secondary:
    datasource:
      driver-class-name: org.postgresql.Driver
      jdbc-url: jdbc:postgresql://postgres-0.postgres.postgres:5432/keycloak
      username: postgres
      password: postgres
      # JPA 重连
      test-on-borrow: true
      validation-query: SELECT 1
      # 连接池名称
      pool-name: HikariPool-1
      ## 最小空闲连接数
      minimum-idle: 10
      ## 最大连接数
      maximum-pool-size: 20
      ## 空闲连接存活最大时间
      idle-timeout: 30000
      ## 最长生命周期
      max-lefetime: 30000
      ## 连接超时时间
      connection-timeout: 30000

这是我的 JAVA 多数据源

@Configuration
public class DataSourceConfig {
    private static Logger logger = LoggerFactory.getLogger(DataSourceConfig.class);

    @Bean(name = "primaryDataSource")
    @Qualifier("primaryDataSource")
    @ConfigurationProperties(prefix = "spring.primary.datasource")
    @Primary
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondaryDataSource")
    @Qualifier("secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.secondary.datasource")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }
}

public class PrimaryConfig {
    @Autowired
    @Qualifier("primaryDataSource")
    private DataSource primaryDataSource;

    @Primary
    @Bean(name = "entityManagerPrimary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return Objects.requireNonNull(entityManagerFactoryPrimary(builder).getObject()).createEntityManager();
    }

    @Primary
    @Bean(name = "entityManagerFactoryPrimary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {
        return builder.dataSource(primaryDataSource)
                .properties(getVendorProperties(primaryDataSource))
                .packages("net.skycloud.platform.licensemanager.model", "net.skycloud.platform.mail.model",
                        "net.skycloud.platform.perm.model", "net.skycloud.platform.model")
                .persistenceUnit("primaryPersistenceUnit")
                .build();
    }

    @Autowired
    private JpaProperties jpaProperties;
    @Autowired
    private HibernateProperties hibernateProperties;
    private Map<String, Object> getVendorProperties() {
        return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
    }

    private Map<String, String> getVendorProperties(DataSource dataSource) {
        Map<String, String> jpaProperties = new HashMap<>(16);
        jpaProperties.put("hibernate.hbm2ddl.auto", "update");
        jpaProperties.put("hibernate.show_sql", "true");
        //        jpaProperties.put("hibernate.format_sql", "true");
        jpaProperties.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
        //        jpaProperties.put("hibernate.dialect", "net.skycloud.platform.common.config.PgDialect");
        //        jpaProperties.put("hibernate.current_session_context_class",
        //        "org.springframework.orm.hibernate5.SpringSessionContext");
        return jpaProperties;
    }

    @Primary
    @Bean(name = "transactionManagerPrimary")
    public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
    }
}

public class SecondaryConfig {
    @Autowired
    @Qualifier("secondaryDataSource")
    private DataSource secondaryDataSource;

    @Bean(name = "entityManagerSecondary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return Objects.requireNonNull(entityManagerFactorySecondary(builder).getObject()).createEntityManager();
    }

    @Bean(name = "entityManagerFactorySecondary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary(EntityManagerFactoryBuilder builder) {
        return builder.dataSource(secondaryDataSource)
                .properties(getVendorProperties(secondaryDataSource))
                .packages("net.skycloud.platform.user.keycloakmodel")
                .persistenceUnit("secondaryPersistenceUnit")
                .build();
    }

    @Autowired
    private JpaProperties jpaProperties;

    @Autowired
    private HibernateProperties hibernateProperties;

    @Autowired
    private Environment env;

    private Map<String, Object> getVendorProperties() {
        return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
    }

    private Map<String, String> getVendorProperties(DataSource dataSource) {
        Map<String, String> jpaProperties = new HashMap<>(16);
        jpaProperties.put("hibernate.hbm2ddl.auto", "none");
        jpaProperties.put("hibernate.show_sql", "true");
        //        jpaProperties.put("hibernate.format_sql", "true");
        //        jpaProperties.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
        jpaProperties.put("hibernate.dialect", "net.skycloud.platform.common.config.PgDialect");
        //        jpaProperties.put("hibernate.current_session_context_class",
        //        "org.springframework.orm.hibernate5.SpringSessionContext");
        return jpaProperties;
    }

    //用来作为数据库事务回滚的限定词
    //@Transactional(rollbackFor = OAPMException.class, value = "transactionManagerSecondary")
    //事务管理器
    @Bean(name = "transactionManagerSecondary")
    PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject());
    }
}

当我再 k8s 上重启 postgres 时，访问任意接口报错如下：

javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not prepare statement
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
	at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1626)
	at org.hibernate.query.internal.AbstractProducedQuery.getSingleResult(AbstractProducedQuery.java:1665)
	at net.skycloud.platform.user.repository.UserDao.findOneByUsername(UserDao.java:121)
	at net.skycloud.platform.user.service.UserService.getUserInfo(UserService.java:184)
	at net.skycloud.platform.user.service.UserService$$FastClassBySpringCGLIB$$e1720440.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:783)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753)
	at org.springframework.validation.beanvalidation.MethodValidationInterceptor.invoke(MethodValidationInterceptor.java:123)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:698)
	at net.skycloud.platform.user.service.UserService$$EnhancerBySpringCGLIB$$a191bfb.getUserInfo(<generated>)
	at net.skycloud.platform.user.api.rest.UserController.getCurrentUser(UserController.java:358)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:645)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:750)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:96)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:889)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.hibernate.exception.GenericJDBCException: could not prepare statement
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:151)
	at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:2122)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2059)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2037)
	at org.hibernate.loader.Loader.doQuery(Loader.java:956)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:357)
	at org.hibernate.loader.Loader.doList(Loader.java:2868)
	at org.hibernate.loader.Loader.doList(Loader.java:2850)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2682)
	at org.hibernate.loader.Loader.list(Loader.java:2677)
	at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338)
	at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2186)
	at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1204)
	at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:177)
	at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1617)
	... 61 common frames omitted
Caused by: java.sql.SQLException: Connection is closed
	at com.zaxxer.hikari.pool.ProxyConnection$ClosedConnection.lambda$getClosedConnection$0(ProxyConnection.java:515)
	at com.sun.proxy.$Proxy155.prepareStatement(Unknown Source)
	at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:337)
	at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:149)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:176)
	... 76 common frames omitted

试了很多办法没有解决，各位大佬我该怎么解决这个问题？

1717 次点击
所在节点    Java
3 条回复
angryfish
2022-04-22 09:23:37 +08:00
把 HikariCP 换掉，用下 druid 之类的？
或者试试 connection-test-query: SELECT 1 ，每隔一段段时间会去检查数据库连接的可用性
angryfish
2022-04-22 09:29:59 +08:00
@angryfish 应该是 test-while-idle
powinds
2022-04-22 14:10:42 +08:00
#初始化连接
initial-size: 10
#最大空闲连接
max-idle: 20
#最小空闲连接
min-idle: 5
#最大连接数量
max-active: 50
#是否在自动回收超时连接的时候打印连接的超时错误
log-abandoned: true
#是否自动回收超时连接
remove-abandoned: true
#超时时间(以秒数为单位)
remove-abandoned-timeout: 180
max-wait: 1000
test-while-idle: true
#检测数据库的查询语句
validation-query: select 1 from dual
test-on-borrow: true
#每隔五分钟检测空闲超过 10 分钟的连接
min-evictable-idle-time-millis: 600000
time-between-eviction-runs-millis: 300000
又加了一大堆，但是没用，准备打几个连接数的日志看看

这是一个专为移动设备优化的页面（即为了让你能够在 Google 搜索结果里秒开这个页面），如果你希望参与 V2EX 社区的讨论，你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/848424

V2EX 是创意工作者们的社区，是一个分享自己正在做的有趣事物、交流想法，可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX