求助:SQL Error: 17008, SQLState: 08003

异常: [pool-5-thread-1] WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 17008, SQLState: 08003

环境:使用的是框架是springboot+jpa ,配置的多数据源SqlServer+Oracle,测试服务器两个数据源都正常,生产服务器上Oracle数据库过几分钟会断,摘要错误就是标题,执行的查询语句是

 SELECT * FROM lydd.TB_IP_DEVICE_BJ WHERE ROWNUM <= 1

就查询一个表的第一条数据。
重点来了:如果定时查询间隔六分钟及六分钟以下查询一次不会断,间隔七分钟查询就会断具体的错误信息

Hibernate: SELECT * FROM lydd.TB_IP_DEVICE_BJ WHERE ROWNUM <= 1
10:00:00.000 [pool-5-thread-1] DEBUG org.hibernate.SQL - SELECT * FROM lydd.TB_IP_DEVICE_BJ WHERE ROWNUM <= 1
10:00:00.001 [pool-5-thread-1] WARN  org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 17008, SQLState: 08003
10:00:00.001 [pool-5-thread-1] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Closed Connection
10:00:00.001 [pool-5-thread-1] ERROR org.springframework.scheduling.support.TaskUtils$LoggingErrorHandler - Unexpected error occurred in scheduled task.
org.springframework.dao.DataAccessResourceFailureException: could not prepare statement; nested exception is org.hibernate.exception.JDBCConnectionException: could not prepare statement
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:257) ~[spring-orm-4.3.9.RELEASE.jar!/:4.3.9.RELEASE]
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:241) ~[spring-orm-4.3.9.RELEASE.jar!/:4.3.9.RELEASE]
        at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:488) ~[spring-orm-4.3.9.RELEASE.jar!/:4.3.9.RELEASE]
        at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59) ~[spring-tx-4.3.9.RELEASE.jar!/:4.3.9.RELEASE]
        at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213) ~[spring-tx-4.3.9.RELEASE.jar!/:4.3.9.RELEASE]
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147) ~[spring-tx-4.3.9.RELEASE.jar!/:4.3.9.RELEASE]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.9.RELEASE.jar!/:4.3.9.RELEASE]
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:673) ~[spring-aop-4.3.9.RELEASE.jar!/:4.3.9.RELEASE]
        at com.pcitc.rpo.task.dal.impl.TimedTaskRepositoryImpl$$EnhancerBySpringCGLIB$$33194383.taskTest(<generated>) ~[rpo-rpa-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT]
        at com.pcitc.rpo.task.bll.impl.TimedTaskServiceImpl.taskTest(TimedTaskServiceImpl.java:230) ~[rpo-rpa-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT]
        at com.pcitc.rpo.rpa.task.ManualSynchronizeController.taskTest(ManualSynchronizeController.java:315) ~[classes!/:1.0-SNAPSHOT]
        at sun.reflect.GeneratedMethodAccessor151.invoke(Unknown Source) ~[?:?]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_201]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_201]
        at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65) ~[spring-context-4.3.9.RELEASE.jar!/:4.3.9.RELEASE]
        at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) ~[spring-context-4.3.9.RELEASE.jar!/:4.3.9.RELEASE]
        at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81) ~[spring-context-4.3.9.RELEASE.jar!/:4.3.9.RELEASE]
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) ~[?:1.8.0_201]
        at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[?:1.8.0_201]
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) ~[?:1.8.0_201]
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) ~[?:1.8.0_201]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_201]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_201]
        at java.lang.Thread.run(Thread.java:748) [?:1.8.0_201]
