HouseKeeper 连接active time 过长，与mysql断开，的解决方法

最新推荐文章于 2024-09-11 00:38:40 发布

lfp0202

最新推荐文章于 2024-09-11 00:38:40 发布

阅读量1k

点赞数

分类专栏： java web 文章标签： mysql exception 数据库服务器 hibernate insert variables

本文链接：https://blog.youkuaiyun.com/lfp0202/article/details/5589691

版权

java web 专栏收录该内容

15 篇文章

订阅专栏

本文探讨了MySQL中连接超时配置项`interactive_timeout`和`wait_timeout`的使用及影响，通过设置`interactive_timeout`和`wait_timeout`来避免数据库连接因长时间无操作而被关闭的问题，同时提供了具体的SQL命令进行配置修改。此外，文章还介绍了MySQL连接参数的调整策略，以提升应用程序的稳定性与响应速度。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[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);