Glassfish，jTDS和SQL Server 2008数据库连接意外关闭 [英] Database connection unexpectedly closed with Glassfish, jTDS and SQL Server 2008

问题描述

我有一个运行在Glassfish上的Java EE应用程序，并通过jTDS连接到MSSQL Server 2008。由于某些未知原因，数据库连接在请求期间意外关闭。这个应用程序非常庞大，但是这里是错误发生的概要：

在Glassfish安装过程中，创建一个连接池，其中包含 asadmin create- jdbc-connection-pool 和 asadmin create-jdbc-resource 。数据源类是 net.sourceforge.jtds.jdbcx.JtdsDataSource 。

当Glassfish升级时，它会调用我们的实现ServletContextListener.contextInitialized（），我们从JNDI获取数据源。数据源存储在一个静态变量中。

有一段时间，一切都很顺利。所有请求都被处理并且没有连接被关闭。我们的应用程序使用Timer和MDB（Message Driven Bean）EJB来执行处理。

这是一个示例 onMessage（）执行：

  public void onMessage（Message message）{
 this.message = message; 
 this.connection = dataSource.getConnection（userName，password）; 
尝试{
 doQuery1（）; 
 doTransaction1（）; 
 doTransaction2（）; 
 doQuery2（）; 
 doQuery3（）; 
} finally {
 this.connection.close（）; 
 this.connection = null; 
 
 
 
 
 
 
 $ b最终，我们开始得到下面的异常（发生在一小时内约100次）： 
 
 
  java.sql.SQLException：无效状态，Connection对象关闭。 
 at net.sourceforge.jtds.jdbc.ConnectionJDBC2.checkOpen（ConnectionJDBC2.java）
 at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareStatement（ConnectionJDBC2.java）
 at net.sourceforge .jtds.jdbc.ConnectionJDBC2.prepareStatement（ConnectionJDBC2.java）
 at com.sun.gjc.spi.base.ConnectionHolder.prepareStatement（ConnectionHolder.java:475）
 at com.acme.myejbs.MyMDB .doQuery2（MyMDB.java:123）
 at com.acme.myejbs.MyMDB.onMessage（MyMDB.java:614）
 at sun.reflect.GeneratedMethodAccessor115.invoke（Unknown Source）
在sun.reflect.DelegatingMethodAccessorImpl.invoke（DelegatingMethodAccessorImpl.java）
在java.lang.reflect.Method.invoke（Method.java）
在com.sun.enterprise.security.application.EJBSecurityManager.runMethod （EJBSecurityManager.java:1011）
 ... 
 at $ Proxy92.onMessage（Unknown Source）
 at com.sun.messaging.jms.ra.OnMessageRunner.run（OnMessageRunner.java） 
在com.sun.enterprise.connectors.work.OneWork.doWork（OneWo rk.java:77）
 at com.sun.corba.ee.impl.orbutil.threadpool.ThreadPoolImpl $ WorkerThread.run（ThreadPoolImpl.java:555）
  
随机JDBC调用发生异常。有时在ResultSet迭代期间，在查询执行期间的其他时间。
 
 
 
在极少数情况下（一个小时内有7次），我们得到这个异常：
  java.sql.SQLException：分配连接时出错。原因：此管理连接无效，因为phyiscal连接无法使用
 at com.sun.gjc.spi.base.DataSource.getConnection（DataSource.java:136）
 at com.acme.myejbs .MyMDB.onMessage（MyMDB.java:614）
在sun.reflect.GeneratedMethodAccessor115.invoke（来源不明）
在sun.reflect.DelegatingMethodAccessorImpl.invoke（DelegatingMethodAccessorImpl.java）
是java .lang.reflect.Method.invoke（Method.java）
 at com.sun.enterprise.security.application.EJBSecurityManager.runMethod（EJBSecurityManager.java:1011）
 ... 
 at $ Proxy92.onMessage（Unknown Source）
 at com.sun.messaging.jms.ra.OnMessageRunner.run（OnMessageRunner.java）
 at com.sun.enterprise.connectors.work.OneWork.doWork（ OneWork.java:77）
 at com.sun.corba.ee.impl.orbutil.threadpool.ThreadPoolImpl $ WorkerThread.run（ThreadPoolImpl.java:555）
  
另外在极少数情况下（一小时内有5次），我们得到这个例外： 
 
 
  java.sql.SQLException：I / O错误：通过peer重置连接：套接字写入错误$ b $ net.sourceforge.jtds.jdbc.TdsCore.executeSQL（TdsCore.java）$ b $ net.sourceforge.jtds .jdbc.JtdsStatement.executeSQLQuery（JtdsStatement.java）
在net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery（JtdsPreparedStatement.java）在com.acme.myejbs.MyMDB.doQuery2 
（MyMDB.java ：126）
 at com.acme.myejbs.MyMDB.onMessage（MyMDB.java:614）
 ... 
引起：java.net.SocketException：由peer重置的连接：socket在java.net.SocketOutputStream.socketWrite0上写入错误
（Native方法）$ java.net.SocketOutputStream.socketWrite（SocketOutputStream.java）
在java.net.SocketOutputStream.write（SocketOutputStream。 （DataOutputStream.java）
 at net.sourceforge.jtds.jdbc.SharedSocket.sendNetPacket（SharedSocket.java）
 at net.sourceforge.jtds .jdbc.RequestStream.putPacket（RequestStream.java）$ net $ net $ ceforge.jtds.jdbc.RequestStream.flush（RequestStream.java）
 ... 44 more 
  
 
 
 <在极少数情况下，我们得到这个可怕的例外（NPE在jTDS中）： 
 
 
  java.lang.NullPointerException 
 at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery（JtdsPreparedStatement.java）
处com.acme.myejbs.MyMDB com.acme.myejbs.MyMDB.doQuery2（MyMDB.java:126）
。 onMessage（MyMDB.java:614）
 ... 
  
我们找不到原因有时候是这样的。请求期间使用的连接永远不会空闲超过一秒钟。我们不知道谁在断开连接。这可能是网络不稳定，但我猜jTDS应该只产生网络相关的异常，对吗？另外一个选择是Glassfish连接池的一些策略或配置（也许Glassfish是最后，MS SQL Server 2008可以远程删除连接，但是我们如何监控服务器端以了解它们如果发生这种情况，请尝试使用SQL服务器Profiler      en-us / library / ms187929.aspxrel =nofollow> http://msdn.microsoft.com/en-us/library/ms187929.aspx  
您可以从模板标准，因为它包含事件：审计登录，审计注销，ExistingConnection  
  http://msdn.microsoft.com/en-us/library/ms190176.aspx  
我认为他们对你最重要
 
I have a Java EE application running on Glassfish and connecting to MSSQL Server 2008 through jTDS. For some unknown reason, the database connection becomes unexpectedly closed during requests. The application is huge, but here is a summary of how the error happens:

During Glassfish setup, with create a connection pool with asadmin create-jdbc-connection-pool and asadmin create-jdbc-resource. The datasource class is net.sourceforge.jtds.jdbcx.JtdsDataSource.

When Glassfish goes up, it calls our implementation of ServletContextListener.contextInitialized(), where we fetch the datasource from JNDI. The datasource is stored on a static variable.

For a while, everything goes fine. All requests are handled and no connection is closed. Our application performs processing using Timer and MDB (Message Driven Bean) EJBs.

This is a sample onMessage() implementation:
public void onMessage(Message message) {
  this.message = message;
  this.connection = dataSource.getConnection(userName, password);
  try {
    doQuery1();
    doTransaction1();
    doTransaction2();
    doQuery2();
    doQuery3();
  } finally {
    this.connection.close();
    this.connection = null;
  }
}
Eventually, we start to get the following exception (happens about 100 times during one hour):
java.sql.SQLException: Invalid state, the Connection object is closed.
  at net.sourceforge.jtds.jdbc.ConnectionJDBC2.checkOpen(ConnectionJDBC2.java)
  at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareStatement(ConnectionJDBC2.java)
  at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareStatement(ConnectionJDBC2.java)
  at com.sun.gjc.spi.base.ConnectionHolder.prepareStatement(ConnectionHolder.java:475)
  at com.acme.myejbs.MyMDB.doQuery2(MyMDB.java:123)
  at com.acme.myejbs.MyMDB.onMessage(MyMDB.java:614)
  at sun.reflect.GeneratedMethodAccessor115.invoke(Unknown Source)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java)
  at java.lang.reflect.Method.invoke(Method.java)
  at com.sun.enterprise.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1011)
  ...
  at $Proxy92.onMessage(Unknown Source)
  at com.sun.messaging.jms.ra.OnMessageRunner.run(OnMessageRunner.java)
  at com.sun.enterprise.connectors.work.OneWork.doWork(OneWork.java:77)
  at com.sun.corba.ee.impl.orbutil.threadpool.ThreadPoolImpl$WorkerThread.run(ThreadPoolImpl.java:555)
