Обсуждение: After server restart I get - An I/O error occured while sending to the backend.

Hi,

I have simple JDBC client app running on Jetty.
Using 8.3 JDBC3 driver and PostgreSQL8.3 server running on localhost.

Any time I restart the PostgresQL server I get SQL error:
An I/O error occured while sending to the backend.

It appears that my application's connection to the server is not
re-established by some reason?

I am no expert in JDBC.
First I was using plan JDBC. I thought that using DB connection pool
will automagically reconnect and solve this problem.
Switching to Apache commons DBCP did not change anything.

What am I supposed to do to make it survive PostgreSQL server restart?

Here is my DB connection manager class:

public class DatabaseProvider {
    private static Connection connection;

    public void init() throws SQLException {

        connection = createConnection();
}

    private static Connection createConnection() throws SQLException{
        try {
            Class.forName(ConfigurationServlet.getConfigurationProperty("database.driver"));
        } catch (ClassNotFoundException e) {
            System.err.println("Database Driver class not found: " + e.getMessage());
            throw new SQLException("Driver class not found: "+e.getMessage());
        }


        DataSource dataSource =
setupDataSource(ConfigurationServlet.getConfigurationProperty("database.url"));
        Connection conn = dataSource.getConnection();
        //Connection conn = DriverManager.getConnection(url,dbprops );
        return conn;
    }

public static DataSource setupDataSource(String connectURI) {
              BasicDataSource ds = new BasicDataSource();
              ds.setDriverClassName(ConfigurationServlet.getConfigurationProperty("database.driver"));

             ds.setUrl(connectURI);
             return ds;
        }

    public void shutdown() {
        try {
            connection.close();
        } catch (SQLException e) {
            System.err.println("Error closing connection: "+e.getMessage());
        }
    }

    public static Connection getConnection() {
        return connection;
    }
}

On 18/05/2010 6:41 PM, Rod wrote:
> Hi,
>
> I have simple JDBC client app running on Jetty.
> Using 8.3 JDBC3 driver and PostgreSQL8.3 server running on localhost.
>
> Any time I restart the PostgresQL server I get SQL error:
> An I/O error occured while sending to the backend.
>
> It appears that my application's connection to the server is not
> re-established by some reason?

Pooled connections usually don't get cleaned up until they're discovered
to be dead. That happens when you try to use them and they break.

Just be prepared for this to happen. If you get a bad connection, grab a
new one and re-try your work.

Yes, you can "test" the connection first ... but something might go
wrong with it between testing it and starting to use it. You need to be
prepared to handle failure at any point and re-try your work. Make sure
to keep your work separated into clear units of work (wrapped as
transactions) so you have a clear idea of what you're up to.

>
> I am no expert in JDBC.
> First I was using plan JDBC. I thought that using DB connection pool
> will automagically reconnect and solve this problem.
> Switching to Apache commons DBCP did not change anything.
>
> What am I supposed to do to make it survive PostgreSQL server restart?

It can't, just as it can't survive connection loss for other reasons.
Your code must be prepared to deal with that.

--
Craig Ringer

I find it hard to believe! We live in 21st century, year 2010.
Electric cars and black president. And there's still no connection
pool library that keeps track if connection is valid or not?


On Tue, May 18, 2010 at 9:42 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> On 18/05/2010 6:41 PM, Rod wrote:
>>
>> Hi,
>>
>> I have simple JDBC client app running on Jetty.
>> Using 8.3 JDBC3 driver and PostgreSQL8.3 server running on localhost.
>>
>> Any time I restart the PostgresQL server I get SQL error:
>> An I/O error occured while sending to the backend.
>>
>> It appears that my application's connection to the server is not
>> re-established by some reason?
>
> Pooled connections usually don't get cleaned up until they're discovered to
> be dead. That happens when you try to use them and they break.
>
> Just be prepared for this to happen. If you get a bad connection, grab a new
> one and re-try your work.
>
> Yes, you can "test" the connection first ... but something might go wrong
> with it between testing it and starting to use it. You need to be prepared
> to handle failure at any point and re-try your work. Make sure to keep your
> work separated into clear units of work (wrapped as transactions) so you
> have a clear idea of what you're up to.
>
>>
>> I am no expert in JDBC.
>> First I was using plan JDBC. I thought that using DB connection pool
>> will automagically reconnect and solve this problem.
>> Switching to Apache commons DBCP did not change anything.
>>
>> What am I supposed to do to make it survive PostgreSQL server restart?
>
> It can't, just as it can't survive connection loss for other reasons. Your
> code must be prepared to deal with that.
>
> --
> Craig Ringer
>

