Mysql8.0.13 + Hiberante3 + Spring3.0.5 程序运行一段时间后无法获取连接

[xjwxj526]

mysql 最大连接设置成5000
<bean id="caimiDataSource" class="com.alibaba.druid.pool.DruidDataSource"
init-method="init" destroy-method="close">
<property name="url" value="${jdbc.driverUrl}" />
<property name="username" value="${jdbc.user}" />
<property name="password" value="${jdbc.password}" />
<!-- 初始化连接数目 -->
<property name="initialSize" value="100" />
<!-- 连接池中最少空闲maxIdle个连接 -->
<property name="minIdle" value="200" />
<!-- 连接池的最大数据库连接数 -->
<property name="maxActive" value="3000" />
<!-- 连接池中连接用完时,新的请求等待时间,毫秒 -->
<property name="maxWait" value="5000" />
<!-- timeBetweenEvictionRunsMillis毫秒检查一次连接池中空闲的连接,把空闲时间超过minEvictableIdleTimeMillis毫秒的连接断开,直到连接池中的连接数到minIdle为止 -->
<property name="timeBetweenEvictionRunsMillis" value="600000" />
<!-- 连接池中连接可空闲的时间,毫秒 -->
<property name="minEvictableIdleTimeMillis" value="300000" />

<property name="validationQuery" value="SELECT 'x'" />
<property name="testWhileIdle" value="true" />
<property name="testOnBorrow" value="true" />
<property name="testOnReturn" value="false" />
<!-- 是否清理removeAbandonedTimeout秒没有使用的活动连接,清理后并没有放回连接池 -->
<property name="removeAbandoned" value="true" />
<!-- 活动连接的最大空闲时间 ,秒-->
<property name="removeAbandonedTimeout" value="600" />
<property name="logAbandoned" value="true" /> <!-- 关闭abanded连接时输出错误日志 -->
<!-- minEvictableIdleTimeMillis,removeAbandonedTimeout这两个参数针对的连接对象不一样,
minEvictableIdleTimeMillis针对连接池中的连接对象,
removeAbandonedTimeout针对未被close的活动连接。
-->
</bean>
<!-- 定义JdbcTemplate Bean(线程安全,执行底层sql语句) -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!-- 设置数据源 -->
<property name="dataSource" ref="caimiDataSource" />
<!-- 设置jdbcTemplate创建的statement查询数据时最大的超时时间,默认0 -->
<property name="queryTimeout" value="0" />
<!-- 设置底层ResultSet每次从数据库返回的行数,默认0,Oracle默认100 -->
<property name="fetchSize" value="0" />
<!-- 设置底层ResultSet从数据库返回的最大行数,默认0 -->
<property name="maxRows" value="0" />
<!-- 是否忽略sql警告信息,默认true,false JdbcTemplate将会抛出SQLWarningException -->
<property name="ignoreWarnings" value="true" />
<property name="nativeJdbcExtractor" ref="nativeJdbcExtractor" />
</bean>

<!-- 定义封装了jdbc操作数据的dao -->
<bean id="simpleJdbcDao" class="statistics.dao.jdbc.SimpleJdbcDao">
<property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>

<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource">
<ref bean="caimiDataSource" />
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</prop>
<prop key="hibernate.show_sql">false</prop>
<prop key="hibernate.format_sql">true</prop>
<prop key="hibernate.use_sql_comments">true</prop>
<prop key="hibernate.jdbc.fetch_size">50</prop>
<prop key="hibernate.max_fetch_depth">5</prop>
<prop key="hibernate.jdbc.batch_size">25</prop>
<prop key="hibernate.cache.use_query_cache">true</prop>
<prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop>
<prop key="hibernate.jdbc.use_streams_for_binary">true</prop>
<prop key="hibernate.query.factory_class">org.hibernate.hql.ast.ASTQueryTranslatorFactory</prop>
<prop key="hibernate.connection.release_mode">after_transaction</prop>
</props>
</property>
<property name="mappingDirectoryLocations">
<list>
<value>classpath:/statistics/model</value>
</list>
</property>
</bean>

每2分钟执行 long size = jdbcTemplate.countRows(sql.toString());
sql如下
select count(1) from tabA ta, tabB tb
where ta.id = tb.id
and (ta.time >= '2018-01-01' or tb.time >= '2018-01-01')
这个例子sql是可以运行的。但
程序大概运行了40分钟就开始报如下错误，再也无法获取连接。

[2018-12-14 13:26:03,145] [DefaultQuartzScheduler_Worker-4] (QuartzScheduler.java:2358) ERROR org.quartz.core.ErrorLogger - Job (DEFAULT.perMinuteTaskMethod threw an exception.
org.quartz.SchedulerException: Job threw an unhandled exception. [See nested exception: org.springframework.scheduling.quartz.JobMethodInvocationFailedException: Invocation of method 'execute' on target class [class job.PerMinuteTask] failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Cannot open connection]
at org.quartz.core.JobRunShell.run(JobRunShell.java:234)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549)
Caused by: org.springframework.scheduling.quartz.JobMethodInvocationFailedException: Invocation of method 'execute' on target class [class job.PerMinuteTask] failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Cannot open connection
at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:269)
at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:86)
at org.quartz.core.JobRunShell.run(JobRunShell.java:223)
... 1 more
Caused by: org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Cannot open connection
at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:596)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371)
at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:335)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at com.sun.proxy.$Proxy3.refreshGameList(Unknown Source)
at job.PerMinuteTask.execute(PerMinuteTask.java:18)
at sun.reflect.GeneratedMethodAccessor7.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:273)
at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:260)
... 3 more
Caused by: org.hibernate.exception.GenericJDBCException: Cannot open connection
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449)
at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:142)
at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:85) at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1354)
at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:555)
... 17 more
Caused by: com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 60000, active 0, maxActive 3000, creating 1, createElapseMillis 3300128
at com.alibaba.druid.pool.DruidDataSource.getConnectionInternal(DruidDataSource.java:1536)
at com.alibaba.druid.pool.DruidDataSource.getConnectionDirect(DruidDataSource.java:1272)
at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1252)
at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1242)
at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:89)
at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
... 22 more
[2018-12-14 13:26:04,065] [DefaultQuartzScheduler_Worker-6] (JDBCExceptionReporter.java:100) WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: null
[2018-12-14 13:26:04,066] [DefaultQuartzScheduler_Worker-6] (JDBCExceptionReporter.java:101) ERROR org.hibernate.util.JDBCExceptionReporter - wait millis 60000, active 0, maxActive 3000, creating 1, createElapseMillis 3301053
[2018-12-14 13:26:04,066] [DefaultQuartzScheduler_Worker-6] (JDBCExceptionReporter.java:100) WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: null
[2018-12-14 13:26:04,066] [DefaultQuartzScheduler_Worker-6] (JDBCExceptionReporter.java:101) ERROR org.hibernate.util.JDBCExceptionReporter - wait millis 60000, active 0, maxActive 3000, creating 1, createElapseMillis 3301053

[oldmee]

这个问题我也碰到过，我是过两三天服务器断开连接，网上的方法都不灵，我就用一个定时任务每天凌晨重启一下服务。

[lifewell1]

事务管理没配置，连接使用完没关闭