Problem detecting failed database

[Anonymous]

I have a two node cluster using PostgreSQL and ha-jdbc running in JBoss. One
database is running on the same node as JBoss and the other on a remote
server. When I stop the PostgreSQL server on the remote box the entire
database cluster fails. I would have expected that ha-jdbc would detect the
failed database, deactivated it, and continued using only the one database
until the remote database came back online and was re-activated. My database
cluster is defined as:

<ha-jdbc>
<distributable/>
<sync id="diff" class="net.sf.hajdbc.sync.DifferentialSynchronizationStrategy">
<property name="fetchSize">1000</property>
<property name="maxBatchSize">100</property>
</sync>
<cluster
balancer="simple"
default-sync="diff"
dialect="postgresql"
meta-data-cache="none"
transaction-mode="serial"
auto-activate-schedule="0 * ? * "
failure-detect-schedule="0 * ? * "
detect-identity-columns="true"
detect-sequences="true"

<database id="vicads1" weight="100" local="true">
<driver>org.postgresql.Driver</driver>
<url>jdbc:postgresql://localhost/vicads</url>
<user>postgres</user>
<password>postgres</password>
</database>
<database id="vicads2" weight="0" local="false">
<driver>org.postgresql.Driver</driver>
<url>jdbc:postgresql://192.168.111.75/vicads</url>
<user>postgres</user>
<password>postgres</password>
</database>
</cluster>
</ha-jdbc>

The cluster is defined in JBoss as:

<datasources>
<local-tx-datasource>
<jndi-name>VicadsDS</jndi-name>
<connection-url>jdbc:ha-jdbc:VICADS99</connection-url>
<user-name>postgres</user-name>
<password>postgres</password>
<driver-class>net.sf.hajdbc.sql.Driver</driver-class>
<min-pool-size>2</min-pool-size>
<max-pool-size>40</max-pool-size>
<new-connection-sql>SELECT 1</new-connection-sql>
<check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
<connection-property name="loginTimeout">5</connection-property>
<connection-property name="cluster">VICADS99</connection-property>
<connection-property name="config">file:C:\VICADSBoss/server/vicads/deploy/ha-
jdbc-vicads-cluster.xml</connection-property>
</local-tx-datasource>
</datasources>

Am I misunderstanding what ha-jdbc is doing or am I doing something wrong?

Thanks in advance, David

[Anonymous]

Here are the errors I receive:

