Mysql空闲连接超时自动断开问题记录
来源:互联网 发布:apache官网下载maven 编辑:程序博客网 时间:2024/05/16 05:38
Mysql数据库空闲连接默认8小时后会自动断开连接,此时由于业务处理使用C3P0连接池,业务在一个长时间休眠恢复后(其实是一个每天的定时任务)出现mysql 读写socket异常。异常栈信息如下:
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:** BEGIN NESTED EXCEPTION **java.io.EOFExceptionMESSAGE: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.STACKTRACE:java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost. at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1997) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2411) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2916) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723) at com.mysql.jdbc.Connection.execSQL(Connection.java:3283) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1467) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:1418) at com.temp.datamigration.tool.DatasourceUtil.executMysqlQuery(DatasourceUtil.java:188) at com.temp.datamigration.processor.recordprocessor.datacloud.UserSelfDefineProcessor.initUserSelfDefineSdsMode(UserSelfDefineProcessor.java:106) at com.temp.datamigration.processor.recordprocessor.datacloud.UserSelfDefineProcessor.init(UserSelfDefineProcessor.java:79) at com.temp.datamigration.processor.WearProcessor.initDataCloudProfileProcessors(WearProcessor.java:176) at com.temp.datamigration.processor.WearProcessor.init(WearProcessor.java:156) at com.temp.datamigration.bootstrap.Bootstrap.startMerge(Bootstrap.java:155) at com.temp.datamigration.bootstrap.Bootstrap.access$000(Bootstrap.java:43) at com.temp.datamigration.bootstrap.Bootstrap$1.run(Bootstrap.java:125) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:304) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745)
或者如下
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:** BEGIN NESTED EXCEPTION **java.net.SocketExceptionMESSAGE: Broken pipeSTACKTRACE:java.net.SocketException: Broken pipe at java.net.SocketOutputStream.socketWrite0(Native Method) at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113) at java.net.SocketOutputStream.write(SocketOutputStream.java:159) at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2744) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1612) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723) at com.mysql.jdbc.Connection.execSQL(Connection.java:3283) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1467) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:1418) at com.temp.datamigration.tool.DatasourceUtil.executMysqlQuery(DatasourceUtil.java:188) at com.temp.datamigration.processor.WearProcessor.obtailUserToBeMerged(WearProcessor.java:100) at com.temp.datamigration.processor.WearProcessor.init(WearProcessor.java:131) at com.temp.datamigration.bootstrap.Bootstrap.startMerge(Bootstrap.java:156) at com.temp.datamigration.bootstrap.Bootstrap.access$000(Bootstrap.java:43) at com.temp.datamigration.bootstrap.Bootstrap$1.run(Bootstrap.java:126) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:304) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745)
mysql server gone away
mysql wait_timeout变量说明
C3P0连接可用测试配置说明
下面是我们代码中初始化C3P0 DataSource的代码
ComboPooledDataSource dataSource = new ComboPooledDataSource();dataSource.setDriverClass("com.mysql.jdbc.Driver");dataSource.setJdbcUrl(mysqlConfig.getJdbcUrl());dataSource.setUser(mysqlConfig.getUsername());dataSource.setPassword(mysqlConfig.getPasswd());dataSource.setMinPoolSize(5);dataSource.setAcquireIncrement(5);dataSource.setMaxPoolSize(maxPoolSize);dataSource.setInitialPoolSize(2);dataSource.setAcquireRetryDelay(400);
即未设置连接任何Connection Testing 配置,都使用默认值
preferredTestQuery 默认值null,没有校验连接可用性的测试语句
testConnectionOnCheckin 默认值false,创建连接时不会校验连接可用性
testConnectionOnCheckout 默认值false,从连接池borrow时不会校验连接可用性
idleConnectionTestPeriod 默认值为0,即不进行空闲测试逐出处理。
而连接池的minPoolSize却被设置为5,连接池默认最小有5个空闲连接。尼玛,这5个连接在长时间(8小时)后被mysql server断连了。
修改方案1:修改mysql wait_timeout值超过24小时。但不能从根本上解决问题,而且一般现网的mysql不能随意修改,该方案不可行。
修改方案2:配置C3P0的Connection Testing ,由连接池自行校验连接可用性。
dataSource.setPreferredTestQuery("select 1");dataSource.setTestConnectionOnCheckout(true);dataSource.setTestConnectionOnCheckin(true);
测试验证:
1)set global wait_timeout=30;—-将mysql的wait_timeout全局变量设置为30s。
2)将业务(定时读取Mysql的任务)定时周期挑战为1分钟。
即可复现和验证该问题。
而现网的Tomcat业务服务器未出现过类似问题,而我们的Tomcat业务服务使用Tomcat dbcp数据库连接池中间件,并设置了validationQuery为”select 1”,但未配置其他Connection Testing。
查看DBCP配置参数说明
testOnBorrow、testOnConnect、testOnReturn、testWhileIdle的默认值也都是False,按道理也应该会存在连接断连的问题的啊。
查看源码发现 org.apache.tomcat.dbcp.dbcp.BasicDataSource.java
testOnBorrow默认值是true。。。。和文档不符。。。
- Mysql空闲连接超时自动断开问题记录
- mysql连接空闲8小时自动断开问题DBCP解决方案
- plsql、dblink连接Oracle数据库长时间空闲后自动断开及远程终端连接linux空闲超时后自动断开问题
- 数据库会自动清除掉超时的空闲连接造成中间件连接池中连接断开的问题
- mysql 默认八小时空闲自动断开连接
- oracle连接超时自动断开问题
- C3P0连接池配置解决MySQL连接的空闲时间超过8小时后自动断开连接的问题
- 连接MySql超时断开报错问题
- MySQL连接超时断开的问题
- mysql的8小时空闲,断开连接的问题
- 采用C3P0连接池解决spring+hibernate+mysql 访问空闲8小时后自动断开连接的问题
- mysql 8小时空闲后连接超时的问题
- 解决mysql 8小时空闲后连接超时的问题
- 解析:mysql 8小时空闲后连接超时的问题
- mysql 8小时空闲后连接超时的问题
- 解决MySQL 8小时空闲后连接超时的问题
- SecureCRT 超时自动断开连接
- [已解决]c3p0连接池超时自动断开mysql
- 安卓IPC基础手记【一】——序列化
- Jinyong Books
- POJ 2960 S-Nim 博弈论,SG函数
- Maven(三)-在eclipse中创建工程与基本命令
- 解决spring+springMVC+Hibernate+spring-data-jpa懒加载No session问题
- Mysql空闲连接超时自动断开问题记录
- leecode 解题总结:355. Design Twitter
- mysql单引号和双引号的用法
- Android注解使用之使用Support Annotations注解优化代码
- 将github上的项目整合到jitpack过程以及问题
- 命令集锦
- SpringMVC4.1+FastJson 自定义日期转换器
- 机制和策略与linux驱动的角色
- java 读取properties文件方法