CODEWITHSUNDEEP
javamysqljdbc
Josh K
Josh K

Reputation: 28893

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

I'm working on getting my database to talk to my Java programs.

Can someone give me a quick and dirty sample program using the JDBC?

I'm getting a rather stupendous error:

Exception in thread "main" 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:409)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1122)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2260)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:787)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
    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:409)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:357)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
    at java.sql.DriverManager.getConnection(DriverManager.java:582)
    at java.sql.DriverManager.getConnection(DriverManager.java:207)
    at SqlTest.main(SqlTest.java:22)
Caused by: 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:409)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1122)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:344)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2181)
    ... 12 more
Caused by: java.net.ConnectException: Connection refused
    at java.net.PlainSocketImpl.socketConnect(Native Method)
    at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
    at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
    at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:432)
    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:256)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:293)
    ... 13 more

Contents of the test file:

import com.mysql.jdbc.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SqlTest {

    public static void main(String [] args) throws Exception {
        // Class.forName( "com.mysql.jdbc.Driver" ); // do this in init
        // // edit the jdbc url 
        Connection conn = DriverManager.getConnection( 
            "jdbc:mysql://localhost:3306/projects?user=user1&password=123");
        // Statement st = conn.createStatement();
        // ResultSet rs = st.executeQuery( "select * from table" );

        System.out.println("Connected?");
    }
}

Upvotes: 328

Views: 1055035

Answers (30)

nikita sharma
nikita sharma

Reputation: 11

In my case this error come bacause my MySQL database server connection stoped . so for this , To start Mysql again I typed "Services" from the Windows 10 Search box. Then I right-clicked on MySQL80 service and clicked 'Start'.

enter image description here

Upvotes: 0

BugsOverflow
BugsOverflow

Reputation: 548

I just want to add another answer because it is not always a problem in your backend configuration or connections pool, etc.

Sometimes it is also the infrastructure of your database, the location of the server where it is at, so make sure that you have a good database infrastructure (cpu, ram, network, etc.) and also that it is on the same region of your application, this way you will improve alot the performance and stop getting this error messages that in some cases as I am trying to explain, is more about your database

Upvotes: 0

Gerard de Visser
Gerard de Visser

Reputation: 8060

In my case, the solution was to add the expected TLS protocol to the connection string like this:

jdbc:mysql://localhost:3306/database_name?enabledTLSProtocols=TLSv1.2

For testing/development purposes (not recommended for production) you can also try:

jdbc:mysql://localhost:3306/database_name?useSSL=false&allowPublicKeyRetrieval=true

Upvotes: 31

mjaggard
mjaggard

Reputation: 2475

This can also happen why MySQL is dying. In my case I had a query that triggered a bug in MySQL and so the server died and restarted whenever the query was executed. Look in your MySQL error log to see if that's happening. In my case (Homebrew on MacOS) that was in /usr/local/var/mysql/<machinename>.err

Since I was seeing this on a test system I ended up dropping and recreating my database which solved the problem but I dread to think what I would have done if it was on production.

Upvotes: 0

Sivaram Rasathurai
Sivaram Rasathurai

Reputation: 6373

In my case, Adding the query parameter in the connection String solved the issue. &useSSL=false

enter image description here

jdbc:mysql://${jdbc_url}:${jdbc_port}/${database_name}?serverTimezone=UTC&autoReconnect=true&useSSL=false&failOverReadOnly=false&maxReconnects=10

Upvotes: 1

akib
akib

Reputation: 115

In my case I was getting this error while running from intellij IDEA while running from terminal with ./gradlew bootRun was working fine. Then I figured out Intellij was pointing to some liberica java I'd downloaded. I changed it to OpenJdk one and it started working fine.

Upvotes: 1

keesp
keesp

Reputation: 311

There is a very special situation when this problem can happen, with MySQL Workbench, and especially when using the Eclipse JPA and persistence under localhost. The problem is that, as mentioned in some other answers, that the MySQL server can be very restrictive to using localhost, 0.0.0.0.1 or 127.0.0.1 when creating a connection. In order to overcome this, the hosts file in (for Windows):

C:\Windows\System32\drivers\etc

needs to have the following entry added to it for the main connection (e.g.myserver):

#MY DATABASE SERVER
127.0.0.1 myserver

If this is not added to hosts, the connection will fail every time you try to connect with the database from 127.0.0.1

Upvotes: 0

salam_verdim_alana_panyatkasi
salam_verdim_alana_panyatkasi

Reputation: 373

Generally this problem occur, when database, which you use executing turn off in your OS. if you use windows then go to the services and make sure that MySQL is Starting positon.

