Re: connection reset error, followed by broken pipe (pg74.213.jdbc3.jar)

Hi Markus,

Thankyou for your assistance.

> So it seems that the network connection breaks from time to time
> Independently from making your application more robust, you might want
> to put some research into that.

Yes, I put a minutely ping script in place, which wasn't able to
detect a failure at a time when the connection was lost. The servers
are dedicated server (installed and hosted by another company), so
I've been trying to detect any outages to report to them.

> If you're using the tomcat connection pooling, my proposed solution
> there might help you

Thanks, I tried the following settings based on your idea looking for
similar tomcat settings on google:

In webapps/ourapp/META-INF/context.ml, adding:
<parameter>
<name>testOnBorrow</name>
<value>true</value>
</parameter>
<parameter>
<name>testOnReturn</name>
<value>true</value>
</parameter>
<parameter>
<name>validationQuery</name>
<value>select version()</value>
</parameter>

I wasn't able to nicely induce the error through our firewall or
taking connections down, but restarting postgresql did lead to a
broken pipe error. The above fix didn't help in this scenerio.

Reading other posts it seems that the jdbc driver is not expected to
reopen connections, therefore it doesn't seem using a newer version
will help. Given that our tomcat app doesn't reconnect to a
postgresql db after it (the database) has been restarted, is it
correct I need to work with the app developer as to how we use the
connection pool, or are there configuration changes that can help?

Regards,
Ed

Ed Blackmore wrote:
> Hi Markus,
>
> Thankyou for your assistance.
>
>
>>So it seems that the network connection breaks from time to time
>>Independently from making your application more robust, you might want
>>to put some research into that.
>
>
> Yes, I put a minutely ping script in place, which wasn't able to
> detect a failure at a time when the connection was lost. The servers
> are dedicated server (installed and hosted by another company), so
> I've been trying to detect any outages to report to them.

One common cause of connection loss is if there is a stateful firewall
or NAT between your client and server .. if the firewall loses the
connection state for some reason (e.g. if the connection goes idle for a
while and the firewall times it out, or if the firewall is restarted),
then the connection will break.

-O

Hi, Ed,

Ed Blackmore wrote:

> I wasn't able to nicely induce the error through our firewall or
> taking connections down, but restarting postgresql did lead to a
> broken pipe error. The above fix didn't help in this scenerio.
>
> Reading other posts it seems that the jdbc driver is not expected to
> reopen connections, therefore it doesn't seem using a newer version
> will help. Given that our tomcat app doesn't reconnect to a
> postgresql db after it (the database) has been restarted, is it
> correct I need to work with the app developer as to how we use the
> connection pool, or are there configuration changes that can help?

Yes, that's true.

My workaround protects against a pool handing out broken connections[1],
but the Application still must get a fresh connection ittself.

Most JSP pages and Servlets I've seen get such a connection on every
incoming request, most Servlets I've written refresh their connection
when an appropriate SQL exception happens.

I think that this is the way your app has to be modified - refresh the
SQL connection in case of an Exception.

HTH,
Markus

[1] Before java 1.6, the pool has no (easy and portable) way to tell
whether a connection is still valid.

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org