java 如何重置 JDBC 连接池

How to reset the JDBC Connection Pool

提问by Trenin

I have a problem where I get tomcat exceptions after I reset my MySQL database at the request of the user via a tomcat web app. I've tried to break this up into the setup, problem, and my analysis so far to help anyone trying to read this.

我有一个问题，在我通过 tomcat web 应用程序根据用户的请求重置我的 MySQL 数据库后，我得到了 tomcat 异常。到目前为止，我已尝试将其分解为设置、问题和我的分析，以帮助任何试图阅读本文的人。

Setup

设置

The reset basically consists of calling a bash script from the java code to:

重置基本上包括从 java 代码调用 bash 脚本来：

• Removing the root mysql user password
• Loading in an old version of the database
• Running some scripts on it
• Restoring all the passwords

• 删除root mysql用户密码
• 加载旧版本的数据库
• 在其上运行一些脚本
• 恢复所有密码

It is a user initiated procedure to usually restore the database to a previous state, but it is also be used to import a database from another system. Once everything is complete, the user then attempts to access a different portion of the web app (i.e. with the same session without logging out/logging back in) which performs a DB query to get some data.

它是一个用户启动的过程，通常用于将数据库恢复到以前的状态，但它也用于从另一个系统导入数据库。一切都完成后，用户然后尝试访问 Web 应用程序的不同部分（即使用相同的会话而不注销/重新登录），该部分执行数据库查询以获取一些数据。

Problem

问题

Once the DB is queried by the tomcat application, there is an exception:

一旦tomcat应用查询到DB，就会出现异常：

Dec 29, 2014 3:49:50 PM ERROR BasicSecurityRealm:216 - 
ERROR: ----- SQLException -----

Dec 29, 2014 3:49:50 PM  INFO BasicSecurityRealm:218 - Exceptioncom.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 234,810 milliseconds ago.  The last packet sent successfully to the server was 12 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
...
Caused by: 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:2540)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2990)

Even if the user logs out and back in, I will see this exception. If I refresh the page, four times, the page will load a little bit more each time with some different exceptions (all variations of the above - CommunicationsException caused by "EOFException: Can not read response from server"). The final time, everything seems to be running normally.

即使用户注销并重新登录，我也会看到此异常。如果我刷新页面四次​​，页面每次都会加载更多一点，但会有一些不同的异常（上述所有变体 - 由“EOFException：无法读取服务器响应”引起的 CommunicationsException）。最后一次，一切似乎都在正常运行。

The only thing that I can do to avoid these exceptions is to restart tomcat. I would like to avoid this because it will mean that the current user who is logged in will lose their session and have to wait for tomcat to restart before they can log back in. Forcing them to log out/back in might be an acceptable compromise, but that doesn't solve the problem anyway.

为了避免这些异常，我唯一能做的就是重新启动 tomcat。我想避免这种情况，因为这意味着当前登录的用户将失去他们的会话，并且必须等待 tomcat 重新启动才能重新登录。强迫他们注销/重新登录可能是一个可以接受的妥协，但这并不能解决问题。

Analysis

分析

From what I can tell, I think that the problem has to do with the JDBC connection pool. I am using JNDI data sources to access my database as follows:

据我所知，我认为问题与JDBC连接池有关。我正在使用 JNDI 数据源访问我的数据库，如下所示：

server.xml:

服务器.xml：

  <GlobalNamingResources>
    <Resource name="jdbc/mydb"
              auth="Container"
              type="javax.sql.DataSource"
              maxActive="30" maxIdle="30" maxWait="2147483647"
              username="x" password="x"
              driverClassName="com.mysql.jdbc.Driver"
              url="jdbc:mysql://localhost:3306/mydb?autoReconnect=true"/>

web.xml:

网页.xml：

<!-- Data source definitions -->
<resource-ref>
    <res-ref-name>jdbc/mydb</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    <res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>

Java:

爪哇：

    // Get connection to specified database
    Context initCtx = new InitialContext();
    Context envCtx = (Context) initCtx.lookup("java:comp/env");
    DataSource ds = (DataSource) envCtx.lookup("jdbc/mydb");
    con = ds.getConnection();
    stmt = con.createStatement();
    rs = stmt.executeQuery("...");

