解决“通信链路故障”使用JDBC和MySQL [英] Solving a "communications link failure" with JDBC and MySQL

问题描述

我正在尝试连接到本地MySQL服务器，但我一直收到错误。

I'm trying to connect to the local MySQL server but I keep getting an error.

这是代码。

public class Connect {

    public static void main(String[] args) {
        Connection conn = null;

        try {
            String userName = "myUsername";
            String password = "myPassword";

            String url = "jdbc:mysql://localhost:3306/myDatabaseName";
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url, userName, password);
            System.out.println("Database connection established");
        } catch (Exception e) {
            System.err.println("Cannot connect to database server");
            System.err.println(e.getMessage());
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    System.out.println("Database Connection Terminated");
                } catch (Exception e) {}
            }
        }
    }
}

和错误：

Cannot connect to database server
Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:344)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2333)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2370)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2154)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:792)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:381)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)
        at java.sql.DriverManager.getConnection(DriverManager.java:582)
        at java.sql.DriverManager.getConnection(DriverManager.java:185)
        at Connect.main(Connect.java:16)
    Caused by: java.net.ConnectException: Connection refused
        at java.net.PlainSocketImpl.socketConnect(Native Method)
        at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:351)
        at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:213)
        at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:200)
        at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)
        at java.net.Socket.connect(Socket.java:529)
        at java.net.Socket.connect(Socket.java:478)
        at java.net.Socket.<init>(Socket.java:375)
        at java.net.Socket.<init>(Socket.java:218)
        at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:257)
        at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:294)
        ... 15 more

我已设置classpath，确保my.cnf有跳过网络选项注释掉。

I've set the classpath, made sure my.cnf had the skip network option commented out.

java版本是1.2.0_26（64位）
mysql 5.5.14
mysql连接器5.1.17

java version is 1.2.0_26 (64 bit) mysql 5.5.14 mysql connector 5.1.17

我确保用户可以访问我的数据库。

I made sure that the user had access to my database.

推荐答案

我遇到了同样的问题在我的两个程序中。我的错误是：

I have had the same problem in two of my programs. My error was this:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

我花了几天时间来解决这个问题。我已经测试了许多在不同网站中提到过的方法，但是没有这些方法有效。最后我改变了我的代码，发现了问题所在。我会尝试告诉你不同的方法，总结一下。

I spent several days to solve this problem. I have tested many approaches that have been mentioned in different web sites, but non of them worked. Finally I changed my code and found out what was the problem. I'll try to tell you about different approaches and sum them up here.

当我在寻找互联网寻找解决方案时错误，我发现有很多解决方案适用于至少一个人，但其他人说这对他们不起作用！ 为什么有很多方法可以解决这个错误？
似乎当连接到服务器时出现问题时，通常会发生此错误。也许问题是由于错误的查询字符串或与数据库的连接太多。

While I was seeking the internet to find the solution for this error, I figured out that there are many solutions that worked for at least one person, but others say that it doesn't work for them! why there are many approaches to this error? It seems this error can occur generally when there is a problem in connecting to the server. Maybe the problem is because of the wrong query string or too many connections to the database.

所以我建议你逐一尝试所有的解决方案，不要给up！

So I suggest you to try all the solutions one by one and don't give up!

以下是我在互联网上找到的解决方案，对于他们每个人来说，至少有人通过该解决方案解决了他的问题。

Here are the solutions that I found on the internet and for each of them, there is at least on person who his problem has been solved with that solution.

提示：对于您需要更改MySQL设置的解决方案，您可以参考以下文件：

Tip: For the solutions that you need to change the MySQL settings, you can refer to the following files:

• Linux：/etc/mysql/my.cnf或/etc/my.cnf（取决于所使用的Linux发行版和MySQL包）

• Linux: /etc/mysql/my.cnf or /etc/my.cnf (depending on the Linux distribution and MySQL package used)

Windows：C：** ProgramData ** \ MySQL \ MySQL Server 5.6 \ my.ini（注意它是ProgramData，而不是程序文件）

