Connecting to SQL via JDBC to an IP hosting MySQL Server

Question

I am trying to connect to an MYSQL database using JDBC. In my.conf file I have commented out the bind-address option. I am using phpmyadmin. In MYSQL privileges table, it shows that

user : sumitb

host : %

Password : Yes

Global Priveleges : ALL PRIVILEGES

Grant : Yes

But the following code gives me error :

   try {
        System.out.println("Loading driver...");
        Class.forName("com.mysql.jdbc.Driver");
        System.out.println("Driver loaded!");
    } catch (ClassNotFoundException e) {
        throw new RuntimeException("Cannot find the driver in the classpath!", e);
    }

    String url = "jdbc:mysql://172.27.20.124:3306/synsetSimilarity"; // Important
    String username = sumitb;
    String password = StaticValues.sqlPassword;
    Connection connection = null;
    try {
        System.out.println("Connecting database...");
        connection = DriverManager.getConnection(url, username, password);
        System.out.println("Database connected!");                      
    } catch (SQLException e) {
        e.printStackTrace();
        throw new RuntimeException("Cannot connect the database!", e);
    } finally {
        System.out.println("Closing the connection.");
        if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
    }
}

I get the following error which I am not able to understand. If I change the ip to localhost or 127.0.0.1, it works fine.

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:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:350)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2408)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2445)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2230)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:813)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:399)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:334)
at java.sql.DriverManager.getConnection(DriverManager.java:579)
at java.sql.DriverManager.getConnection(DriverManager.java:221)
at krsystem.ontology.wekaClustering.PopulateSimilarityDB.test(PopulateSimilarityDB.java:137)
at krsystem.ontology.wekaClustering.PopulateSimilarityDB.main(PopulateSimilarityDB.java:243)
Caused by: java.net.ConnectException: Connection refused
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:339)
at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:200)
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:182)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:391)
at java.net.Socket.connect(Socket.java:579)
at java.net.Socket.connect(Socket.java:528)
at java.net.Socket.<init>(Socket.java:425)
at java.net.Socket.<init>(Socket.java:241)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:259)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:300)
... 16 more

Any help would be appreciated.

Update : On trying from terminal, I get the following error

 $mysql -u sumitb -h 172.27.20.124 -p
 Enter password: 
 ERROR 2003 (HY000): Can't connect to MySQL server on '172.27.20.124' (111

Are you able to connect to the server from work bench or mysql admin GUI? — Ravindra Gullapalli, Mar 16 '13 at 07:43
Check out this => http://stackoverflow.com/questions/2983248/com-mysql-jdbc-exceptions-jdbc4-communicationsexception-communications-link-fai?answertab=votes#tab-top — tmwanik, Mar 16 '13 at 07:43
Are you sure your hosting provider allows connections from outside of its hosting servers. It's quite rare to allow that. — JB Nizet, Mar 16 '13 at 07:44
It looks like you have a connectivity issue because of lack of visibility to that server / port — Miguel Prz, Mar 16 '13 at 07:45
@RavindraGullapalli : I am able to connect to mysql admin via GUI. I am using phpmyadmin for the same. — damned, Mar 16 '13 at 07:45
Try to connect to the mysql server using the mysql command line client from the machine you are running this code on and see if it works. — Abhinav Sarkar, Mar 16 '13 at 07:47
@JBNizet : How to check whether its allowing connections or not? I am hosting it on my computer itself. — damned, Mar 16 '13 at 07:48
By reading the hosting provider terms of services and documentation. Or by asking them directly. — JB Nizet, Mar 16 '13 at 07:50

score 2 · Accepted Answer · answered Mar 16 '13 at 08:00

did you restart mysql after commenting out "the bind-address option"?

if your mysql server is running on the same machine as your client, why not use 127.0.0.1 as the ip instead of 172.27.20.124?

if you insist on using 172.27.20.124, check that the mysql port is open. you can use telnet from the command line:

telnet 172.27.20.124 3306

and see if it's open. or use nmap. the error you are getting says that your client isn't able to even talk to your mysql server.

also, as Abhinav Sarkar said, try to connect to mysql from the command line specifying your host like you do in java and see if that works. try specifying the host as 127.0.01 if you are indeed running mysql locally as well (mysql -u sumitb -h 127.0.0.1 -p)

I didn't restart it. It worked after restarting mysql server. Thanks. — damned, Mar 16 '13 at 10:11

Connecting to SQL via JDBC to an IP hosting MySQL Server

1 Answers1