相同的老故事:Tomcat DBCP + MySQL，MySQLNonTransientConnectionException:连接关闭后不允许进行任何操作 [英] Same old story: Tomcat DBCP + MySQL, MySQLNonTransientConnectionException: No operations allowed after connection closed

问题描述

我在这里检查了有关此主题的相关问题，并用谷歌搜索了一段时间.似乎我仍然不了解Tomcat的DBCP配置或机制中的一些令人毛骨悚然的内容.

我在server.xml中配置了Tomcat 6，DBCP资源:

<Resource name="jdbc/myDB" auth="Container" description="Database connection"
  type="javax.sql.DataSource"
  driverClassName="com.mysql.jdbc.Driver"
  url="jdbc:mysql://myhost:3306/mydb?autoReconnectForPools=true&useUnicode=true"
  username="user"
  password="password"
  validationQuery="SELECT 1"
  testOnBorrow="true"
  testWhileIdle="true"
  timeBetweenEvictionRunsMillis="10000" minEvictableIdleTimeMillis="60000"
  maxActive="20" maxWait="20000" maxIdle="10"
  removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true" />

我也在寻找mysql的wait_timeout，它是默认的28800.

总而言之，我尝试了几种方法，但似乎问题在于mysql连接处于闲置状态达28800秒，服务器关闭了它们.我认为该池应该以某种方式使用validationQuery和testOnBorrow处理这种情况，但似乎我错了.

如果在闲置时间后在url中使用autoReconnect，则我的第一个查询尝试将导致"...CommunicationsException: The last packet successfully received from the server was 157,493,261 milliseconds ago."，但此后它可以正常工作.

如果我在url中使用autoReconnectForPools或只是不使用它-闲置时间(8小时)后，我每次都会得到".MySQLNonTransientConnectionException: No operations allowed after connection closed".

在两种情况下，应用程序加载时都能顺利进行.因此，我得出的结论是，连接是从mysql端关闭的.

帮助，我错过了什么?我想在不更改Mysql中的wait_timeout的情况下解决此问题.目标-稳定的应用程序，如果出现这种情况，它可能会在空闲时间生存下来:)

我想了一下，决定保留这个问题，尽管问题根本不在Tomcat DBCP中.我发现许多类似的问题，其中许多问题没有得到答案，却从未发现可能发生的情况的线索，这促使我做出决定.因此，我在这里向突然走同一路的任何人发出警告消息:

问题是我有一个使用数据库的资源，该资源仅被初始化过一次，例如:

class MyClass {
   private MyResource res = null;

   private MyResource getMyResource() {
      if (res == null) res = new MyResource(getConnection());
      return res;
   }

   private Connection getConnection() {
      ....
      con = dataSource.getConnection();
      ....
      return con;
   }
}

当我的应用程序在Servlet中时，导致MyResource实例是唯一的实例并打开了连接的情况.因此，当MySQL服务器终止过期的连接时，我的应用程序将在第一次查询时获得有关超时的异常，但是由于后续查询的自动重新连接，连接将再次恢复活动. Pool只是没有触及连接，因为它从未被释放.

最后我意识到了这一点，并通过将getMyResource更改为:

private MyResource getMyResource() {
  return new MyResource(getConnection());
}

在我看来，这是合理的.更改之后，所有事物开始按预期工作.

I examined related questions here on this topic, and also googled for some time. Still seems I don't understand something crytical in Tomcat's DBCP configuration or mechanics.

I got Tomcat 6, DBCP resource configured in server.xml:

<Resource name="jdbc/myDB" auth="Container" description="Database connection"
  type="javax.sql.DataSource"
  driverClassName="com.mysql.jdbc.Driver"
  url="jdbc:mysql://myhost:3306/mydb?autoReconnectForPools=true&useUnicode=true"
  username="user"
  password="password"
  validationQuery="SELECT 1"
  testOnBorrow="true"
  testWhileIdle="true"
  timeBetweenEvictionRunsMillis="10000" minEvictableIdleTimeMillis="60000"
  maxActive="20" maxWait="20000" maxIdle="10"
  removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true" />

Also I looked for mysql's wait_timeout and it is default 28800.

In summary I tried several options, but seems the problem is that mysql connections hit 28800 seconds of being idle and server closes them. I thought that pool should somehow handle this situation with validationQuery and testOnBorrow, but seems I'm wrong.

If I use autoReconnect in url after idle period My first query attempt results in "...CommunicationsException: The last packet successfully received from the server was 157,493,261 milliseconds ago." but after this it works normal.

If I use autoReconnectForPools in url or just don't use it - after idle period (8 hours) I got everytime ".MySQLNonTransientConnectionException: No operations allowed after connection closed".

In both cases things go smoothly while app has load. So, I concluded that connections was closed from side of mysql.

Help, what have I missed? I'd like to solve this problem without changing wait_timeout in Mysql. Goal - stable app which may survive idle times if it occur :)

解决方案

I thought a bit, and decided to leave this question to be, though problem was not in Tomcat DBCP at all. My decision is motivated by the fact that I found quite a number of similar questions, and many of them unanswered, and never found a clue for what might have been going on. So, I leave a message here just as a warning for anyone suddenly walking the same path:

The problem was that I had a resource using database which was initialized just once, like:

class MyClass {
   private MyResource res = null;

   private MyResource getMyResource() {
      if (res == null) res = new MyResource(getConnection());
      return res;
   }

   private Connection getConnection() {
      ....
      con = dataSource.getConnection();
      ....
      return con;
   }
}

When my app was in Servlet it led to the situation where instance of MyResource were the only one, and had connection open. So, when MySQL server kill the expired connection my app would get exception about timeout on first query, but owing to autoReconnect for subsequent queries connection will be alive again. Pool just didn't touched connection because it had never been released.

Finally I realized it, and fixed by changing getMyResource to:

private MyResource getMyResource() {
  return new MyResource(getConnection());
}

In my case it was reasonable. After this change all things started to work as expected.

这篇关于相同的老故事:Tomcat DBCP + MySQL，MySQLNonTransientConnectionException:连接关闭后不允许进行任何操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！