Caused by: org.hibernate.exception.JDBCConnectionException: could not prepare statement
        at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:115) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:148) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1929) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1898) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.loader.Loader.doQuery(Loader.java:919) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.loader.Loader.doList(Loader.java:2617) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.loader.Loader.doList(Loader.java:2600) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.loader.Loader.list(Loader.java:2424) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:336) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1967) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:322) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:125) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at com.pcitc.rpo.task.dal.impl.TimedTaskRepositoryImpl.taskTest(TimedTaskRepositoryImpl.java:676) ~[rpo-rpa-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT]
        at com.pcitc.rpo.task.dal.impl.TimedTaskRepositoryImpl$$FastClassBySpringCGLIB$$c6e7d6cd.invoke(<generated>) ~[rpo-rpa-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT]
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-4.3.9.RELEASE.jar!/:4.3.9.RELEASE]
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:738) ~[spring-aop-4.3.9.RELEASE.jar!/:4.3.9.RELEASE]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.3.9.RELEASE.jar!/:4.3.9.RELEASE]
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) ~[spring-tx-4.3.9.RELEASE.jar!/:4.3.9.RELEASE]
        ... 18 more
Caused by: java.sql.SQLRecoverableException: Closed Connection
        at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:3560) ~[ojdbc6-11.2.0.3.jar!/:11.2.0.3.0]
        at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:3461) ~[ojdbc6-11.2.0.3.jar!/:11.2.0.3.0]
        at sun.reflect.GeneratedMethodAccessor153.invoke(Unknown Source) ~[?:?]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_201]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_201]
        at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126) ~[tomcat-jdbc-8.5.15.jar!/:?]
        at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108) ~[tomcat-jdbc-8.5.15.jar!/:?]
        at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:75) ~[tomcat-jdbc-8.5.15.jar!/:?]
        at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108) ~[tomcat-jdbc-8.5.15.jar!/:?]
        at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81) ~[tomcat-jdbc-8.5.15.jar!/:?]
        at com.sun.proxy.$Proxy102.prepareStatement(Unknown Source) ~[?:?]
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:148) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1929) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1898) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.loader.Loader.doQuery(Loader.java:919) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.loader.Loader.doList(Loader.java:2617) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.loader.Loader.doList(Loader.java:2600) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.loader.Loader.list(Loader.java:2424) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:336) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1967) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:322) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:125) ~[hibernate-core-5.0.12.Final.jar!/:5.0.12.Final]
        at com.pcitc.rpo.task.dal.impl.TimedTaskRepositoryImpl.taskTest(TimedTaskRepositoryImpl.java:676) ~[rpo-rpa-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT]
        at com.pcitc.rpo.task.dal.impl.TimedTaskRepositoryImpl$$FastClassBySpringCGLIB$$c6e7d6cd.invoke(<generated>) ~[rpo-rpa-1.0-SNAPSHOT.jar!/:1.0-SNAPSHOT]
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-4.3.9.RELEASE.jar!/:4.3.9.RELEASE]
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:738) ~[spring-aop-4.3.9.RELEASE.jar!/:4.3.9.RELEASE]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.3.9.RELEASE.jar!/:4.3.9.RELEASE]
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) ~[spring-tx-4.3.9.RELEASE.jar!/:4.3.9.RELEASE]
        ... 18 more

附上配置文件

spring.profiles.active=dev
#spring.profiles.active=prod
#spring.profiles.active=test

# HikariCP settings
# spring.datasource.hikari.*
spring.datasource.hikari.connection-timeout=60000

#spring.jpa
spring.jpa.hibernate.show_sql = true
spring.jpa.show_sql = true
spring.jpa.open-in-view= true
spring.jpa.properties.hibernate.proc.param_null_passing=true
hibernate.proc.param_null_passing=true
spring.jpa.hibernate.jdbc.batch_size = 30
spring.jpa.hibernate.cache.use_second_level_cache = false

spring.cache.ehcache.config=ehcache.xml

#超时设置
spring.mvc.async.request-timeout=360000
server.connection-timeout=360000

#引入的prod文件的配置
#生产部署端口
server.port=8091

sqlserver数据库地址
data_base_ip=xxx.xxx.xxx.xxx:60001
data_base_database=xxxx
data_base_username=xxxx
data_base_password=xxxx
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.url=jdbc:sqlserver://${data_base_ip};Database=${data_base_database}
spring.datasource.username=${data_base_username}
spring.datasource.password = ${data_base_password}


