HouseKeeper 串連active time 過長，與mysql斷開，的解決方案

[WARN ] HouseKeeper 2010-05-13 11:52:17,781      org.logicalcobwebs.proxool.MySql     - #0005 was active for 3634735 milliseconds and has been removed automaticaly. The Thread responsible was named 'http-7085-Processor22', and the last SQL it performed is 'insert into workitem (ACTOR_ID, ACTOR_NAME, COMMENTS, CREATED_TIME, END_TIME, SIGNED_TIME, STATE, TASKINSTANCE_ID, TYPE_NAME) values (76, '張三', NULL, '13-五月-2010.11:51:30', NULL, NULL, 0, 295, '審核'); '.
[INFO ] http-7085-Processor22 2010-05-13 11:52:54,687 com.fsti.infopriv.service.impl.WorkflowServiceImpl     - 簡訊發送失敗1
[INFO ] http-7085-Processor22 2010-05-13 11:52:54,687 com.fsti.infopriv.service.impl.WorkflowServiceImpl     - 工單派發 --> 76 : 張三 成功
[ERROR] http-7085-Processor22 2010-05-13 11:52:55,296 org.springframework.transaction.interceptor.TransactionInterceptor     - Application exception overridden by rollback exception
org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [update taskinstance set ACTIVITY_ID=?, ASSIGNMENT_STRATEGY=?, BIZ_TYPE=?, CREATED_TIME=?, DISPLAY_NAME=?, END_TIME=?, EXPIRED_TIME=?, FROM_TASK_ID=?, NAME=?, PROCESS_ID=?, PROCESSINSTANCE_ID=?, SN=?, SN_NAME=?, STARTED_TIME=?, STATE=?, TARGET_TASK_ID=?, TASK_ID=?, TASK_TYPE=? where ID=?]; SQL state [null]; error code [0]; could not update: [com.fsti.infopriv.model.TaskInstance#294]; nested exception is org.hibernate.exception.GenericJDBCException: could not update: [com.fsti.infopriv.model.TaskInstance#294]
    at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:642)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:95)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:212)
    at org.springframework.orm.jpa.JpaAccessor.translateIfNecessary(JpaAccessor.java:152)
    at org.springframework.orm.jpa.JpaTemplate.execute(JpaTemplate.java:189)
    at org.springframework.orm.jpa.JpaTemplate.executeFind(JpaTemplate.java:151)
    at org.springframework.orm.jpa.JpaTemplate.find(JpaTemplate.java:311)
    at com.fsti.commons.dao.template.JpaSpringDaoTemplate.findByHql(JpaSpringDaoTemplate.java:156)

 

 

 

 

mysql>show variables like '%timeout';

列印結果如下：

+----------------------------+-------+
| Variable_name | Value |

+----------------------------+-------+
| connect_timeout | 5 |
|
delayed_insert_timeout | 300 |
| interactive_timeout | 28800 |
|
net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout |
3600 |
| wait_timeout | 28800 |
+----------------------------+-------+

interactive_timeout
需在mysql_connect()設定CLIENT_INTERACTIVE選項後起作用，並被賦值為wait_timeout；

mysql>set
wait_timeout = 10; 對當前互動連結有效；
mysql>set interactive_timeout = 10;
對後續起的互動連結有效；

該逾時時間單位是秒，從變數從上次SQL執行後算起；當前空閑若超過該時間，則也會被強制斷開。

interactive_timeout(常用)

　　The number of seconds the server waits for activity on an interactive
connection before closing it. An interactive client is defined as a client that
uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also
wait_timeout.

　　譯解：用戶端和伺服器互動的空閑逾時時間。該系統變數僅當用戶端串連伺服器時設定了“MYSQL_CLIENT_INTERACTIVE”標誌才生效，例如：

　　//啟用MYSQL_CLIENT_INTERACTIVE模式，串連資料庫伺服器

　　$link = mysql_connect($host, $user, $pwd, false,
MYSQL_CLIENT_INTERACTIVE);