访问远程服务器时MySQL连接出错
问题描述：
我已经托管了一个MySQL数据库在网络服务器。我授予的所有privledges，并允许我的IP远程从我的本地计算机连接到该数据库。它被连接起来，我可以从数据库中检索数据到我的Java Swing应用程序。但是，有时我收到此错误消息和我的与托管数据库的连接失败。 误差如下所示：访问远程服务器时MySQL连接出错
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)
有人可以帮我这种情况。我不明白什么是错的！它是服务器还是？ 非常感谢:)
这是我的同班同学连接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关闭，则获取新连接。
UPDATE：
你到处创建一个新的连接之外的getResult（），这是在堆栈跟踪标识错误的来源。您可以像创建其他地方一样创建新连接，也可以改进getConnection（）的封装。换句话说，更改getConnection（）以返回存储在您的类中的静态连接，条件是它不为空且仍然有效。否则，创建一个新的连接并返回。
答
这是在MySQL中一个相当普遍的问题，遗憾的是在生产中通常只发生后，当应用程序运行于不同的开发周期短一些较长的时间。
MySQL的关闭连接，而不会通知客户并没有太大的告诉例外是当您尝试使用一个超时连接显示的内容。例如你可以定期用一些简单的SQL语句来ping连接，以避免超时。
它是使用连接池（以避免过多的用户访问DB一次的问题），例如，一个很好的做法DBCP，一般来说。 DBCP可以很容易地添加到您的应用程序中，您只能将其设置为准JDBC驱动程序。 在那里你可以设置正确的方式。
我复制了我的一个项目这个DBCP配置：
MaxActive=20,
MaxWait=60000,
TestWhileIdle=true,
TimeBetweenEvictionRunsMillis=300000,
MinEvictableIdleTimeMillis=300000,
TestOnBorrow=true,
ValidationQuery='SELECT 1'
更新： 作为一个非常简单的解决方案，你可以尝试追赶上访问异常，并打开这种情况下，一个新的连接。
感谢您的提示。但我该怎么做？ – wishman 2012-04-06 19:44:41
您是直接调用连接还是使用数据库抽象工具？如果您直接调用java.sql.Connection，请尝试使用isValid（int timeout）方法。 – Dave 2012-04-06 19:56:31
我刚刚编辑了我的问题，并添加了我的课程来连接数据库。您能否善意地检查它并告诉我应该在哪里纠正它？非常感谢:) – wishman 2012-04-06 20:02:13