Java应用程序处理连接被删除 [英] Java app handling for connections getting dropped

问题描述

我的应用程式似乎挂了一夜，因为连接中断了（我认为这是问题。）我如何结构我的应用程序，以便它可以尝试汇总一个新的连接？

My app seems to be hanging overnight because of the connection getting dropped(I think that's the problem.) How can I structure my app so that it can try to roll up a new connection?

自从事件发生后，我更新了我的应用程序使用的getConnection（）方法：

Since the incident I have updated the getConnection() method that my app uses as so:

private Connection getConnection() {
    boolean failed = false;
    try{
        failed = !connection.isValid(1000);
    }catch(SQLException ex){
        System.out.println("WARNING: Connection not valid!");
    }
    try{
        failed = connection.isClosed();
    }catch(SQLException ex){
        System.out.println("WARNING: Connection is closed!");
    }
    if(failed){
        System.out.println("Renewing connection");
        this.initializeConnection();
    }
    return connection;
}

这是输出：

** BEGIN NESTED EXCEPTION **

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
MESSAGE: The last packet successfully received from the server was54521 seconds
ago.The last packet sent successfully to the server was 54521 seconds ago, which
  is longer than the server configured value of 'wait_timeout'. You should consi
der either expiring and/or testing connection validity before use in your applic
ation, increasing the server configured values for client timeouts, or using the
 Connector/J connection property 'autoReconnect=true' to avoid this problem.

STACKTRACE:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet success
fully received from the server was54521 seconds ago.The last packet sent success
fully to the server was 54521 seconds ago, which  is longer than the server conf
igured value of 'wait_timeout'. You should consider either expiring and/or testi
ng connection validity before use in your application, increasing the server con
figured values for client timeouts, or using the Connector/J connection property
 'autoReconnect=true' to avoid this problem.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou
rce)
        at java.lang.reflect.Constructor.newInstance(Unknown Source)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1
074)
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3246)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1917)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536)
        at com.mysql.jdbc.ConnectionImpl.setCatalog(ConnectionImpl.java:4962)
        at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMet
aData.java:1506)
        at com.mysql.jdbc.DatabaseMetaData.getProcedureOrFunctionColumns(Databas
eMetaData.java:4120)
        at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.
java:4057)
        at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStat
ement.java:809)
        at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:609)
        at com.mysql.jdbc.JDBC4CallableStatement.<init>(JDBC4CallableStatement.j
ava:46)
        at sun.reflect.GeneratedConstructorAccessor6.newInstance(Unknown Source)

        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou
rce)
        at java.lang.reflect.Constructor.newInstance(Unknown Source)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
        at com.mysql.jdbc.CallableStatement.getInstance(CallableStatement.java:5
05)
        at com.mysql.jdbc.ConnectionImpl.parseCallableStatement(ConnectionImpl.j
ava:3881)
        at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:3965)
        at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:3939)
        at com.protocase.hmiclient.db.HMIDatabaseAdapter.getAvailableBatchesForW
orkstation(HMIDatabaseAdapter.java:471)
        at com.protocase.hmiclient.views.WorkstationContainer.getBatches(Worksta
tionContainer.java:74)
        at com.protocase.hmiclient.views.BatchList.<init>(BatchList.java:55)
        at com.protocase.hmiclient.views.WorkstationContainer.goToBatchList(Work
stationContainer.java:56)
        at com.protocase.hmiclient.views.forms.BatchListControlPanel.refreshButt
onActionPerformed(BatchListControlPanel.java:118)
        at com.protocase.hmiclient.views.forms.BatchListControlPanel.access$200(
BatchListControlPanel.java:16)
        at com.protocase.hmiclient.views.forms.BatchListControlPanel$3.actionPer
formed(BatchListControlPanel.java:64)
        at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
        at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
        at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
        at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
        at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Sour
ce)
        at java.awt.Component.processMouseEvent(Unknown Source)
        at javax.swing.JComponent.processMouseEvent(Unknown Source)
        at java.awt.Component.processEvent(Unknown Source)
        at java.awt.Container.processEvent(Unknown Source)
        at java.awt.Component.dispatchEventImpl(Unknown Source)
        at java.awt.Container.dispatchEventImpl(Unknown Source)
        at java.awt.Component.dispatchEvent(Unknown Source)
        at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
        at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
        at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
        at java.awt.Container.dispatchEventImpl(Unknown Source)
        at java.awt.Window.dispatchEventImpl(Unknown Source)
        at java.awt.Component.dispatchEvent(Unknown Source)
        at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
        at java.awt.EventQueue.access$000(Unknown Source)
        at java.awt.EventQueue$1.run(Unknown Source)
        at java.awt.EventQueue$1.run(Unknown Source)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown
Source)
        at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown
Source)
        at java.awt.EventQueue$2.run(Unknown Source)
        at java.awt.EventQueue$2.run(Unknown Source)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown
Source)
        at java.awt.EventQueue.dispatchEvent(Unknown Source)
        at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
        at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: java.net.SocketException: Software caused connection abort: socket wr
ite error
        at java.net.SocketOutputStream.socketWrite0(Native Method)
        at java.net.SocketOutputStream.socketWrite(Unknown Source)
        at java.net.SocketOutputStream.write(Unknown Source)
        at java.io.BufferedOutputStream.flushBuffer(Unknown Source)
        at java.io.BufferedOutputStream.flush(Unknown Source)
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3227)
        ... 61 more


** END NESTED EXCEPTION **

推荐答案

这个异常表明你在应用程序启动期间只打开一次连接，并在应用程序的生命周期中永远打开。这不好。数据库迟早会收回连接，因为它已经打开太久。您应该在 try 块的 finally 块中正确关闭连接，因为您打开它并执行查询。

This exception suggests that you're opening the connection only once during application's startup and keeping forever open during the application's lifetime. This is bad. The DB will reclaim the connection sooner or later because it's been open for too long. You should close connections properly in the finally block of the very same try block as you're opening it and executing the query on it.

例如

public Entity find(Long id) throws SQLException {
    Connection connection = null;
    // ...

    try {
        connection = database.getConnection();
        // ...
    } finally {
        // ...
        if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
    }

    return entity;
}

如果您对此有性能问题最昂贵的任务），那么你应该使用连接池。它还透明地处理这种连接断开问题。例如， BoneCP 。请注意，在连接池的情况下，您仍应该按照上述JDBC代码惯例在 finally 块中关闭连接。这将使它们可以重复使用。

If you have a performance concern regarding this (which is very reasonable as connecting is the most expensive task), then you should be using a connection pool. It also transparently handles this kind of "connection dropped" problems. For example, BoneCP. Please note that also in case of a connection pool, you should still be closing the connections in the finally block as per the above JDBC code idiom. It will namely make them available for reuse.

这篇关于Java应用程序处理连接被删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！