#ORACLE数据库地址
data_slave_base_ip=xxx.xxx.xxx.xxx:1521
data_slave_base_database=xxxx
data_slave_base_username=xxxx
data_slave_base_password=xxxx
spring.slave.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.slave.datasource.url=jdbc:oracle:thin:@${data_slave_base_ip}:${data_slave_base_database}
spring.slave.datasource.username=${data_slave_base_username}
spring.slave.datasource.password = ${data_slave_base_password}

#从数据源连接池配置
spring.slave.datasource.max-active=10
spring.slave.datasource.max-wait=10000
spring.slave.datasource.min-idle=5
spring.slave.datasource.initial-size=5
spring.slave.datasource.validation-query=select 1 from dual
spring.slave.datasource.test-on-borrow=true
spring.slave.datasource.test-while-idle=true
spring.slave.datasource.validation-timeout=2000

#30s运行空闲连接回收器
spring.slave.datasource.time-between-eviction-runs-millis=30000
#空闲连接5分钟后被回收
spring.slave.datasource.min-evictable-idle-time-millis=18000000
spring.slave.datasource.num-tests-per-eviction-run=3
spring.slave.datasource.remove-abandoned=true
spring.slave.datasource.remove-abandoned-timeout=180

spring.datasource.max-active=500

DataSourcesConfig

@Bean("master")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean("slave")
    @ConfigurationProperties(prefix = "spring.slave.datasource")
    public DataSource dataSourceSlave(){
        return DataSourceBuilder.create().build();
    }

主数据源配置类

	@Autowired
    @Qualifier("master")
    private DataSource dataSourceMaster;

    @Autowired(required=false)
    private JpaProperties jpaProperties;

    @Bean("entityManagerMaster1")
    @Primary
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryBean(builder).getObject().createEntityManager();
    }

    @Bean("entityManagerFactoryMaster1")
    @Primary
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean(EntityManagerFactoryBuilder builder) {
        return builder.dataSource(dataSourceMaster)
                .properties(getVendorProperties(dataSourceMaster))
                .packages("com.test.test.pojo.test")
                //持久化单元名称,当存在多个EntityManagerFactory时,需要制定此名称
                .persistenceUnit("masterPersistenceUnit")
                .build();

    }

    @Bean("transactionManagerMaster1")
    @Primary
    public PlatformTransactionManager transactionManager(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryBean(builder).getObject());
    }

    private Map<String, String> getVendorProperties(DataSource dataSource) {
        return jpaProperties.getHibernateProperties(dataSource);
    }

从数据源配置类

	@Autowired
    @Qualifier("slave")
    private DataSource dataSourceSlave;

    @Autowired(required=false)
    private JpaProperties jpaProperties;

    @Bean(name="entityManagerMaster2",value = "entityManagerMaster2")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        EntityManager entityManager = entityManagerFactoryBean(builder).getObject().createEntityManager();
        return entityManager;
    }

    @Bean("entityManagerFactoryMaster2" )
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean(EntityManagerFactoryBuilder builder) {
        return builder.dataSource(dataSourceSlave)
                .properties(getVendorProperties(dataSourceSlave))
                .packages("com.test.test.pojo.test1")
                //持久化单元名称,当存在多个EntityManagerFactory时,需要制定此名称
                .persistenceUnit("slavePersistenceUnit")
                .build();

    }

    @Bean("transactionManagerMaster2")
    public PlatformTransactionManager transactionManager(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryBean(builder).getObject());
    }

    private Map<String, String> getVendorProperties(DataSource dataSource) {
        return jpaProperties.getHibernateProperties(dataSource);
    }

望各位大佬帮忙查看!!!

  • 0
    点赞
  • 0
    收藏
  • 打赏
    打赏
  • 3
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:游动-白 设计师:我叫白小胖 返回首页
评论 3

打赏作者

Fall in your eyes

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值