java 如何解决java.sql.SQLNonTransientConnectionException：无法读取结果集：连接重置？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25054095/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-11-02 07:14:21 来源:igfitidea点击:
How to solve java.sql.SQLNonTransientConnectionException: Could not read resultset: Connection reset?
提问by Biscuit128
I am getting an exception when calling database code after a period of inactivity
在一段时间不活动后调用数据库代码时出现异常
java.sql.SQLNonTransientConnectionException: Could not read resultset: Connection reset
at org.mariadb.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:136)
at org.mariadb.jdbc.internal.SQLExceptionMapper.throwException(SQLExceptionMapper.java:106)
at org.mariadb.jdbc.MySQLStatement.executeQueryEpilog(MySQLStatement.java:264)
at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:288)
at org.mariadb.jdbc.MySQLStatement.executeQuery(MySQLStatement.java:302)
at org.mariadb.jdbc.MySQLStatement.executeQuery(MySQLStatement.java:361)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at com.vimba.database.DBFactory.attemptLoginWithTempPasswordDetails(DBFactory.java:181)
at com.vimba.database.DBFactory.authenticate(DBFactory.java:131)
at com.vimba.service.ExposedFunctions.login(ExposedFunctions.java:88)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at com.sun.xml.ws.api.server.InstanceResolver.invoke(InstanceResolver.java:210)
at com.sun.xml.ws.server.InvokerTube.invoke(InvokerTube.java:132)
at com.sun.xml.ws.server.sei.EndpointMethodHandler.invoke(EndpointMethodHandler.java:241)
at com.sun.xml.ws.server.sei.SEIInvokerTube.processRequest(SEIInvokerTube.java:74)
at com.sun.xml.ws.api.pipe.Fiber.__doRun(Fiber.java:559)
at com.sun.xml.ws.api.pipe.Fiber._doRun(Fiber.java:518)
at com.sun.xml.ws.api.pipe.Fiber.doRun(Fiber.java:503)
at com.sun.xml.ws.api.pipe.Fiber.runSync(Fiber.java:400)
at com.sun.xml.ws.server.WSEndpointImpl.process(WSEndpointImpl.java:226)
at com.sun.xml.ws.transport.http.HttpAdapter$HttpToolkit.handle(HttpAdapter.java:375)
at com.sun.xml.ws.transport.http.HttpAdapter.handle(HttpAdapter.java:175)
at com.sun.xml.ws.transport.http.servlet.ServletAdapter.handle(ServletAdapter.java:134)
at com.sun.xml.ws.transport.http.servlet.WSServletDelegate.doPost(WSServletDelegate.java:159)
at com.sun.xml.ws.transport.http.servlet.WSServlet.doPost(WSServlet.java:49)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:754)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:295)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:149)
at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:169)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:145)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:97)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:102)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:336)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:653)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:920)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.mariadb.jdbc.internal.common.QueryException: Could not read resultset: Connection reset
at org.mariadb.jdbc.internal.mysql.MySQLProtocol.getResult(MySQLProtocol.java:926)
at org.mariadb.jdbc.internal.mysql.MySQLProtocol.executeQuery(MySQLProtocol.java:991)
at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:281)
... 40 more
Caused by: java.net.SocketException: Connection reset
I have tried to change the /etc/my.cnf to add a wait_timeout of a week but it has not made a difference;
我尝试更改 /etc/my.cnf 以添加一周的 wait_timeout，但没有任何区别；
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld/mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
# Currently, there are mariadb and community-mysql packages in Fedora.
# This particular config file is included in respective RPMs of both of them,
# so the following settings are general and will be also used by both of them.
# Otherwise the RPMs would be in conflict.
# Settings for particular implementations like MariaDB are then
# defined in appropriate sections; for MariaDB server in [mariadb] section in
# /etc/my.cnf.d/server.cnf (part of mariadb-server).
# It doesn't matter that we set these settings only for [mysqld] here,
# because they will be read and used in mysqld_safe as well.
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqld_safe]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
#one week timeout to see if exception goes
wait_timeout=604800
event_scheduler=on
/etc/my.cnf (END)
Does anyone know how to solve this issue?
有谁知道如何解决这个问题？
采纳答案by Biscuit128
in the end i gave up and just wrote a method to ping the database every hour
最后我放弃了，只写了一个每小时 ping 数据库的方法
public static void pingServer(final BasicDataSource source){
final Thread serverPing = new Thread(new Runnable() {
@Override
public void run() {
Connection conn = null;
try {
while(!Thread.currentThread().isInterrupted()){
String sql = "/* ping */ SELECT 1";
PreparedStatement st = null;
conn = source.getConnection();
st = conn.prepareStatement(sql);
st.executeUpdate(sql);
LOGGER.info("Database connection test successful");
DatabaseUtilities.closeConnection(conn);
TimeUnit.HOURS.sleep(1);
}
} catch (SQLException | InterruptedException e) {
LOGGER.error("Unable to continue pinging database server", e);
DatabaseUtilities.closeConnection(conn);
}
finally{
DatabaseUtilities.closeConnection(conn);
}
}
});
serverPing.setDaemon(true);
serverPing.start();
}
回答by isalgueiro
You can configure DBCPto do that connection check for you. Add something like this to your config
您可以配置DBCP来为您执行连接检查。将这样的内容添加到您的配置中
validationQuery="/* ping */ SELECT 1"
testOnBorrow="true"
DBCP will check if the connection is alive every time you get a connection from the pool, dropping dead connections and creating new ones when needed
每次从池中获取连接时，DBCP 都会检查连接是否处于活动状态，删除死连接并在需要时创建新连接
