Solving "communication link failure" issues using JDBC and MySQL

Question

我正在尝试连接到本地 MySQL 服务器，但不断收到错误消息。

这是代码。

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

我已经设置了类路径，确保 my.cnf 已注释掉跳过网络选项。

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

我确保用户有权访问我的数据库。

Answer 1

I also encountered the same problem in two programs. My error is 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.

It took me several days to solve this problem. I tested many methods mentioned on different websites but none of them worked. Finally I changed the code and figured out the problem. I'll try to introduce you to different methods and summarize them here.

When I searched the internet for solutions to this error, I found that there were many solutions that worked for at least one person, but others said it didn't work for them!

strong> Why are there many ways to fix this error? It seems usuallythis error occurs when there is a problem connecting to the server. Maybe the problem is because of wrong query string or too many connections to the database.

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

Below are the solutions I found on the internet, for each solution at least one person's problem has been solved by that solution.

Tips: For solutions that require changing MySQL settings, please refer to the following files:

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

• Windows: C:\**ProgramData**\MySQL\MySQL Server 5.6\my.ini (note it is ProgramData, not Program Files)

Here are the solutions:

• Changebind-addressProperties:

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

  bind-address="127.0.0.1"

  or

  bind-address="0.0.0.0"

• Comment out "skip-networking"

  If there is a skip-networking line in the MySQL configuration file, please comment it out by adding # symbols at the beginning of the line.

• Change "wait_timeout" and "interactive_timeout"

  Add these lines to the MySQL configuration file:

  [wait_timeout][1] = *number*
  
  interactive_timeout = *number*
  
  connect_timeout = *number*

• Ensure Java does not translate "localhost" as [:::1] instead of [127.0.0.1]

  Because MySQL can recognize 127.0.0.1 (IPv4), but not :::1 (IPv6)

  This can be avoided by using one of two methods:

  1. Use 127.0.0.1 instead of localhost in the connection string to avoid localhost being converted to ::: 1

  2. Run java using option -Djava.net.preferIPv4Stack=true to force java to use IPv4 instead of IPv6. On Linux this can also be achieved by running (or putting this into /etc/profile:

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

• Check operating system proxy settings, firewall and anti-virus programs

  Make sure the MySQL service is not blocked by firewall or anti-virus software.

  Temporarily stop iptables on Linux. If iptables are misconfigured, they may allow tcp packets to be sent to the mysql port, but prevent tcp packets from returning to the same connection.

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

  Stop antivirus software on Windows.

• Change connection string

  Check your query string. Your connection string should look 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 there are no spaces in the string. All connection strings should be contiguous without any space characters.

Try replacing "localhost" with the loopback address 127.0.0.1. Also try adding the port number to the connection string, for example:

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

Usually the default port of MySQL is 3306.

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

• Update your JDK driver library files
• Test different JDKs and JREs (e.g. JDK 6 and 7)
• Do not change max_allowed_packet

"max_allowed_packet" is a variable in the MySQL configuration file that indicates the maximum packet size, not the maximum number of packets. So this doesn't help in solving this error.

• Change tomcat security

Change TOMCAT6_SECURITY=yes to TOMCAT6_SECURITY=no

• Use validationQuery attribute

Use validationQuery="select now()" to ensure that each query has a response

• Automatic reconnect

Add this code to your connection string:

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

---

While none of these solutions worked for me, I recommend you give them a try. Because some people solved their problems by following these steps.

But what solved my problem?

My problem is that I have a lot of SELECTs in the database. Every time I create a connection and then close it. Although I close the connection every time, the system faces many connections and gives me this error. What I did was define the connection variable as a public (or private) variable for the entire class and initialize it in the constructor. Then every time I use that connection. It solved my problem and improved my speed tremendously.

#in conclusion# There is no simple and unique way to solve this problem. It is recommended that you consider the above solutions based on your own situation. If you get this error at the beginning of your program and can't connect to the database at all, there might be something wrong with your connection string. However, if you get this error after many successful interactions with the database, the problem may be with the number of connections and you may consider changing "wait_timeout" and other MySQL settings, or rewriting the code to reduce the number of connections.