访问远程服务器时MySQL连接出错

I've hosted a MySQL DB in a webserver. I've granted all privledges and allowed my IP to connect to this database remotely from my local computer. It gets connected and I'm able to retrieve data from the database to my Java Swing application. But, sometimes I get this error message and my connection with the hosted DB fails. Error is shows below:

我在网络服务器上托管了一个MySQL数据库。我已授予所有权限并允许我的IP从本地计算机远程连接到此数据库。它连接起来，我能够从数据库中检索数据到我的Java Swing应用程序。但是，有时我收到此错误消息，并且我与托管数据库的连接失败。错误如下所示：

Apr 7, 2012 12:49:20 AM scm.new_fas txtSearchKeyReleased
SEVERE: null
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.io.EOFException
MESSAGE: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

STACKTRACE:

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:1997)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2411)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2916)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3250)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3179)
    at com.mysql.jdbc.Statement.executeQuery(Statement.java:1207)
    at scm.DBControl.getResult(DBControl.java:49)
    at scm.new_fas.txtSearchKeyReleased(new_fas.java:1686)
    at scm.new_fas.access$2300(new_fas.java:28)
    at scm.new_fas$23.keyReleased(new_fas.java:1136)
    at java.awt.Component.processKeyEvent(Component.java:5999)
    at javax.swing.JComponent.processKeyEvent(JComponent.java:2794)
    at java.awt.Component.processEvent(Component.java:5815)
    at java.awt.Container.processEvent(Container.java:2058)
    at java.awt.Component.dispatchEventImpl(Component.java:4410)
    at java.awt.Container.dispatchEventImpl(Container.java:2116)
    at java.awt.Component.dispatchEvent(Component.java:4240)
    at java.awt.KeyboardFocusManager.redispatchEvent(KeyboardFocusManager.java:1848)
    at java.awt.DefaultKeyboardFocusManager.dispatchKeyEvent(DefaultKeyboardFocusManager.java:693)
    at java.awt.DefaultKeyboardFocusManager.preDispatchKeyEvent(DefaultKeyboardFocusManager.java:958)
    at java.awt.DefaultKeyboardFocusManager.typeAheadAssertions(DefaultKeyboardFocusManager.java:830)
    at java.awt.DefaultKeyboardFocusManager.dispatchEvent(DefaultKeyboardFocusManager.java:657)
    at java.awt.Component.dispatchEventImpl(Component.java:4282)
    at java.awt.Container.dispatchEventImpl(Container.java:2116)
    at java.awt.Window.dispatchEventImpl(Window.java:2429)
    at java.awt.Component.dispatchEvent(Component.java:4240)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:173)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:121)


** END NESTED EXCEPTION **



Last packet sent to the server was 2 ms ago.
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2622)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2916)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3250)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3179)
    at com.mysql.jdbc.Statement.executeQuery(Statement.java:1207)
    at scm.DBControl.getResult(DBControl.java:49)
    at scm.new_fas.txtSearchKeyReleased(new_fas.java:1686)
    at scm.new_fas.access$2300(new_fas.java:28)
    at scm.new_fas$23.keyReleased(new_fas.java:1136)
    at java.awt.Component.processKeyEvent(Component.java:5999)
    at javax.swing.JComponent.processKeyEvent(JComponent.java:2794)
    at java.awt.Component.processEvent(Component.java:5815)
    at java.awt.Container.processEvent(Container.java:2058)
    at java.awt.Component.dispatchEventImpl(Component.java:4410)
    at java.awt.Container.dispatchEventImpl(Container.java:2116)
    at java.awt.Component.dispatchEvent(Component.java:4240)
    at java.awt.KeyboardFocusManager.redispatchEvent(KeyboardFocusManager.java:1848)
    at java.awt.DefaultKeyboardFocusManager.dispatchKeyEvent(DefaultKeyboardFocusManager.java:693)
    at java.awt.DefaultKeyboardFocusManager.preDispatchKeyEvent(DefaultKeyboardFocusManager.java:958)
    at java.awt.DefaultKeyboardFocusManager.typeAheadAssertions(DefaultKeyboardFocusManager.java:830)
    at java.awt.DefaultKeyboardFocusManager.dispatchEvent(DefaultKeyboardFocusManager.java:657)
    at java.awt.Component.dispatchEventImpl(Component.java:4282)
    at java.awt.Container.dispatchEventImpl(Container.java:2116)
    at java.awt.Window.dispatchEventImpl(Window.java:2429)
    at java.awt.Component.dispatchEvent(Component.java:4240)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:173)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:121)

Can somebody please help me with this situation. I dont understand whats wrong! Is it the server or ? Thanks a lot :)

