Solving a "communications link failure" with JDBC and MySQL [duplicate]

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

Here is the code.

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) {}
            }
        }
    }
}

and the errors :

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

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

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.

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.

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

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 (Notice it's ProgramData, not Program Files)

Here are the solutions:

changing bind-address attribute:

Uncomment bind-address attribute or change it to one of the following IPs:
```
 bind-address="127.0.0.1"
```
or
```
 bind-address="0.0.0.0"
```
commenting out "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.

change "wait_timeout" and "interactive_timeout"

Add these lines to the MySQL config file:

[wait_timeout][1] = *number*

interactive_timeout = *number*

connect_timeout = *number*

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

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

This could be avoided by using one of two approaches:
1. In the connection string use 127.0.0.1 instead of localhost to avoid localhost being translated to :::1
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

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

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
```
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.

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

Usually default port for MySQL is 3306.

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

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

"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.

change tomcat security

change TOMCAT6_SECURITY=yes to TOMCAT6_SECURITY=no

use validationQuery property

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.

But what solved my problem?

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.

#Conclusion# 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.

If you are using MAMP PRO, the easy fix, which I really wish I had realized before I started searching the internet for days trying to figure this out. Its really this simple...

You just have to click "Allow Network Access to MySQL" from the MAMP MySQL tab.

Really, thats it.

Oh, and you MIGHT have to still change your bind address to either 0.0.0.0 or 127.0.0.1 like outlined in the posts above, but clicking that box alone will probably solve your problems if you are a MAMP user.

Setting the bind-address to the server's network IP instead of the localhost default, and setting privileges on my user worked for me.

my.cnf:

bind-address = 192.168.123.456

MySql Console:

GRANT ALL PRIVILEGES ON dbname.* to username@'%' IDENTIFIED BY 'password';

In my case,

Change the remote machine mysql configuration at /etc/mysql/my.cnf: change bind-address = 127.0.0.1 to #bind-address = 127.0.0.1
On the remote machine, change mysql user permissions with GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'password';
IMPORTANT: restart mysql on the remote machine: sudo /etc/init.d/mysql restart

In my case (I am a noob), I was testing Servlet that make database connection with MySQL and one of the Exception is the one mentioned above.

It made my head swing for some seconds but I came to realize that it was because I have not started my MySQL server in localhost.
After starting the server, the problem was fixed.

So, check whether MySQL server is running properly.

Solving a "communications link failure" with JDBC and MySQL [duplicate]

Here are the solutions:

Related

Recent Posts