Broken pipe Error

[Kumara Swamy]

Hi,
In my application iam getting the broken pipe error frequently. due to this connection pooling is not working. without connection pool the frequency of error occurance is less.
Can any one help me on this.

jdbc connection url=jdbc:mysql://localhost:3306/livepbx?autoReconnect=true
in mysql wait_timeout=120

is it problem with mysql settings?

i am using mysql5.x, tomcat5.x


The error is :::
============
Communications link failure due to underlying exception: ** BEGIN NESTED EXCEPTION ** java.net.SocketException MESSAGE: Broken pipe STACKTRACE: java.net.SocketException: Broken pipe 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:2692) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2621) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1552) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666) at com.mysql.jdbc.Connection.execSQL(Connection.java:2994) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:936) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1030) ...


Thanks n Regards
Kumar.

[Bauke Scholtz]

The connection (pipe) got lost (broken). This can have several causes. Other side crashed, other side restarted, other side went down, there is poor network hardware/software quality (cable, card, firewall, router, proxy, etc), the JDBC driver is outdated/buggy, the JVM is behaving mad. Doublecheck the complete route of the network packets at both hardware and software level.

[Kumara Swamy]

Hi,

Thanks for your valuable reply.

There is no restart/crash on my sql server side.
We are using the mysql connector-3.1.12, jdk1.5.02
As per your suggestions we would grep the network packets and let you know.

Regards,
Kumara Swamy.

[Kumara Swamy]

Hi,
My d/b server is configured with connection time out value as 3seconds.
I used the ngrep technique and debugged the issue but i was unable to find the resolution.
There was no issue with database server.
Web server is sending a request to d/b server with already closed connection object and its causing the issue.
Can any one help how to resolve the issue?

my code is as follows:
======================

String _conString = "jdbc:mysql://localhost:3306/livepbx";
String _user = "mysql";
String _password = "logme";
Connection con = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(_conString+"?user="+_user+"&password="+_password+"&autoReconnect=true");

// starting point of block abc
if(con != null) {
stmt = con.createStatement();
stmt.executeQuery("SELECT NOW()");
} else {
System.out.println("connection is null ::::::::::::::::::::::: "+con);
}
// end point for block abc

//halting the execution for 15secs.
Thread.sleep(1000*15);


// starting point of block xyz
if(con != null) {

stmt = con.createStatement();
stmt.executeQuery("SELECT NOW()");
} else {
System.out.println("connection is null after time out::::::::::::::::::::::: "+con);
}
// end point for block xyz
} catch(Exception e){
System.out.println("error ::::::::::::::::::::::: "+e);
}
===================================

The mysql configuration values:
mysql wait_timeout=3;
interactive_timeout =3;

when ever we are executing the block abc its perfectly working.

when ever we are executing the block xyz.
we are able to get a statement obj on a Connection object even after 15seconds of connection establish.
then if we tried to execute the stmt.executeQuery("SELECT NOW()"); then its throwing the following exception:

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

** BEGIN NESTED EXCEPTION **

java.io.EOFException

STACKTRACE:

