Tuesday, June 22, 2010

Connecting MySQL 5.1 Database Remotely Using JDBC

Recently I installed MySQL 5.1 on my Ubuntu Lucid Lynx and then was trying to connect to database from my windows machine and stuck with following exception

com.mysql.jdbc.CommunicationsException: Communications link failure

Last packet sent to the server was 0 ms ago.
     at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1070)
     at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2104)
     at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:729)
     at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:298)
     at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:283)
     at java.sql.DriverManager.getConnection(DriverManager.java:512)
     at java.sql.DriverManager.getConnection(DriverManager.java:171)
     at com.jak.sandbox.hsm.HSMTest.testJDBC(HSMTest.java:35)
     at com.jak.sandbox.hsm.HSMTest.main(HSMTest.java:24)
Caused by: java.net.ConnectException: Connection refused: connect
     at java.net.PlainSocketImpl.socketConnect(Native Method)
     at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:305)
     at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:171)
     at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:158)
     at java.net.Socket.connect(Socket.java:452)
     at java.net.Socket.connect(Socket.java:402)
     at java.net.Socket.(Socket.java:309)
     at java.net.Socket.(Socket.java:153)
     at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:256)
     at com.mysql.jdbc.MysqlIO.(MysqlIO.java:276)
     at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2027)
 ... 7 more

After googling a lot and reading may forums finally found solution from Ubuntu Forums. Here is the deal
  1. By default MySQL 5.1 allows only local connection
  2. Remote connections should explicitly be turned on
As with the previous version of MySQL, where server starts with --skip-networking to prevent all TCP/IP connections, MySQL 5.1 does this by means of configuration in my.cnf file. This file stores all the configuration on how server starts. Default configuration is to prevent all TCP/IP connections. This means that if you want your JAVA application to talk to MySQL database, you will see the above mentioned exception. This default configuration is displayed in below snapshot...


So to allow TCP/IP connections just comment out the line bind-address = 127.0.0.1 and restart the server. Settings mentioned here opens up connection from any IP. So not might be a very good configuration if we think in terms of database administration or security. But for a developer like me, this suffices the requirement. Regarding database administration and security, let me not poke my nose and leave it upto smart database administrators.

[UPDATE - 22 July 2010 16:48] - Found same issue (rather default configuration) for JBoss too. When you run JBoss application server with default configuration, it will not be accessible over internet/intranet. It will be accessible only on localhost. To make it available over the internet/intranet, start JBoss with run -b 0.0.0.0 and it should work.

Cheers !!!
- Jay
Reactions: 
Posted by Jay Khimani at 6/22/2010 05:58:00 PM
Labels: , , ,

2 comments:

  1. What if there is no "my.cnf" file? Also what security issues does this solution leave open?

    ReplyDelete

  2. I doubt if you have no my.cnf file. Its the startup file which mysql server uses to start the server. It has lots of required startup parameters. Probably you might want to see this link for different possible location of my.cnf file depending upon your OS - http://dev.mysql.com/doc/refman/5.1/en/option-files.html

    Regarding bind-address parameter, it depends on how you intend to use. If you just comment it out, its open for all connections. If you know that only one application server is going to connect then you can provide address of that server or even range of IP addresses. For more details check below link.
    http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_bind-address

    Hope this helps

    ReplyDelete

Subscribe to: Post Comments (Atom)
 
Disclaimer: This is a personal blog and all content represent what I think and it does not advocate/support/advertise any other person/company. I do not earn money or intended to do so with this blog or any of the contents the blog hosts. If I post something here that you find helpful, that's wonderful. Just in case, if I say something stupid, the stupidity is mine, and mine alone and I can not be held for anything if you fall for such stupidity :-). I cannot be held responsible for any kind of damage that may be caused by downloading or viewing the files or information provided herewith. Anybody and everybody can use/refer the contents of this blog at their own will and of course at own risk. There is no need for any kind of approval of the author. Although it would be great if feedback is left for any such usage to the author