V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
powinds
V2EX  ›  Java

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

  •  
  •   powinds · 169 天前 · 804 次点击
    这是一个创建于 169 天前的主题,其中的信息可能已经有所发展或是发生改变。

    这是我的多数据源配置

    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
    

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

    3 条回复    2022-04-22 14:10:42 +08:00
    angryfish
        1
    angryfish  
       168 天前
    把 HikariCP 换掉,用下 druid 之类的?
    或者试试 connection-test-query: SELECT 1 ,每隔一段段时间会去检查数据库连接的可用性
    angryfish
        2
    angryfish  
       168 天前
    @angryfish 应该是 test-while-idle
    powinds
        3
    powinds  
    OP
       168 天前
    #初始化连接
    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
    又加了一大堆,但是没用,准备打几个连接数的日志看看
    关于   ·   帮助文档   ·   API   ·   FAQ   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   941 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 37ms · UTC 19:18 · PVG 03:18 · LAX 12:18 · JFK 15:18
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.