2011-11-14 09:10:14,343 pool-16-thread-1 WARN
net.sf.hajdbc.sql.AbstractDatabaseCluster Database vicads2 from cluster
VICADS99 is not responding.
org.postgresql.util.PSQLException: Connection refused. Check that the hostname
and port are correct and that the postmaster is accepting TCP/IP connections.
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionF
actoryImpl.java:122)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java
:66)
at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection
.java:125)
at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection
.java:30)
at org.postgresql.jdbc3.Jdbc3Connection.<init>(Jdbc3Connection.java:24)
at org.postgresql.Driver.makeConnection(Driver.java:382)
at org.postgresql.Driver.connect(Driver.java:260)
at net.sf.hajdbc.sql.DriverDatabase.connect(DriverDatabase.java:127)
at net.sf.hajdbc.sql.DriverDatabase.connect(DriverDatabase.java:40)
at net.sf.hajdbc.sql.AbstractDatabaseCluster.test(AbstractDatabaseCluster.java
:248)
at net.sf.hajdbc.sql.AbstractDatabaseCluster.isAlive(AbstractDatabaseCluster.j
ava:230)
at net.sf.hajdbc.sql.AbstractDatabaseCluster$2.call(AbstractDatabaseCluster.ja
va:192)
at net.sf.hajdbc.sql.AbstractDatabaseCluster$2.call(AbstractDatabaseCluster.ja
va:189)
at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: java.net.ConnectException: Connection refused: connect
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(Unknown Source)
at java.net.PlainSocketImpl.connectToAddress(Unknown Source)
at java.net.PlainSocketImpl.connect(Unknown Source)
at java.net.SocksSocketImpl.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at java.net.Socket.<init>(Unknown Source)
at java.net.Socket.<init>(Unknown Source)
at org.postgresql.core.PGStream.<init>(PGStream.java:59)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionF
actoryImpl.java:77)
... 17 more
java.sql.SQLException: Database cluster VICADS99 is not active.
at net.sf.hajdbc.sql.AbstractInvocationHandler.invoke(AbstractInvocationHandle
r.java:89)
at $Proxy731.close(Unknown Source)
at org.jboss.resource.adapter.jdbc.CheckValidConnectionSQL.isValidConnection(C
heckValidConnectionSQL.java:63)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnectionFactory.isValid
Connection(BaseWrapperManagedConnectionFactory.java:559)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkValid(Bas
eWrapperManagedConnection.java:321)
at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.matchMa
nagedConnections(LocalManagedConnectionFactory.java:402)
at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConne
ction(InternalManagedConnectionPool.java:219)
at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.ge
tConnection(JBossManagedConnectionPool.java:679)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConne
ction(BaseConnectionManager2.java:404)
at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnecti
on(TxConnectionManager.java:381)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnect
ion(BaseConnectionManager2.java:496)
at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManag
erProxy.allocateConnection(BaseConnectionManager2.java:941)
at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperData
Source.java:89)
at org.hibernate.connection.DatasourceConnectionProvider.getConnection(Datasou
rceConnectionProvider.java:92)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:
446)
at
org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.ja
va:161)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1573)
at org.hibernate.loader.Loader.doQuery(Loader.java:696)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.j
ava:259)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1881)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.
java:71)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.
java:65)
at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityP
ersister.java:3072)
at org.hibernate.event.def.DefaultRefreshEventListener.onRefresh(DefaultRefres
hEventListener.java:145)
at org.hibernate.event.def.DefaultRefreshEventListener.onRefresh(DefaultRefres
hEventListener.java:62)
at org.hibernate.impl.SessionImpl.fireRefresh(SessionImpl.java:929)
at org.hibernate.impl.SessionImpl.refresh(SessionImpl.java:913)
at com.orci.vicads.VideoSvc.WallManagerFactoryImpl$1.run(WallManagerFactoryImp
l.java:134)
at java.util.TimerThread.mainLoop(Unknown Source)
at java.util.TimerThread.run(Unknown Source)
java.sql.SQLException: Database cluster VICADS99 is not active.
at net.sf.hajdbc.sql.AbstractInvocationHandler.invoke(AbstractInvocationHandle
r.java:89)
at $Proxy731.close(Unknown Source)
at org.jboss.resource.adapter.jdbc.CheckValidConnectionSQL.isValidConnection(C
heckValidConnectionSQL.java:63)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnectionFactory.isValid
Connection(BaseWrapperManagedConnectionFactory.java:559)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkValid(Bas
eWrapperManagedConnection.java:321)
at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.matchMa
nagedConnections(LocalManagedConnectionFactory.java:402)
at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConne
ction(InternalManagedConnectionPool.java:219)
at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.ge
tConnection(JBossManagedConnectionPool.java:679)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConne
ction(BaseConnectionManager2.java:404)
at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnecti
on(TxConnectionManager.java:381)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnect
ion(BaseConnectionManager2.java:496)
at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManag
erProxy.allocateConnection(BaseConnectionManager2.java:941)
at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperData
Source.java:89)
at org.hibernate.connection.DatasourceConnectionProvider.getConnection(Datasou
rceConnectionProvider.java:92)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:
446)
at
org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.ja
va:161)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1573)
at org.hibernate.loader.Loader.doQuery(Loader.java:696)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.j
ava:259)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1881)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.
java:71)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.
java:65)
at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityP
ersister.java:3072)
at org.hibernate.event.def.DefaultRefreshEventListener.onRefresh(DefaultRefres
hEventListener.java:145)
at org.hibernate.event.def.DefaultRefreshEventListener.onRefresh(DefaultRefres
hEventListener.java:62)
at org.hibernate.impl.SessionImpl.fireRefresh(SessionImpl.java:929)
at org.hibernate.impl.SessionImpl.refresh(SessionImpl.java:913)
at com.orci.vicads.VideoSvc.WallManagerFactoryImpl$1.run(WallManagerFactoryImp
l.java:134)
at java.util.TimerThread.mainLoop(Unknown Source)
at java.util.TimerThread.run(Unknown Source)
java.sql.SQLException: Database cluster VICADS99 is not active.
at net.sf.hajdbc.sql.AbstractInvocationHandler.invoke(AbstractInvocationHandle
r.java:89)
at $Proxy730.createStatement(Unknown Source)
at org.jboss.resource.adapter.jdbc.CheckValidConnectionSQL.isValidConnection(C
heckValidConnectionSQL.java:55)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnectionFactory.isValid
Connection(BaseWrapperManagedConnectionFactory.java:559)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkValid(Bas
eWrapperManagedConnection.java:321)
at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.matchMa
nagedConnections(LocalManagedConnectionFactory.java:402)
at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConne
ction(InternalManagedConnectionPool.java:219)
at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.ge
tConnection(JBossManagedConnectionPool.java:679)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConne
ction(BaseConnectionManager2.java:404)
at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnecti
on(TxConnectionManager.java:381)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnect
ion(BaseConnectionManager2.java:496)
at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManag
erProxy.allocateConnection(BaseConnectionManager2.java:941)
at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperData
Source.java:89)
at org.hibernate.connection.DatasourceConnectionProvider.getConnection(Datasou
rceConnectionProvider.java:92)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:
446)
at
org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.ja
va:161)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1573)
at org.hibernate.loader.Loader.doQuery(Loader.java:696)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.j
ava:259)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1881)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.
java:71)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.
java:65)
at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityP
ersister.java:3072)
at org.hibernate.event.def.DefaultRefreshEventListener.onRefresh(DefaultRefres
hEventListener.java:145)
at org.hibernate.event.def.DefaultRefreshEventListener.onRefresh(DefaultRefres
hEventListener.java:62)
at org.hibernate.impl.SessionImpl.fireRefresh(SessionImpl.java:929)
at org.hibernate.impl.SessionImpl.refresh(SessionImpl.java:913)
at com.orci.vicads.VideoSvc.WallManagerFactoryImpl$1.run(WallManagerFactoryImp
l.java:134)
at java.util.TimerThread.mainLoop(Unknown Source)
at java.util.TimerThread.run(Unknown Source)
java.sql.SQLException: Database cluster VICADS99 is not active.
at net.sf.hajdbc.sql.AbstractInvocationHandler.invoke(AbstractInvocationHandle
r.java:89)
at $Proxy730.createStatement(Unknown Source)
at org.jboss.resource.adapter.jdbc.CheckValidConnectionSQL.isValidConnection(C
heckValidConnectionSQL.java:55)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnectionFactory.isValid
Connection(BaseWrapperManagedConnectionFactory.java:559)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkValid(Bas
eWrapperManagedConnection.java:321)
at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.matchMa
nagedConnections(LocalManagedConnectionFactory.java:402)
at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConne
ction(InternalManagedConnectionPool.java:219)
at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.ge
tConnection(JBossManagedConnectionPool.java:679)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConne
ction(BaseConnectionManager2.java:404)
at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnecti
on(TxConnectionManager.java:381)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnect
ion(BaseConnectionManager2.java:496)
at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManag
erProxy.allocateConnection(BaseConnectionManager2.java:941)
at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperData
Source.java:89)
at org.hibernate.connection.DatasourceConnectionProvider.getConnection(Datasou
rceConnectionProvider.java:92)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:
446)
at
org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.ja
va:161)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1573)
at org.hibernate.loader.Loader.doQuery(Loader.java:696)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.j
ava:259)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1881)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.
java:71)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.
java:65)
at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityP
ersister.java:3072)
at org.hibernate.event.def.DefaultRefreshEventListener.onRefresh(DefaultRefres
hEventListener.java:145)
at org.hibernate.event.def.DefaultRefreshEventListener.onRefresh(DefaultRefres
hEventListener.java:62)
at org.hibernate.impl.SessionImpl.fireRefresh(SessionImpl.java:929)
at org.hibernate.impl.SessionImpl.refresh(SessionImpl.java:913)
at com.orci.vicads.VideoSvc.WallManagerFactoryImpl$1.run(WallManagerFactoryImp
l.java:134)
at java.util.TimerThread.mainLoop(Unknown Source)
at java.util.TimerThread.run(Unknown Source)
org.jboss.resource.JBossResourceException: Could not create connection; -
nested throwable: (java.sql.SQLException: No active databases in cluster
VICADS99)
at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.getLoca
lManagedConnection(LocalManagedConnectionFactory.java:225)
at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.createM
anagedConnection(LocalManagedConnectionFactory.java:195)
at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.createCo
nnectionEventListener(InternalManagedConnectionPool.java:633)
at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConne
ction(InternalManagedConnectionPool.java:267)
at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.ge
tConnection(JBossManagedConnectionPool.java:679)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConne
ction(BaseConnectionManager2.java:404)
at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnecti
on(TxConnectionManager.java:381)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnect
ion(BaseConnectionManager2.java:496)
at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManag
erProxy.allocateConnection(BaseConnectionManager2.java:941)
at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperData
Source.java:89)
at org.hibernate.connection.DatasourceConnectionProvider.getConnection(Datasou
rceConnectionProvider.java:92)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:
446)
at
org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.ja
va:161)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1573)
at org.hibernate.loader.Loader.doQuery(Loader.java:696)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.j
ava:259)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1881)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.
java:71)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.
java:65)
at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityP
ersister.java:3072)
at org.hibernate.event.def.DefaultRefreshEventListener.onRefresh(DefaultRefres
hEventListener.java:145)
at org.hibernate.event.def.DefaultRefreshEventListener.onRefresh(DefaultRefres
hEventListener.java:62)
at org.hibernate.impl.SessionImpl.fireRefresh(SessionImpl.java:929)
at org.hibernate.impl.SessionImpl.refresh(SessionImpl.java:913)
at com.orci.vicads.VideoSvc.WallManagerFactoryImpl$1.run(WallManagerFactoryImp
l.java:134)
at java.util.TimerThread.mainLoop(Unknown Source)
at java.util.TimerThread.run(Unknown Source)
Caused by: java.sql.SQLException: No active databases in cluster VICADS99
at net.sf.hajdbc.sql.DatabaseWriteInvocationStrategy.invokeAll(DatabaseWriteIn
vocationStrategy.java:81)
at net.sf.hajdbc.sql.ConnectionInvocationStrategy.invoke(ConnectionInvocationS
trategy.java:57)
at net.sf.hajdbc.sql.Driver.connect(Driver.java:104)
at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.getLoca
lManagedConnection(LocalManagedConnectionFactory.java:207)
... 27 more

[Anonymous]

The problem seems to have been the "local=" attribute on the databases. I
removed them and it seemed to start working