DB Connection lost - after DB Server reboot

[gabe12]

Hi,

i develop a web application on WebSphere 5.1 and every night the db server goes down for backup. after that on every sql call i get

org.springframework.dao.DataAccessResourceFailureException: PreparedStatementCallback; SQL [SELECT * FROM foo where bar order by foobar desc]; Io exception: There is no process to read data written to a pipe.; nested exception is java.sql.SQLException: Io exception: There is no process to read data written to a pipe.
     org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:231)
     org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java(Compiled Code))
     org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java(Compiled Code))
     org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java(Inlined Compiled Code))
     org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java(Inlined Compiled Code))
     org.springframework.jdbc.object.SqlQuery.execute(SqlQuery.java(Inlined Compiled Code))
     org.springframework.jdbc.object.SqlQuery.execute(SqlQuery.java(Inlined Compiled Code))
     org.springframework.jdbc.object.SqlQuery.execute(SqlQuery.java(Compiled Code))

until i reboot the webapp.


my question is how do i get spring to reconnect to my oracle DB.

cheerio
gabe

[Andreas Senft]

Actually this is no Spring issue. It has to do with the configuration of your connection pool. You have to configure your connection pool to perform a check on the pooled connections (either on hand-out or periodically). This is the only way to find out if connections are broken. Maybe you have to use another connection pool if your implementation does not support this.

One pool implementation I know is able to perform checks is c3p0.

Regards,
Andreas

[gabe12]

thank you for replying.

but isnt the websphere connection pool the one who should do those things?

is it possible to use c3p0 and the websphere jndi datasource?

[Andreas Senft]

but isnt the websphere connection pool the one who should do those things?

Yes you are right. It seems I overlooked that part in your post.

Nevertheless it is true that you have to configure the appropriate settings. I have not worked with Websphere yet, but from Weblogic I know that such checks are disabled by default.
So have a look at your appserver's connection-pool configuration and see how to enable the appropriate checks.

Regards,
Andreas

[gabe12]

i found some promising properties

it is possible to change the purge policy from

failingConnectionOnly

to

entirePool

and it there is a property called

preTestSQLString

where an sql statement can be passed to validate the connections.

i'll give it a try and will report the sucess or failures ;-)

[gabe12]

it did not work.

i think this is an application issue not configuration.

websphere is known to keep the stale connections.

[Andreas Senft]

Does your application require distributed transactions, or do you access only a single database?

In the latter case you could as well dismiss the container configured datasource and define one yourself in your application context. In that case you could use a pooling implementation that provides a working connection checking like, say, c3p0.

Regards,
Andreas

[gabe12]

i'll try c3p0.

sounds simple and good.

[Costin Leau]

c3p0 documentation is pretty straight forward and you should not have any problems setting it up. However, if you have, search the forum - you should find examples for c3p0 and also commons-dbcp.