Rod wrote on 22.05.2010 09:31:
> I find it hard to believe! We live in 21st century, year 2010.
> Electric cars and black president. And there's still no connection
> pool library that keeps track if connection is valid or not?
>
Depends on the connection pool.

DBCP has configuration option "testOnBorrow" where the pool will verify that the connection is OK before handing it
out.
I'm sure C3PO has a similar option (to name the two most popular ones)

Thomas

On 22/05/2010 3:31 PM, Rod wrote:
> I find it hard to believe! We live in 21st century, year 2010.
> Electric cars and black president. And there's still no connection
> pool library that keeps track if connection is valid or not?

Sure, there are plenty of connection pools that *try*.

It's fundamentally impossible to do it properly, though, as the
connection can die at *any* time. Including between when the connection
pool tests it and when it hands it to the application for use. This is a
race condition that you just can't solve.

For that matter, the connection can die while the app is using it -
someone trips over an Ethernet cable (or a server's power cable), a
laptop goes out of wifi range, whatever.

So - a connection pool testing for connection validity is only a
convenience. The app must still be prepared to handle the connection
breaking at any time. Given that, why bother testing it with the
connection pool? It only offers a false sense of security, and wastes
resources polling unused connections.

I suppose it'd be possible to produce a Connection object wrapper that
recorded all operations sent through it and if the underlying connection
dropped replayed those operations to hide the connection loss from the
application. However, the new connection couldn't obtain the same
snapshot of the database state as the old one so the replay couldn't
guarantee it'd have the same effects as the original run of those same
statements. It'd be extremely unsafe at best.

Now, maybe if the database kept the snapshot and connection state for
lost connections around in case the client re-connected to pick up where
it left off, then it'd be possible*. Hell, the database could even save
that state and restore it after a database server reload. The complexity
involved would be really ugly, though, and it'd only be of use to poorly
behaved apps that can't gracefully handle connection failure. Even then
it wouldn't save you from unexpected situations like server power loss,
postgresql backend/server unexpected termination, etc, so the app/pool
STILL needs to be able to gracefully cope with having all its work
yanked out from under it.

This is one of the reasons client/server transactional SQL programming
can be more complicated than everything-on-one-machine shared file
database programming. On the other hand, that could go wrong too, just
not as easily so people pretended it couldn't and hoped it never would.

--
Craig Ringer

Tanks for the replies.
To test the connection, what statement or command should I execute?
Anyone have code sample?

On Sat, May 22, 2010 at 7:57 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> On 22/05/2010 3:31 PM, Rod wrote:
>>
>> I find it hard to believe! We live in 21st century, year 2010.
>> Electric cars and black president. And there's still no connection
>> pool library that keeps track if connection is valid or not?
>
> Sure, there are plenty of connection pools that *try*.
>
> It's fundamentally impossible to do it properly, though, as the connection
> can die at *any* time. Including between when the connection pool tests it
> and when it hands it to the application for use. This is a race condition
> that you just can't solve.
>
> For that matter, the connection can die while the app is using it - someone
> trips over an Ethernet cable (or a server's power cable), a laptop goes out
> of wifi range, whatever.
>
> So - a connection pool testing for connection validity is only a
> convenience. The app must still be prepared to handle the connection
> breaking at any time. Given that, why bother testing it with the connection
> pool? It only offers a false sense of security, and wastes resources polling
> unused connections.
>
> I suppose it'd be possible to produce a Connection object wrapper that
> recorded all operations sent through it and if the underlying connection
> dropped replayed those operations to hide the connection loss from the
> application. However, the new connection couldn't obtain the same snapshot
> of the database state as the old one so the replay couldn't guarantee it'd
> have the same effects as the original run of those same statements. It'd be
> extremely unsafe at best.
>
> Now, maybe if the database kept the snapshot and connection state for lost
> connections around in case the client re-connected to pick up where it left
> off, then it'd be possible*. Hell, the database could even save that state
> and restore it after a database server reload. The complexity involved would
> be really ugly, though, and it'd only be of use to poorly behaved apps that
> can't gracefully handle connection failure. Even then it wouldn't save you
> from unexpected situations like server power loss, postgresql backend/server
> unexpected termination, etc, so the app/pool STILL needs to be able to
> gracefully cope with having all its work yanked out from under it.
>
> This is one of the reasons client/server transactional SQL programming can
> be more complicated than everything-on-one-machine shared file database
> programming. On the other hand, that could go wrong too, just not as easily
> so people pretended it couldn't and hoped it never would.
>
> --
> Craig Ringer
>

I've written a wrapper that use the "setTransationIsolation" method as
first operation against a just obtained connection to implicitly check
if it is still alive.

On 05/22/2010 03:06 PM, Rod wrote:
> Tanks for the replies.
> To test the connection, what statement or command should I execute?
> Anyone have code sample?
>
> On Sat, May 22, 2010 at 7:57 PM, Craig Ringer
> <craig@postnewspapers.com.au> wrote:
>
>> On 22/05/2010 3:31 PM, Rod wrote:
>>
>>> I find it hard to believe! We live in 21st century, year 2010.
>>> Electric cars and black president. And there's still no connection
>>> pool library that keeps track if connection is valid or not?
>>>
>> Sure, there are plenty of connection pools that *try*.
>>
>> It's fundamentally impossible to do it properly, though, as the connection
>> can die at *any* time. Including between when the connection pool tests it
>> and when it hands it to the application for use. This is a race condition
>> that you just can't solve.
>>
>> For that matter, the connection can die while the app is using it - someone
>> trips over an Ethernet cable (or a server's power cable), a laptop goes out
>> of wifi range, whatever.
>>
>> So - a connection pool testing for connection validity is only a
>> convenience. The app must still be prepared to handle the connection
>> breaking at any time. Given that, why bother testing it with the connection
>> pool? It only offers a false sense of security, and wastes resources polling
>> unused connections.
>>
>> I suppose it'd be possible to produce a Connection object wrapper that
>> recorded all operations sent through it and if the underlying connection
>> dropped replayed those operations to hide the connection loss from the
>> application. However, the new connection couldn't obtain the same snapshot
>> of the database state as the old one so the replay couldn't guarantee it'd
>> have the same effects as the original run of those same statements. It'd be
>> extremely unsafe at best.
>>
>> Now, maybe if the database kept the snapshot and connection state for lost
>> connections around in case the client re-connected to pick up where it left
>> off, then it'd be possible*. Hell, the database could even save that state
>> and restore it after a database server reload. The complexity involved would
>> be really ugly, though, and it'd only be of use to poorly behaved apps that
>> can't gracefully handle connection failure. Even then it wouldn't save you
>> from unexpected situations like server power loss, postgresql backend/server
>> unexpected termination, etc, so the app/pool STILL needs to be able to
>> gracefully cope with having all its work yanked out from under it.
>>
>> This is one of the reasons client/server transactional SQL programming can
>> be more complicated than everything-on-one-machine shared file database
>> programming. On the other hand, that could go wrong too, just not as easily
>> so people pretended it couldn't and hoped it never would.
>>
>> --
>> Craig Ringer
>>
>>
>

On 23/05/10 06:48, Daniele Depetrini wrote:
> I've written a wrapper that use the "setTransationIsolation" method as
> first operation against a just obtained connection to implicitly check
> if it is still alive.

Yep, or you can SHOW a variable if you like. That won't mess with
ongoing transaction state, won't create a snapshot (like SELECT 1;
might), etc.

Again, though, remember that you still need to be prepared to handle
connections dying while you're in the middle of doing work.

--
Craig Ringer

Rod wrote:
> To test the connection, what statement or command should I execute?
> Anyone have code sample?

The one you intend to execute to fulfill your program logic.

E.g.,
  "SELECT id, handle, attribute FROM foo"

--
Lew

Daniele Depetrini wrote:
> I've written a wrapper that use[s] the "setTransationIsolation" method as
> first operation against a just[-]obtained connection to implicitly check
> if it is still alive.

And what if the connection dies just after you've set transaction isolation
but before you do anything else with it?

As Craig Ringer pointed out, that technique does not relieve following code of
the responsibility to check for exceptions due to a lost connection, so what
does it buy you but the performance hit of an extra round trip to the database?

--
Lew