访问远程服务器时MySQL连接出错 [英] Error in MySQL Connection when accessing a remote server

问题描述

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

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:

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;
    }
}

推荐答案

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

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.

更新：

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

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.

这篇关于访问远程服务器时MySQL连接出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！