I believe that the connection pool contains connections that are stale/dead. Whenever I get a connection with ds.getConnection, then it is getting one of these old connections. Attempts to use it will fail the first time and the connection is reset (notice that I am using autoReconnect=true, so the second time should (and does) work). However, the pool contains many (in my case, empirically 4 or 5) stale connections, so it takes a while before they are all properly reset. Once a connection is reset, everything behaves properly.

我相信连接池包含陈旧/死的连接。每当我与 建立连接时ds.getConnection，它就会获得这些旧连接之一。第一次尝试使用它会失败并且连接被重置（注意我正在使用autoReconnect=true，所以第二次应该（并且确实）工作）。但是，该池包含许多（在我的情况下，根据经验为 4 或 5 个）陈旧连接，因此需要一段时间才能正确重置它们。重置连接后，一切都会正常运行。

Solutions?

解决方案？

Since I use autoReconnect=trueI could re-structure my code so that if I get an exception when I attempt a query, I can retry the query once. If it fails again, then I would know that there is really a problem. If it passes, then the connection was successfully re-established.

因为我使用autoReconnect=true我可以重新构建我的代码，这样如果我在尝试查询时遇到异常，我可以重试一次查询。如果它再次失败，那么我就知道确实有问题。如果通过，则连接已成功重新建立。

The problem with this is that there are queries EVERYWHERE in the code. Re-factoring them all would take a lot of time and testing, which I will do if necessary, but would like to avoid. Also, if the query is failing for other reasons, then it would be attempted twice before being reported. For long queries, this could have a significant user experience delay, but only in error conditions.

问题在于代码中到处都有查询。将它们全部重新分解将花费大量时间和测试，如有必要，我会这样做，但希望避免。此外，如果查询由于其他原因失败，则在报告之前会尝试两次。对于长查询，这可能会导致显着的用户体验延迟，但仅限于错误情况。

Another solution would be to force a reset/reconnect of all connections in the connection pool. I could do this either programmatically (i.e. from my java code when the invocation of the bash script completes) or from the bash script (e.g. with some type of command line utility). The problem is, I don't know how to do this, or if it is even possible.

另一种解决方案是强制重置/重新连接连接池中的所有连接。我可以以编程方式（即在 bash 脚本调用完成时从我的 java 代码中）或从 bash 脚本（例如，使用某种类型的命令行实用程序）执行此操作。问题是，我不知道该怎么做，或者是否有可能。

I've found some documentation on Interceptors, but I am not sure if that would work for resetting a connection. I will continue to investigate.

我找到了一些关于拦截器的文档，但我不确定这是否适用于重置连接。我会继续调查。

Thanks everyone for your time and help!

感谢大家的时间和帮助！

采纳答案by terma

You can test connection before getting from the pool

您可以在从池中获取之前测试连接

By default Tomcat <7 uses commond-dbcpfor Tomcat >= 7 it's jdbc-pool

默认情况下，Tomcat <7 使用commond-dbcpfor Tomcat >= 7 它是jdbc-pool

In both cases add next properties to connection pool configuration:

在这两种情况下，将下一个属性添加到连接池配置中：

validationQuery=<TEST SQL>
testOnBorrow=true

回答by user10893531

That exception will raise due to your project contain multiple ojdbcjars like (ojdbc6 or ojdbc14) at that moment you may get exception when you are trying to connect oracle 12cremove all ojdbcjars in your project and add any one jar only recommended one is ojdbc7jar.

由于您的项目包含多个ojdbcjar，例如 ( ojdbc6 or ojdbc14)，当您尝试连接 oracle 时可能会出现异常，请12c删除ojdbc项目中的所有jar 并添加任何一个 jar，仅推荐一个是ojdbc7jar。

回答by user10893531

You can test connection before getting from the pool.

您可以在从池中获取之前测试连接。

Add in connection pool configuration:

在连接池配置中添加：

validationQuery=TEST SQL
testOnBorrow=true