The exception happens at random JDBC calls. Sometimes is during ResultSet iteration, other times during query execution.

In very rare cases (7 times during an hour) we get this exception:
java.sql.SQLException: Error in allocating a connection. Cause: This Managed Connection is not valid as the phyiscal connection is not usable
  at com.sun.gjc.spi.base.DataSource.getConnection(DataSource.java:136)
  at com.acme.myejbs.MyMDB.onMessage(MyMDB.java:614)
  at sun.reflect.GeneratedMethodAccessor115.invoke(Unknown Source)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java)
  at java.lang.reflect.Method.invoke(Method.java)
  at com.sun.enterprise.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1011)
  ...
  at $Proxy92.onMessage(Unknown Source)
  at com.sun.messaging.jms.ra.OnMessageRunner.run(OnMessageRunner.java)
  at com.sun.enterprise.connectors.work.OneWork.doWork(OneWork.java:77)
  at com.sun.corba.ee.impl.orbutil.threadpool.ThreadPoolImpl$WorkerThread.run(ThreadPoolImpl.java:555)
Also in very rare cases (5 times during an hour) we get this exception:
java.sql.SQLException: I/O Error: Connection reset by peer: socket write error
  at net.sourceforge.jtds.jdbc.TdsCore.executeSQL(TdsCore.java)
  at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java)
  at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java)
  at com.acme.myejbs.MyMDB.doQuery2(MyMDB.java:126)
  at com.acme.myejbs.MyMDB.onMessage(MyMDB.java:614)
  ...
