Mybatis破MySql8小时断线问题
来源:互联网 发布:excel随机数据生成 编辑:程序博客网 时间:2024/05/16 14:28
MySql有一个系统变量,如图:
以上数值,单位为秒。
mysql的连接允许的闲置时间。当超过闲置时间以后,database端就会将此连接单方面废弃。这时如果使用jdbc继续使用之前的连接,则会收到以下异常:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
### Cause: java.sql.SQLException: Could not retrieve transation read-only status server
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:
26
)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
111
)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
102
)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:
119
)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:
63
)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:
52
)
at com.sun.proxy.$Proxy8.getMonthlyChart(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:
620
)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:
727
)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:
303
)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:
208
)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:
52
)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:
241
)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:
208
)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:
220
)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:
122
)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:
501
)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:
170
)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:
98
)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:
950
)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:
116
)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:
408
)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:
1040
)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:
607
)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:
315
)
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:
722
)
Caused by: java.sql.SQLException: Could not retrieve transation read-only status server
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:
1086
)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:
989
)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:
975
)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:
920
)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:
951
)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:
941
)
at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:
3972
)
at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:
3943
)
at com.mysql.jdbc.PreparedStatement.checkReadOnlySafeStatement(PreparedStatement.java:
1258
)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:
1278
)
at sun.reflect.GeneratedMethodAccessor54.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
43
)
at java.lang.reflect.Method.invoke(Method.java:
601
)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:
62
)
at com.sun.proxy.$Proxy10.execute(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:
59
)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:
73
)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:
60
)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:
267
)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:
137
)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:
96
)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:
77
)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
108
)
...
28
more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was
85
,
659
,
759
milliseconds ago. The last packet sent successfully to the server was
85
,
659
,
775
milliseconds ago. is longer than the server configured value of
'wait_timeout'
. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values
for
client timeouts, or using the Connector/J connection property
'autoReconnect=true'
to avoid
this
problem.
at sun.reflect.GeneratedConstructorAccessor24.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:
45
)
at java.lang.reflect.Constructor.newInstance(Constructor.java:
525
)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:
411
)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:
1129
)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:
3988
)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:
2598
)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:
2778
)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:
2828
)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:
2777
)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:
1651
)
at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:
3966
)
...
44
more
Caused by: java.net.SocketException: Software caused connection abort: socket write error
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:
109
)
at java.net.SocketOutputStream.write(SocketOutputStream.java:
153
)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:
82
)
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:
140
)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:
3969
)
...
50
more
mysql允许设置闲置时间,默认是8小时,最大是1年。
大部分客户端都使用连接池以提高性能,如果用户访问量不大,连接池中的连接可能闲置时间超过数据库允许时间,数据库单方面断掉连接,而客户端却不知情。当下一个用户访问时,使用连接池中的连接,则会抛出上述异常。
解决方法可以是增大闲置时间。但这不是一个好方法。闲置时间是有上限的,在极端情况下,还是可能发生异常。此外,长时间保留闲置的连接,会降低数据库性能,消耗内存,最终耗尽数据库的连接数。所以不推荐增大闲置时间。
一般常用的解决方法是在使用一个长时间闲置的连接之前,对它ping一下,确保它还在正常工作。在mybatis自带连接池配置中,是这样做的:
1
2
3
4
5
6
7
8
9
10
11
12
<
environment
id
=
"development"
>
<
transactionManager
type
=
"jdbc"
/>
<
dataSource
type
=
"POOLED"
>
<
property
name
=
"driver"
value
=
"com.mysql.jdbc.Driver"
/>
<
property
name
=
"url"
value
=
"jdbc:mysql://localhost:3306/kpi?autoReconnect=true"
/>
<
property
name
=
"username"
value
=
"mysql"
/>
<
property
name
=
"password"
value
=
"mysql"
/>
<
property
name
=
"poolPingEnabled"
value
=
"true"
/>
<
property
name
=
"poolPingQuery"
value
=
"select now() from kpi.lastupdatedlog limit 1"
/>
<
property
name
=
"poolPingConnectionsNotUsedFor"
value
=
"3600000"
/>
</
dataSource
>
</
environment
>
配置连接池时,需要声明三个属性:
poolPingEnabled - 默认值是false,当值为true的时候,将开启ping机制。
poolPingQuery - 对数据库进行ping时所使用的sql。
poolPingConnectionsNotUsedFor - 默认值是0,单位是毫秒。我们不能在每次使用连接池之前,都使用ping机制,这会使每一条sql的执行,都要额外执行一次ping语句。所以使用此属性来避免这种不合理做法。我们只针对闲置时间超过某个时间的连接,进行ping。本例中的值为1小时,当从连接池中拿出的连接闲置超过1小时,才会对它进行ping。
0 0
- Mybatis破MySql8小时断线问题
- [linux]mysql8小时问题
- c3po MySQL8小时问题
- C3P0下Mysql8小时问题
- MySQL8小时连接超时断开问题
- MySQL8小时连接超时断开问题
- 解决MySql8小时自动断开连接问题。
- 解决mysql8小时自动断开问题
- Springboot jpa 解决Mysql8小时问题
- druid1.0.21版本源码研究之连接回收(分析解决mysql8小时断线)
- mySql8小时问题总结,DBCP,C3P0数据源配置方法。
- 解决mysql8小时无连接自动断掉问题
- C3P0官方对于MySQL8小时问题的解决方案
- mysql8小时自动关闭
- MySQL8小时的解决方案
- java数据库连接池proxool介绍及mysql8小时断开连接问题的说明
- 关于MySql8小时空闲后连接超时问题(testOnBorrow,logAbandoned等)
- mybatis+mysql8.0.7配置文件mybatis_config.xml
- BaseDao接口设计
- 布尔检索
- 堆栈的顺序存储结构的简单实现
- JVM内存分配
- handler消息处理机制
- Mybatis破MySql8小时断线问题
- MySql性能调优(五)采用合适的锁机制之表锁的演示
- 递归求数组和
- 机房收费系统之如何注册控件
- HD4185Oil Skimming
- DM3730 x-loader 分析 二
- 反射中的方法
- 2015年大一下第12周项目0-阅读程序
- 组件中双向映射