mysql中断自动连接不上_mysql 连接池断开后自动连接

MySQL对所有连接的有效时间默认为28800秒，正好8小时，也就是说，如果一个连接8小时没有请求和操作，就会自动断开；但是对于

Hibernate来说，它的连接池并不知道它所管理的连接中是否有被MySQL断开的。如果一个程序要使用数据库连接，而Hibernte的连接池分配

一个已经被MySQL断开了的给程序使用，那么便会出现错误.以下是proxool连接池的解决方案

数据库连接池proxool(0.91)，它有两个属性：一个是test-before-use

，还有一个是test-after-use，这两个属性就是在使用前和使用后都要进行对连接的检查，如果连接无效就扔掉再创建一个新的连接，它们的解释如下：

test-before-use

:

If

you set this to true then each connection is tested (with whatever is

defined in house-keeping-test-sql) before being served. If a connection

fails then it is discarded and another one is picked. If all connections

fail a new one is built. If that one fails then you get an SQLException

saying so.

test-after-use:

If you set this to true then each

connection is tested (with whatever is defined in

house-keeping-test-sql) after it is closed (that is, returned to the

connection pool). If a connection fails then it is discarded.

由于项目是spring+ibatis+struts

有关proxool的配置片段如下

web.xml

ServletConfigurator

org.logicalcobwebs.proxool.configuration.ServletConfigurator

xmlFile

WEB-INF/proxool.xml

1

proxool.xml放在WEB-INF下

dbname

com.mysql.jdbc.Driver

100

10

90000

10

5

true

关键代码

true

关键代码

select 1 from dual

关键代码

spring的applicationContext.xml

class="org.springframework.jdbc.datasource.DriverManagerDataSource">

value="org.logicalcobwebs.proxool.ProxoolDriver" />

和proxool.xml中的alias相同

测试OK

同理其他连接池的解决方案

DBCP增加以下配置信息:

//set to 'selecｔ 1'

validationQuery =

"selecｔ 1"

//set to 'true'

testWhileIdle =

"true"

//some positive integer

timeBetweenEvictionRunsMillis =

3600000

//失效检查线程运行时间间隔，如果小于等于0，不会启动检查线程

//set to something smaller than 'wait_timeout'

minEvictableIdleTimeMillis =

18000000

//大于0 ，进行连接空闲时间判断，或为0，对空闲的连接不进行验证

//if you don't mind a hit for every getConnection(), set to "true"

testOnBorrow =

"true"

C3P0增加以下配置信息:

//获取connnection时测试是否有效

testConnectionOnCheckin = true

//自动测试的table名称

automaticTestTable=C3P0TestTable

//set to something much less than wait_timeout, prevents connections from going stale

idleConnectionTestPeriod =

18000

//置一段时间后连接池自动测试执行，保持连接开放，

//set to something slightly less than wait_timeout, preventing 'stale' connections from being handed out

maxIdleTime =

25000

//if you can take the performance 'hit', set to "true"

testConnectionOnCheckout =

true

分享到：

2010-08-13 16:14

浏览 2783

评论