java 访问远程服务器时 MySQL 连接出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10048296/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error in MySQL Connection when accessing a remote server
提问by wishman
I've hosted a MySQL DBin a webserver. I've granted all privledgesand allowed my IP to connect to this database remotelyfrom 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 messageand 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.access00(new_fas.java:28)
at scm.new_fas.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.access00(new_fas.java:28)
at scm.new_fas.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;
}
}
采纳答案by Dave
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 并且仍然有效。否则创建一个新连接并返回。
回答by MarianP
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 在不通知其客户端的情况下关闭连接，当您尝试使用超时连接时出现的异常情况并不多。例如，您可以定期使用一些简单的 SQL 语句 ping 连接以避免超时。
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.
更新：作为一个非常简单的解决方案，您可以尝试在访问时捕获异常并在这种情况下打开一个新连接。