Upvotes: 1

user7641341
user7641341

Reputation: 209

Add this useSSL=false&allowPublicKeyRetrieval=true

jdbc:mysql://localhost:3306/cloudapp?useSSL=false&allowPublicKeyRetrieval=true

https://youtu.be/ray3YvnIohM

Upvotes: 11

Jackson
Jackson

Reputation: 159

Please use Platform Independent zip or tar, it works for me when encountering this error.

https://dev.mysql.com/downloads/connector/j/?os=26

Upvotes: 1

Musfiq Shanta
Musfiq Shanta

Reputation: 1625

This problem is Create From xampp Port. First You should confirm your port number. Basically, everyone tries to use 3306 but sometimes it will be wrong. You can try this jdbc:mysql://127.0.0.1:3307

Upvotes: 1

Neetesh Kumar Gupta
Neetesh Kumar Gupta

Reputation: 156

I was facing this issue while running the automation tests on remote Jenkins server. However while running the tests on local no issue was occurring.

For me this issue was resolved after updating the mysql-connector version to the latest in the POM file as the MySQL version was changed on server side.

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.24</version>
</dependency>

But debugging of the same turned out a bit difficult and took almost two days as I was not aware of the MySQL version change and it was working fine in local.

Upvotes: 1

Thuan Tran
Thuan Tran

Reputation: 447

I resolve this problem by downgrade jdk version. Downgrade from 11.0.11 to 11.0.10 or older java version

Upvotes: 1

Pedro Queiroga
Pedro Queiroga

Reputation: 391

This question has MANY answers, and I think mine would suit another question better, but the answers to that question are locked. It points to this thread.

What solved it for me was appending &createDatabaseIfNotExist=true to the spring.datasource.url string in application.properties.

This was very tricky because it manifested itself in a couple of different, weird, seemingly unrelated errors, and when I tried to fix what it complained about it simply would pop another error up, or the error was impossible to fix. It complained about not being able to load JDBC, saying it wasn't in the classpath, but I added it to the classpath in some 30 different ways and was already facedesking.

Upvotes: 1

Adnan Asghar
Adnan Asghar

Reputation: 64

In my case, turn out to be that the version of mysql-connector-java was different. I just changed mysql jdbc to maria jbdc

Old jdbc driver

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.21</version>
</dependency>

New Jdbc driver

<!-- https://mvnrepository.com/artifact/org.mariadb.jdbc/mariadb-java-client -->
<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>mariadb-java-client</artifactId>
    <version>2.6.2</version>
</dependency>

Upvotes: 2

A.R.K.S
A.R.K.S

Reputation: 1802

In my case, I faced this error when trying to connect to mysql-server running inside a container on a Ubuntu host VM from the same host.

Example: If my VM name is abc.company.com, the following jdbc URL would not work:

jdbc:mysql://abc.company.com:3306/dbname

Above jdbc url would work fine from other machines like xyz.company.com but just not abc.company.com.

where as the following jdbc URL would work just fine on abc.company.com machine:

jdbc:mysql://localhost:3306/dbname

which led me to check the /etc/hosts file.

Adding the following line to /etc/hosts fixed this issue:

127.0.1.1 abc.company.com abc

This seems to be an OS bug that requires us to add these on some Ubuntu versions. Reference: https://www.debian.org/doc/manuals/debian-reference/ch05.en.html#_the_hostname_resolution

Before trying this, I had tried all other solutions like GRANT ALL.., changing the bind-address line in mysql.cnf.. None of them helped me in this case.

Upvotes: 1

Haimei
Haimei

Reputation: 13015

My same problem is solved by the following steps:

  1. Go to my.cnf

    vi /etc/mysql/my.cnf

  2. Modify its bind-address

    "bind-address = 0.0.0.0"

  3. Restart MySQL

    sudo /etc/init.d/mysql restart

Upvotes: 3

Peter Chen
Peter Chen

Reputation: 871

I have the same connection error: The problem I have is I used MySQL 8.0.15 but the Java Connector is 5.x.x.

Below is how I fixed it. 1. download the 8.0.15. from Maven repository: https://search.maven.org/search?q=g:mysql%20AND%20a:mysql-connector-java

  1. In the Eclipse IDE, select the "Referenced Libraries" in Explorer Right Mouse Button > Build Path > Configure Build Path a. remove the "mysql-connector-5.x.jar" b. Click "Add External JARs..." and select mysql-connector-java-8.0.15.jar.

Re-run it, the problem went away.

Upvotes: 1

Shiv Buyya
Shiv Buyya

Reputation: 4140

