Sep 28, 2008

solve "com.mysql.jdbc.CommunicationsException"

This is the error that I got when trying to connect to mysql (of lampp package, yes in linux) using Hibernate in java.

WARN 2008-09-28 13:55:17,116 org.hibernate.cfg.SettingsFactory -s Could not obtain connection metadata
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.net.ConnectException
MESSAGE: Connection refused

STACKTRACE:

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:366)
at java.net.Socket.connect(Socket.java:519)
at java.net.Socket.connect(Socket.java:469)
at java.net.Socket.(Socket.java:366)
at java.net.Socket.(Socket.java:209)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:256)
at com.mysql.jdbc.MysqlIO.(MysqlIO.java:271)


Basically, the problem here is because of jdbc connector, which was unable to connect to the database. There are two ways of communication with mysql in linux.
  1. Don't listen on a TCP/IP port at all, if all processes that need to connect to mysqld run on the same host. There interaction with mysqld are made via Unix sockets or named pipes which is .sock file.

  2. B. Simply listen on a TCP/IP port. This allows remote communication to the database too.


Lampp initial configuration sets the first method of communication. And the sock file is created in < lampp root dir >/var/mysql/mysql.sock . But by default the jdbc search it in "/var/run/mysql/mysql.sock" (somewhere like this). Due to this problem, jdbc wont be able to connect to the database.

There are many ways to solve this:
The simple one is to switch to second type of communication method.
find the following line in <lampp root dir >/etc/my.cnf
skip-networking

and comment it.
# skip-networking


Hope this helps you out.

13 comments:

Anonymous said...

I am so glad you wrote today's blog. It has saved me a lot of time and trouble.

In addition to doing your #skip-networking move, I also put this in my my.cnf file:

port=3306

Again, many thanks.

Sudheer Krishna said...

If a want o use sockets , tehn how d mention the connection string in hibernate ?

Suraj Sapkota said...

Sudheer,
I am not clear about your question.

Though the above problem seems to be of hibernate. Actually it is not. Mysql is serving over UNIX socket (file mysqld.sock file) and the hibernate (java) is trying to communicate via socket with mysql.
What I did here is, forced mysql to listen on socket :) ... Thats it.

If you are asking me how to configure this via Hibernate (may be because you dont have access to my.cnf), well I must say, I don't know.

minty said...

thanks SATA for the wonderful post, it helps a lot.. one question, so does that mean that if we don't comment out the skip-networking entry, mysql would always communicate over UNIX socket, while on the hand, if we "#" the skip-networking entry, then both mysql and java can comm. through socket on port 3306 ?
Anyway, thanks again.

Suraj Sapkota said...

@minti,
Yes in all *nix system. But AFAIK, in windows system, mysql always listens on the port 3306.

Pranav Kumar Varshney said...

Most of the web hosting providers do not allow the second type of communication nethod (TCP / IP Port). Atleast my provider does not allow this.

So,I am left with Option 1, using Unix sockets. How do I connect to MySql using mysql.sock file through my Java web application? Any ideas?

Anonymous said...

My question was the same as above ..


I need to connect from my java layer uisng hibernate and i have given the connection string as

jdbc:mysql://localhost/DBNAME

in my unix system , i just installed mysql binary version by unzipping it and running the mysqld command .

I am able to connect to the db after logging into myserver uisng
mysql -u username -p password

and i also dont see any ports listening on 3306 with skip-networking commented(i used netstat -nlp to cehck the listening ports)

Suraj Sapkota said...

@pranav, @Anonymous
I am afraid, I don't have any suggestions to you guys.

yasaswy said...

hi

i am trying to connect to mysql from matlab and i am getting this error,i use winXP.

in my.cnf file what changes should i do?

plz help

Unknown said...

Hi SATA,

I am getting same Exception in my application. But the main problem is this exception sometimes come and sometimes not.

Exception is as below -

STACKTRACE:

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: Broken pipe

STACKTRACE:

java.net.SocketException: Broken pipe


I want to know the cause of this problem. Any help would be highly appreciated.

Regards,
Nilesh.

Unknown said...

Solved my problem like a magic. Thanks for the great tip.

Anonymous said...

My issue was that my AjaxSolr.Manager was setup incorrectly. By default, Solr listens on port 8983 but I had it listening on 1024. Here is the correct snippet:

Manager = new AjaxSolr.Manager({solrUrl: 'http://localhost:8983/solr/'});

Prakash said...

Dear Sata,
The same problem I am getting in some cases,and I am using windows machine.
Server Running in :Windows
Client Running in :Monta Vista Linux 5.0
Mysql Version :Mysql Server 5.0

Below is the error
------------------
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: Software caused connection abort: socket write error

STACKTRACE:

java.net.SocketException: Software caused connection abort: socket write error
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2744)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1612)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3277)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3206)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1232)


Can u kindly help me to solve the issue.

Thanks & Regards
Prakash B