java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1905)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2351)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2862)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2988)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2917)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:824)
at org.apache.jsp.testconnection_jsp._jspService(testconnection_jsp.ja
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapp
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Ap
at org.apache.catalina.core.ApplicationFilterChain.doFilter(Applicatio
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrappe
at org.apache.catalina.core.StandardContextValve.invoke(StandardContex
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve
at org.apache.catalina.valves.FastCommonAccessLogValve.invoke(FastComm
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineV
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.j
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.ja
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEnd
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderF
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(Threa
at java.lang.Thread.run(Thread.java:595)


** END NESTED EXCEPTION **



Last packet sent to the server was 1 ms ago.

[Ulf Dittmer]

Why are you creating a new Statement object? Either re-use the existing one, or close it before creating a new one for the same connection.

[Kumara Swamy]

Hi,
Even we use the same statement object or closed it and created a new statement there is no difference.
My intention to create a new statement object is:
even after connection time out we are able to create a statement object by using the same connection(here there is no involvement of mysql server) but when ever we trying to execute the query then the mysql server comes into picture and throws communication exception.

so now my question is : Is there any way to find whether the connection object is already expired or not before getting a statement object on that.

Regards,
Kumara Swamy.

[Sagar Rohankar]

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

Read this and try changing the MySQL url parameters.

Kumara Swamy wrote:
so now my question is : Is there any way to find whether the connection object is already expired or not before getting a statement object on that.

If you're using Apache DBCP, then you can track abandoned db connections and can use "validationQuery" parameter to check whether the connection object is alive or not. If no DBCP, then Follow this link and find out if any parameter can be helpful.

[Kumara Swamy]

Hi Mr.Sagar Rohankar,

Thanks a lot you resolved the issue.
The urls provided by you has the exact content what i am expecting and its resolved the issue.

thank you very much to the community.

Regards,
Kumara Swamy.

[Sagar Rohankar]

Kumara Swamy wrote:Hi Mr.Sagar Rohankar,
Thanks a lot you resolved the issue.
The urls provided by you has the exact content what i am expecting and its resolved the issue.

I'm glad to know that

Kumara Swamy wrote:
thank you very much to the community.

And as usual, JR Rocks !!

[PavanPL KalyanK]

Kumara Swamy wrote:
in mysql wait_timeout=120

If we specify this in mysql wait_timeout=120 parameter and if this time exceeds while connecting to the Connection .

Do we get an exception ?? Can we handle this exception in our Application.

[Sagar Rohankar]

PavanPL KalyanK wrote:
Do we get an exception ?? Can we handle this exception in our Application.

Of course we get an exception when server gone away when wait_timeout has been expired and like any other Java exception we can handle this too, using try.. catch..

[PavanPL KalyanK]

Sagar,
So we get an SQLException or more specific exception when this problem arises??

[Sagar Rohankar]

Most possibly, this exception

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

[PavanPL KalyanK]

Thanks Sagar.

You will definately get a Privilege permisions .

[Sagar Rohankar]

PavanPL KalyanK wrote:
You will definately get a Privilege permisions .

[Kumara Swamy]

Sagar Rohankar wrote:

PavanPL KalyanK wrote:
Do we get an exception ?? Can we handle this exception in our Application.

Of course we get an exception when server gone away when wait_timeout has been expired and like any other Java exception we can handle this too, using try.. catch..

I have a code which is not in try/catch like as follows:
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306?user=user&password=password&autoReconnect=true");
if(con != null) {
ResultSet rs = null;
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT NOW()");
if(rs.next()) {
String timeStr = rs.getString("NOW()");
}
}
so i usually gets com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: even autoReconnect flag is true

my application has similar snippet at lot of places so i need to replace every where with the following code:
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306?user=user&password=password&autoReconnect=true");
if(con != null) {
ResultSet rs = null;
try {
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT NOW()");
} catch(Exception e) {
rs = stmt.executeQuery("SELECT NOW()");
}
if(rs.next()) {
String timeStr = rs.getString("NOW()");
}
}

this takes lot of time and effort to do.
Is there any way to fulfill our requirement with a simple change like connection url change?

[Sagar Rohankar]

Really, didn't get what you want ? Do you want to say,
How can I change the URL string at every connection code (Which is not best JDBC practice, more than one connection code)
OR
How can I enclose a connection code in try .. catch block?

For this above two problem, your IDEs, 'Find and Replace' method will works

[Kumara Swamy]

Hi Sagar,

Sagar Rohankar wrote:Really, didn't get what you want ? Do you want to say,
How can I change the URL string at every connection code (Which is not best JDBC practice, more than one connection code)

For this snippet only i created the connection there itself. Usually we used to get the connection from a unique class.
Changing the jdbc url at one place would reflect my whole applicartion.

Sagar Rohankar wrote:Really, didn't get what you want ? Do you want to say,
How can I enclose a connection code in try .. catch block?
For this above two problem, your IDEs, 'Find and Replace' method will works

My question is instead of enclosing the execution code in try/catch do we have any alternative?
because we have lot of class files and jsps which are having the execution code.

if we have a solution by changing jdbc url then it is a simple chage at one location. otherwise i have to enclose the execution code with try/catch in more than 1000 files(jsps, classes).

[Sagar Rohankar]

Look, I don't have any idea about How your current code is handling exception. So its difficult for me to suggest any alternatives and if you changed the db url, its dosen't have any effect on your connection class, provided the url is correct.

Kumara Swamy wrote: My question is instead of enclosing the execution code in try/catch do we have any alternative?

The methods which you're invoking in a connection class, throws SQLException and if this current code is working one, then your methods MUST declared with throws clause. Look for the method which calls/create connection object..

[Kumara Swamy]

Hi,
I will look into my code once again.
Thank you Mr.Sagar for your support to resolve my issue.