Open file /etc/mysql/my.cnf: change below parameter from

`bind-address = 127.0.0.1

to

bind-address = 0.0.0.0 #this allows all systems to connect

Run below command in mysql for specific IP Address->

grant all privileges on dbname.* to dbusername@'192.168.0.3' IDENTIFIED BY 'dbpassword';

If you want to give access to all IP Address, run below command:

grant all privileges on dbname.* to dbusername@'%' IDENTIFIED BY 'dbpassword';

Upvotes: 1

Yugerten
Yugerten

Reputation: 898

For Remote Call to Mysql

  1. Add remote user to Mysql from for exemple IP=remoteIP :

    mysql -u xxxx -p //local coonection to mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO 'theNewUser'@'remoteIP' IDENTIFIED BY 'passWord';
//Query OK, 0 rows affected (xx sec)
mysql> FLUSH PRIVILEGES;
//Query OK, 0 rows affected

  2. Allow remote access to Mysql (by default all externall call is not allowed):

    Edit 
/etc/mysql/mysql.conf.d/mysqld.cnf    or    /etc/mysql/my.cnf
Change line:  bind-address = 127.0.0.1   to
              bind-address = 0.0.0.0
Restart Mysql: /etc/init.d/mysql restart

  3. For The latest version of JDBC Driver, the JDBC :

    jdbc.url='jdbc:mysql://remoteIP:3306/yourDbInstance?autoReconnect=true&amp;useUnicode=true&amp;useJDBCCompliantTimezoneShift=true&amp;useLegacyDatetimeCode=false&amp;serverTimezone=UTC'
jdbc.user='theNewUser'

Upvotes: 1

Sreedhar GS
Sreedhar GS

Reputation: 2788

Please update your IP address in /etc/mysql/my.cnf file

bind-address  = 0.0.0.0

Restart mysql deamon and mysql services.

Upvotes: 4

sureshAngamuthu
sureshAngamuthu

Reputation: 191

In my case, I needed to do a replacement of Localhost to the actual database server IP address

Instead of 

 Connection con = DriverManager.getConnection(
 "jdbc:mysql://localhost:3306/DBname", "root", "root");

I needed 

 Connection con = DriverManager.getConnection(
 "jdbc:mysql://192.100.0.000:3306/DBname", "root", "root");

Upvotes: 15

Ravinath
Ravinath

Reputation: 1760

Sample jdbc connection class file. simply call the getConnection method when you want to get a connection. include related mysql-connector.jar

import java.sql.Connection;
import java.sql.DriverManager;


public class DBConnection {

    public Connection getConnection() {
        Connection con = null;

        String dbhost;
        String user;
        String password;
// get properties
dbhost="jdbc:mysql://localhost:3306/cardmaildb";
user="root";
password="123";

 System.out.println("S=======db Connecting======");

        try {
            Class.forName("com.mysql.jdbc.Driver");

            con = DriverManager.getConnection(dbhost, user, password);
            //if you are facing with SSl issue please try this 
            //con = DriverManager.getConnection("jdbc:mysql://192.168.23.100:3306/cardmaildb?useUnicode=yes&characterEncoding=UTF-8&useSSL=false",user, password);

        } catch (Exception e) {
            System.err.println("error in connection");
            e.printStackTrace();
        }
        System.out.println("E=======db Connecting======");
        return con;
    }

    public static void main(String[] args) {
        new DBConnection().getConnection();
    }
}

Upvotes: 1

Sadeq Dousti
Sadeq Dousti

Reputation: 3536

This error may also happen if Java tries to connect to MySQL over SSL, but something goes wrong. (In my case, I was configuring Payara Server 5.193.1 connection pools to MySQL.)

Some people suggested setting useSSL=false. However, since Connector/J version 8.0.13, that setting is deprecated. Here's an excerpt from MySQL Connector/J 8.0 Configuration Properties:

sslMode

By default, network connections are SSL encrypted; this property permits secure connections to be turned off, or a different levels of security to be chosen. The following values are allowed: DISABLED - Establish unencrypted connections; PREFERRED - (default) Establish encrypted connections if the server enabled them, otherwise fall back to unencrypted connections; REQUIRED - Establish secure connections if the server enabled them, fail otherwise; VERIFY_CA - Like REQUIRED but additionally verify the server TLS certificate against the configured Certificate Authority (CA) certificates; VERIFY_IDENTITY - Like VERIFY_CA, but additionally verify that the server certificate matches the host to which the connection is attempted.

This property replaced the deprecated legacy properties useSSL, requireSSL, and verifyServerCertificate, which are still accepted but translated into a value for sslMode if sslMode is not explicitly set: useSSL=false is translated to sslMode=DISABLED; {"useSSL=true", "requireSSL=false", "verifyServerCertificate=false"} is translated to sslMode=PREFERRED; {"useSSL=true", "requireSSL=true", "verifyServerCertificate=false"} is translated to sslMode=REQUIRED; {"useSSL=true" AND "verifyServerCertificate=true"} is translated to sslMode=VERIFY_CA. There is no equivalent legacy settings for sslMode=VERIFY_IDENTITY. Note that, for ALL server versions, the default setting of sslMode is PREFERRED, and it is equivalent to the legacy settings of useSSL=true, requireSSL=false, and verifyServerCertificate=false, which are different from their default settings for Connector/J 8.0.12 and earlier in some situations. Applications that continue to use the legacy properties and rely on their old default settings should be reviewed.

The legacy properties are ignored if sslMode is set explicitly. If none of sslMode or useSSL is set explicitly, the default setting of sslMode=PREFERRED applies.

Default: PREFERRED

Since version: 8.0.13

So, in my case, setting sslMode=DISABLED was all I needed to resolve the issue. This was on a test machine. But for production, the secure solution would be properly configuring the Java client and MySQL server to use SSL.

Notice that by disabling SSL, you might also have to set allowPublicKeyRetrieval=true. (Again, not a wise decision from security standpoint). Further information is provided in MySQL ConnectionString Options:

AllowPublicKeyRetrieval

If the user account uses sha256_password authentication, the password must be protected during transmission; TLS is the preferred mechanism for this, but if it is not available then RSA public key encryption will be used. To specify the server’s RSA public key, use the ServerRSAPublicKeyFile connection string setting, or set AllowPublicKeyRetrieval=True to allow the client to automatically request the public key from the server. Note that AllowPublicKeyRetrieval=True could allow a malicious proxy to perform a MITM attack to get the plaintext password, so it is False by default and must be explicitly enabled.

Upvotes: 8

Ravinath
Ravinath

Reputation: 1760

dbhost=jdbc:mysql://172.18.23.100:3306/yourdatabase?useUnicode=yes&characterEncoding=UTF-8&useSSL=false
user=root
password=Password#321

con = DriverManager.getConnection(dbhost, user, password);

if mysql version 8 or higher user updated connector

Upvotes: 2

shellbye
shellbye

Reputation: 4858

In my case, turn out to be that the version of mysql-connector-java was too old.

In my demo, I somehow use mysql-connector-java like this:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.9</version>
</dependency>

But in the develop environment, I use this:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.31</version>
</dependency>

And my MySQL version was 5.1.48(yes, it is old, just for mimic the product version). So I met the same error.

Since the reason is found, the solution is found, too. Match the version!

Upvotes: 11

10110
10110

Reputation: 2695

I was receiving multiple errors such as:

  • CommunicationsException: Communications link failure
  • java.lang.NullPointerException: Attempt to invoke interface method 'java.sql.Statement java.sql.Connection.createStatement()' on a null object reference at.

I had to add:

  • In AndroidManifest.xml include <uses-permission android:name="android.permission.INTERNET"/> just after the opening manifest tag.

  • Add the JDBC driver into your Gradle (or Maven) dependencies.

Upvotes: 1

Chase Owens
Chase Owens

Reputation: 96

Also your server might just be closed. On a Mac, navigate to mySQL preference panel through the spotlight. When there, check the box to start your server when your computer starts and start the server.

Upvotes: -2

FullStack Alex
FullStack Alex

Reputation: 2093

In my case I had to establish an ssh tunnel to the remote database and all the settings were correct and testing the connection with PhpStorm were also successful. And also the schema was loaded, but not the data. Instead I got: 

[08S01] 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.

No of the suggestions above worked. For any reason I tried to solve the problem by simply restarting PhpStorm and voila it worked!

Upvotes: 0

Prakhar Agrawal
Prakhar Agrawal

Reputation: 173

I was facing the same problem while i was starting my application, 

Caused by: 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.

The issue was that in the mysql file /etc/mysql/mysql.conf.d/mysqld.cnf, the configuration was like that

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
# By default we only accept connections from localhost
bind-address    = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

So as we can see that bind address is pointing to the 127.0.0.1, so for starting your application, a simple solution is that we can use the jdbc:mysql://127.0.0.1:3306/pizzeria instead of jdbc:mysql://localhost:3306/pizzeria which will connect the mysql to the application or another solution is that you can check and change the mysql configurations to default.Both solution can work. I hope it will work for you guys too.

Upvotes: 0

Related Questions