Caused by: java.net.SocketException: Connection reset by peer: socket write error
  at java.net.SocketOutputStream.socketWrite0(Native Method)
  at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java)
  at java.net.SocketOutputStream.write(SocketOutputStream.java)
  at java.io.DataOutputStream.write(DataOutputStream.java)
  at net.sourceforge.jtds.jdbc.SharedSocket.sendNetPacket(SharedSocket.java)
  at net.sourceforge.jtds.jdbc.RequestStream.putPacket(RequestStream.java)
  at net.sourceforge.jtds.jdbc.RequestStream.flush(RequestStream.java)
  ... 44 more
In rare cases we get this scary exception (NPE inside jTDS):
java.lang.NullPointerException
  at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java)
  at com.acme.myejbs.MyMDB.doQuery2(MyMDB.java:126)
  at com.acme.myejbs.MyMDB.onMessage(MyMDB.java:614)
  ...
We can't find why this happens. The used connections never become idle for more than a second during a request. We don't know who is dropping the connection. It might be network instability, but then I guess jTDS should yield only network related exceptions, right?

Another option is some policy or configuration of Glassfish connection pool (maybe Glassfish is closing physical connections prematurely), but how can we track it?

Finally, MS SQL Server 2008 can be remotely dropping connections, but how can we monitor the server side to know if it's happening?
解决方案
Try to use SQL server Profiler http://msdn.microsoft.com/en-us/library/ms187929.aspx
You can start with a template "Standard", because it contains events: Audit Login,Audit Logout, ExistingConnection
http://msdn.microsoft.com/en-us/library/ms190176.aspx
I think they are most important to you 

                        
这篇关于Glassfish，jTDS和SQL Server 2008数据库连接意外关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！