有人可以帮我解决这个问题。我不明白什么是错的！是服务器还是？非常感谢：）

This is my class to Connect the DB:

这是我连接数据库的类：

package scm;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DBControl {

    private static DBControl con;
    private static String severIp = null;
    private static String severPort = null;
    private static String userName = null;
    private static String password = null;
    private static ResultSet rs = null;
    private static Connection cc = null;

    public DBControl() {
    }

    public static synchronized DBControl getInstance() {
        if (con == null) {
            con = new DBControl();
        }
        return con;
    }

    public void setSeverIp(String Ip) {
        severIp = Ip;
    }

    public void setSeverPort(String Port) {
        severPort = Port;
    }

    public void SetUserName(String Name) {
        userName = Name;
    }

    public void setPassword(String passWord) {
        password = passWord;
    }

    public static ResultSet getResult(String url) throws Exception {
        Statement s = cc.createStatement();
        rs = s.executeQuery(url);
        return rs;
    }

    public static Connection getConnection() throws Exception {
//        try {
        Class.forName("com.mysql.jdbc.Driver");
        cc = (Connection) DriverManager.getConnection("jdbc:mysql://" + getSeverIp() + ":" + getSeverPort() + "/anuradha_rr", getUserName(), getPassword());
        return cc;
    }

    public void setResult(String url) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        cc = (Connection) DriverManager.getConnection("jdbc:mysql://" + getSeverIp() + ":" + getSeverPort() + "/anuradha_rr", getUserName(), getPassword());
        Statement s = (Statement) cc.createStatement();
        s.executeUpdate(url);
    }

    public static String getSeverIp() {
        return severIp;
    }

    public static String getSeverPort() {
        return severPort;
    }

    public static String getUserName() {
        return userName;
    }

    public static String getPassword() {
        return password;
    }
}

2 个解决方案

#1

Make sure you are not using a dead jdbc connection. Depending on how you are creating your jdbc connection they may be going idle. If you are using the same connection over and over, test the connection before you try to issue a query and get a new one if it has been closed by mysql.

确保您没有使用死jdbc连接。根据您创建jdbc连接的方式，它们可能会空闲。如果您反复使用相同的连接，请在尝试发出查询之前测试连接，如果已被mysql关闭，则获取新连接。

UPDATE:

更新：

You create a new connection everywhere except getResult() which is where the stack trace identifies the error comes from. You can either create a new connection like you do everywhere else, or improve the encapsulation of getConnection(). In other words, change getConnection() to return the static Connection stored in your class, on condition it is not null and still valid. Otherwise create a new connection and return that.

除了getResult（）之外，您可以在任何地方创建新连接，这是堆栈跟踪标识错误的来源。您可以像在其他任何地方一样创建新连接，也可以改进getConnection（）的封装。换句话说，更改getConnection（）以返回存储在类中的静态Connection，条件是它不为null且仍然有效。否则创建一个新连接并返回该连接。

#2

This is a fairly common problem in MySQL, unfortunately occurring usually only after in production when application runs for some extended time unlike in the short development cycle.

这在MySQL中是一个相当普遍的问题，不幸的是，只有在生产中，当应用程序运行一段延长的时间时，通常只会在短的开发周期中发生。

MySQL closes connections without notifying its clients and the not much telling exception is what appears when you try to use a timed out connection. you could for example ping the connection with some trivial SQL statement periodically to avoid timeout.

MySQL在不通知其客户端的情况下关闭连接，并且当您尝试使用超时连接时，会出现不太明显的异常。例如，您可以定期ping一些简单的SQL语句以避免超时。

It is a good practice to use a connection pool (to avoid problem with too many users accessing the DB at once), e.g. DBCP, in general. DBCP may be added to your application easily and you only set it as a quasi JDBC driver. There you can set things proper way.

使用连接池是一种很好的做法（以避免有太多用户一次访问数据库的问题），例如一般而言，DBCP。 DBCP可以轻松添加到您的应用程序中，您只需将其设置为准JDBC驱动程序。在那里你可以设置正确的方式。

I copied this DBCP config from one of my projects:

我从我的一个项目中复制了这个DBCP配置：

                 MaxActive=20,
                 MaxWait=60000,
                 TestWhileIdle=true,
                 TimeBetweenEvictionRunsMillis=300000,
                 MinEvictableIdleTimeMillis=300000,
                 TestOnBorrow=true,
                 ValidationQuery='SELECT 1'

Update: As a very simple solution, you could try catching the exception on access and open a new connection in that case.

更新：作为一个非常简单的解决方案，您可以尝试在访问时捕获异常并在这种情况下打开新连接。

#1