How to recover from database restart while JBoss is running.

[bertnz]

Hi all,

Excuse me for being a bit green in the J2EE world but I have a JBoss server (3.2.5) running a J2EE application which uses jdbc/Hibernate/Sql Server/Quartz/JBossMQ JMS.

All works fine until the database this application is talking to crashes (or is reset by me).

At the time of the crash (or reset) I understand why I get big errors in my console.

However once I restart my sql server JBoss doesn't recover at all. It looks to me as if the connection pool in just keeps allocating connections from memory which have all been closed by the server crashing.

When I look at my activity for the DB JBoss has no connections to the DB at all and my scheduler (Quartz) keeps throwing these exceptions:

java.sql.SQLException: Invalid state, the Connection object is closed

Surely whatever is pooling these connections should be smart enough to handle an unexpected DB shutdown?

If someone can point me in the right direction here that would be great.

cheers
Robert Hughes

[bertnz]

OK - looks like I will have to solve this one myself.

Turns out that I needed to specify this additional field in my datasource descriptor (ms-sql.ds).

<!-- sql to call to check exiting connection is still ok -->
<check-valid-connection-sql>select top 1 name from sysobjects with (nolock)</check-valid-connection-sql>

This is apparently called before a pooled connection is used, if it fails (say because the connection is closed!!!!) - then the connection is released and a new one is used.

Thus - I can now reset my database server without my JBoss application dying a horrible horrible death.


Fantastic!