Windows: C:**ProgramData**\MySQL\MySQL Server 5.6\my.ini (Notice it's ProgramData, not Program Files)

• 更改bind-address 属性

• changing "bind-address" attribute

取消注释bind-address属性或将其更改为以下IP之一s：

Uncomment "bind-address" attribute or change it to one of the following IPs:

bind-address =127.0.0.1

bind-address="127.0.0.1"

或

bind-address =0.0.0.0

bind-address="0.0.0.0"

• 评论出skip-networking

• commenting out "skip-networking"

如果您的MySQL配置文件中有skip-networking行，请通过添加＃符号进行评论在该行的开头。

If there is a "skip-networking" line in your MySQL config file, make it comment by adding "#" sign at the beginning of that line.

• 更改wait_timeout和interactive_timeout

• change "wait_timeout" and "interactive_timeout"

将这些行添加到MySQL配置文件中：

Add these lines to the MySQL config file:

wait_timeout = number

interactive_timeout = 数字

interactive_timeout = number

connect_timeout = 数字

connect_timeout = number

• 确保Java没有将'localhost'翻译为[::: 1]而不是[127.0.0.1]

• Make sure Java isn't translating 'localhost' to [:::1] instead of [127.0.0.1]

由于MySQL识别127.0.0.1（IPv4）但不识别:: ：1（IPv6）

Since MySQL recognizes 127.0.0.1 (IPv4) but not :::1 (IPv6)

使用以下两种方法之一可以避免这种情况：

This could be avoided by using one of two approaches:

选项＃1：在连接字符串中使用 127.0.0.1 而不是 localhost ，以避免将localhost转换为::: 1

Option #1: In the connection string use 127.0.0.1 instead of localhost to avoid localhost being translated to :::1

选项＃2：使用选项-Djava.net.preferIPv4Stack = true运行java以强制java使用IPv4而不是IPv6。在Linux上，这也可以通过运行（或将其放在/ etc / profile中来实现：

Option #2: Run java with the option -Djava.net.preferIPv4Stack=true to force java to use IPv4 instead of IPv6. On Linux, this could also be achieved by running (or placing it inside /etc/profile:

export _JAVA_OPTIONS="-Djava.net.preferIPv4Stack=true"

• 检查操作系统代理设置，防火墙和防病毒程序


• check Operating System proxy settings, firewalls and anti-virus programs

确保防火墙或防病毒软件没有阻止MySQL服务。

Make sure the Firewall, or Anti-virus software isn't blocking MySQL service.

在Linux上暂时停止iptables。如果iptables配置错误，他们可能允许将tcp数据包发送到mysql端口，但阻止tcp数据包返回同一连接。

Stop iptables temporarily on linux. If iptables are misconfigured they may allow tcp packets to be sent to mysql port, but block tcp packets from coming back on the same connection.

# Redhat enterprise and CentOS
systemctl stop iptables.service
# Other linux distros
service iptables stop

在Windows上停止防病毒软件。

Stop anti-virus software on Windows.



• 更改连接字符串


• change connection string

检查查询字符串你的连接字符串应该是一些东西像这样：

Check your query string. your connection string should be some thing like this:

dbName = "my_database";
dbUserName = "root";
dbPassword = "";
String connectionString = "jdbc:mysql://localhost/" + dbName + "?user=" + dbUserName + "&password=" + dbPassword + "&useUnicode=true&characterEncoding=UTF-8";

确保字符串中没有空格。所有连接字符串都应该继续，不带任何空格字符。

Make sure you don't have spaces in your string. All the connection string should be continues without any space characters.

尝试将localhost替换为环回地址127.0.0.1。
还尝试在连接字符串中添加端口号，例如：

Try to replace "localhost" with the loopback address 127.0.0.1. Also try to add port number to your connection string, like:

String connectionString = "jdbc:mysql://localhost:3306/my_database?user=root&password=Pass&useUnicode=true&characterEncoding=UTF-8";

通常MySQL的默认端口是3306。

Usually default port for MySQL is 3306.

不要忘记将用户名和密码更改为MySQL服务器的用户名和密码。

Don't forget to change username and password to the username and password of your MySQL server.



• 更新JDK驱动程序库文件


• 测试不同的JDK和JRE（如JDK 6和7）


• 不要更改max_allowed_pa​​cket


• update your JDK driver library file
• test different JDK and JREs (like JDK 6 and 7)
• don't change max_allowed_packet

max_allowed_pa​​cket 是MySQL配置文件中的一个变量，表示最大数据包大小，而不是最大数据包数。所以它无助于解决这个错误。

"max_allowed_packet" is a variable in MySQL config file that indicates the maximum packet size, not the maximum number of packets. So it will not help to solve this error.



• 更改tomcat安全性


• change tomcat security

将TOMCAT6_SECURITY = yes更改为TOMCAT6_SECURITY = no

change TOMCAT6_SECURITY=yes to TOMCAT6_SECURITY=no



• 使用validationQuery属性


• use validationQuery property

使用validationQuery =select now（）确保每个查询都有回复

use validationQuery="select now()" to make sure each query has responses



• 自动重新连接


• AutoReconnect

添加此代码到你的连接字符串：

Add this code to your connection string:

&autoReconnect=true&failOverReadOnly=false&maxReconnects=10





---





虽然这些解决方案都不适合我，但我建议你试试。因为有些人通过以下步骤解决了他们的问题。

---

Although non of these solutions worked for me, I suggest you to try them. Because there are some people who solved their problem with following these steps.

但是什么解决了我的问题？

我的问题是我在数据库上有很多SELECT。每次我创建连接然后关闭它。虽然我每次关闭连接，但系统面临许多连接并给了我这个错误。 我所做的是我将连接变量定义为整个类的公共（或私有）变量，并在构造函数中初始化它。然后每次我只使用该连接。它解决了我的问题，也大大提高了我的速度。

My problem was that I had many SELECTs on database. Each time I was creating a connection and then closing it. Although I was closing the connection every time, but the system faced with many connections and gave me that error. What I did was that I defined my connection variable as a public (or private) variable for whole class and initialized it in the constructor. Then every time I just used that connection. It solved my problem and also increased my speed dramatically.

没有简单而独特的方法解决这个问题。我建议你考虑一下自己的情况并选择上述解决方案。如果在程序开头出现此错误并且根本无法连接到数据库，则可能是连接字符串中存在问题。但是如果在几次成功交互到数据库之后出现此错误，问题可能在于连接数量，您可能会考虑更改wait_timeout和其他MySQL设置，或者重写代码如何减少连接数。

There is no simple and unique way to solve this problem. I suggest you to think about your own situation and choose above solutions. If you take this error at the beginning of the program and you are not able to connect to the database at all, you might have problem in your connection string. But If you take this error after several successful interaction to the database, the problem might be with number of connections and you may think about changing "wait_timeout" and other MySQL settings or rewrite your code how that reduce number of connections.

这篇关于解决“通信链路故障”使用JDBC和MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！